I am trying to write a Google Apps Script that will right align text strings in column C, if column F in the same row is not blank. Please see the sample screenshot attached. The script should make C4 and C7 right aligned.

sample-google-sheet

I have also tried !isnotblank function but couldn't get that to work either. When running the code below nothing happens, and it doesn't stop running until I click cancel.

function alignRight() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var startRow = 3;
  var numRows = 1000;

  var dataRange = sheet.getRange(startRow, 1, numRows, 6);
  var data = dataRange.getValues();

    for (var i=0; i<numRows; i  ) {
    var row = data[i];

    var columnF = sheet.getDataRange().getValues();
    columnF.forEach(function(row) {
    row[6] 
    });

    var columnC = sheet.getDataRange().getValues();
    columnC.forEach(function(row) {
    row[3] 
    });

    if (0 < columnF) {
    sheet.getRange(columnC).setHorizontalAlignment("right");
    };
  };
} 

CodePudding user response：

So first you want to not call getRange() and then call getDataRange() a few times in your for loop because you already have all the data you need with your first call. So basically

var data = dataRange.getValues()
data.forEach( (row, i) => {
  // Column F is 6th in line, so it would be in the 5th index
  if( !row[5] || !row[5].length  ) {
     // align right 
     sheet.getRange(i startRow, 3).setHorizontalAlignment("right");
  }
})

CodePudding user response：

Align right

function alignRight() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var startRow = 3;
  var data = sheet.getRange(startRow, 1, sheet.getLastRow() - startRow   1, 6).getValues();
  data.forEach(r => {
    if(r[5])sheet.getRange(i   startRow,3).setHorizontalAlignment("right");
  });
}

A bit faster

function alignRight() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var startRow = 3;
  var numRows = 1000;
  var data = sheet.getRange(startRow, 1, sheet.getLastRow() - startRow   1, 6).getValues();
  let cA = [];
  data.forEach(r => {
    if (r[6]) {
      cA.push(['right'])
    } else {
      cA.push(['left']);
    }
  });
  sheet.getRange(startRow, 3,cA.length,1).setHorizontalAlignments(cA);
}