Google Sheets macro to center align all dashes in top row-CodePudding

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');
    }
  }
};

CodePudding user response：

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");
    }
  });
}

CodePudding user response：

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.

Modification points:

Modified script:

References: