Google Sheets Script: Format Row Based on Cell Value - Trying to Optimize Script

Question

I have a script that will loop through the rows of a specific column in my Google Sheet and then format the entire row based on the value that is contained within the cell. The problem is that this script is very slow because I am using getValue on each individual row of the column range, rather than using getValues on the entire column and referencing it like an array.

See the original script below:

function rowLoop() {
  var ss = SpreadsheetApp.openById("Fake_ID");
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Fake Name"));
  var endRow = sheet.getLastRow();
  // <= to repeat for all rows
  for (var r = 1; r <= endRow; r++) {
    rowAlignment(r);
    }
}

function rowAlignment(r) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var c = sheet.getLastColumn();
  var row = sheet.getRange(r, 2, 1, c);
  // Get cell in column E of row
  var typeCell = row.getCell(1,25);
  // Get its value
  var typeData = typeCell.getValue();
  // Test equal to 'Post' with ==
  if(typeData == 'Post') {
     row.setHorizontalAlignment('right').setFontSize('6').setFontStyle('italic').setFontWeight('normal');
  }
  else if (typeData == 'Campaign') {
     row.setFontWeight('bold').setHorizontalAlignment('left').setFontSize('8').setFontStyle('normal');
  }
  SpreadsheetApp.flush();
}

The script does exactly what it's meant to, but it's just slow. I tried optimizing it by using getValues rather than getValue. This is what I've written so far, but the issue is that the script doesn't do anything. It doesn't pop any errors, it just doesn't seem to do anything. See below:

function rowTestLoop() {
  var ss = SpreadsheetApp.openById("Fake_ID");
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Fake_Name"));
  var endRow = sheet.getLastRow();
  var endCol = sheet.getLastColumn();
  var data = sheet.getRange(1,1, endRow, endCol).getValues();
  // <= to repeat for all rows
  for (var r = 1; r <= endRow; r++) {
    var currentRow = sheet.getRange(r, 2, 1, endCol);
    if(data[r][24] == 'Post') {
      currentRow.setHorizontalAlignment('right').setFontSize('6').setFontStyle('italic').setFontWeight('normal');
    }
    else if (data[r][24] == 'Campaign') {
     currentRow.setHorizontalAlignment('left').setFontSize('8').setFontStyle('normal').setFontWeight('bold');
    }
  }
  SpreadsheetApp.flush();
}

Can anyone please help?

Jack Brown Jack Brown · Accepted Answer · 2017-01-27T00:04:24

So I believe I have figured it out.

Your are trying to look for tags post and Campaign in column number 24. However when you extract the sheet data into a array you have account for that fact that array indices start at '0' where as spreadsheet indices start at '1'

Orginal Code: if(data[r][24] == 'Post')

Edited Code: if(data[r-1][23] == 'Post')

So basically you need to modify indices to match that of the array and not the spreadsheet. In other words Row 1 in spreadsheet is element 0 in an array, similarly Row 1, column 12 in the spreadsheet is element[0][11] in an array

function rowTestLoop() {
  var ss = SpreadsheetApp.openById("Fake_ID");
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Fake_Name"));
  var endRow = sheet.getLastRow();
  var endCol = sheet.getLastColumn();
  var data = sheet.getRange(1,1, endRow, endCol).getValues();
  // <= to repeat for all rows
  for (var r = 1; r <= endRow; r++) {
    var currentRow = sheet.getRange(r, 2, 1, endCol);
    if(data[r-1][23] == 'Post') {
      currentRow.setHorizontalAlignment('right').setFontSize('6').setFontStyle('italic').setFontWeight('normal');
    }
    else if (data[r-1][23] == 'Campaign') {
     currentRow.setHorizontalAlignment('left').setFontSize('8').setFontStyle('normal').setFontWeight('bold');
    }
  }
  SpreadsheetApp.flush();
}

Google Sheets Script: Format Row Based on Cell Value - Trying to Optimize Script

1 Answers