1

I'm trying to use a macro to check the top row of my sheet and automatically center align all cells that have only a dash. Right now, it looks like my code is running, but it's not doing anything:

function CenterAlignAllDashes() {
  var spreadsheet = SpreadsheetApp.getActive();
  var topRow = spreadsheet.getRange('A1:1');
  var topRowValues = topRow.getValues();
  for (let i=0; i < topRowValues.length; i++) {
    if (topRowValues[i] == '-') {
      topRow[i].setHorizontalAlignment('center');
    }
  }
};
bkula
  • 541
  • 3
  • 10
  • 22

2 Answers2

3

Modification points:

  • In your script, topRowValues is var topRowValues = topRow.getValues();. In this case, it is a 2-dimensional array. By this, topRowValues[i] == '-' is always false.
  • topRow is var topRow = spreadsheet.getRange('A1:1');. By this, topRow[i].setHorizontalAlignment('center') occurs an error because topRow[i] is undefined. But, by the above issue, the script in the if statement is not run.
  • About var topRowValues = topRow.getValues() and topRowValues.length, in this case, topRowValues.length is 1. By this, the for loop is looped only one time.
  • About spreadsheet.getRange('A1:1'), in this case, all cells of a row are retrieved.
  • When setHorizontalAlignment is used in a loop, the process cost will become high.

When these points are reflected in your script, it becomes as follows.

Modified script:

function CenterAlignAllDashes() {
  // Ref: https://stackoverflow.com/a/53678158
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : "";

  var sheet = SpreadsheetApp.getActiveSheet();
  var topRow = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var topRowValues = topRow.getValues()[0];
  var ranges = [];
  for (let i = 0; i < topRowValues.length; i++) {
    if (topRowValues[i] == '-') {
      ranges.push(`${columnIndexToLetter_(i)}1`);
    }
  }
  sheet.getRangeList(ranges).setHorizontalAlignment("center");
}
  • When this script is run, first, the values are retrieved from the 1st row and create the range list. And, the alignment is changed using the range list.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
2

Try this:

function CenterAlignAllDashes() {
  const sh = SpreadsheetApp.getActiveSheet();
  sh.getRange(1, 1, 1, sh.getLastColumn()).getValues().flat().forEach((v, i) => {
    if (v == "-") {
      sh.getRange(1, i + 1).setHorizontalAlignment("center");
    }
  });
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Hi how to do the same for this case: To all the cells In column A containing exactly "CH", have their adjacent cells in Column B centered aligned? I tested as so but it horizontally centers all the cells in column B, not just the ones adjacent to cells in column A with "CH": https://pastebin.com/UWaRvvuQ Thanks! – Lod Mar 01 '23 at 16:19
  • 1
    @Lod not sure what you mean by adjacent and your link doesn't work for me. – Cooper Mar 01 '23 at 19:06
  • Oh ok. I just mean for all cells in column B next to cells in column A. For example, if A2, A7, A222 etc. contain exaclty the string "CH", then the script must horiyontally center align B2, B7, B222 etc. cell's contents. Strange about the link not working, it works on my end. But here it is on pastecode.io: https://pastecode.io/s/r09ogie7 Thanks+! – Lod Mar 02 '23 at 20:51