Google Sheets Script: How to run function on loop over all sheets in workbook and onOpen?

Question

I want a script to highlight a cell if another cell is yesterday's date (then remove the previous highlights). I want the script to run whenever I open the spreadsheet and when I switch to another tab.

I originally just had the script run as a .setActiveSelection() or a .getRange("B:B").activate(), but it's easier to notice when the cell is highlighted. But then I have to remove the previous highlights.

I'm trying to get my function to work over all my sheets to highlight a cell (col E) in the same row if the date=today()-1 (col B) and remove the highlight in column E if the date (col B) < today()-1. Right now, I'm using a script to set the highlight and conditional formatting to remove any previous highlights. However, the script only runs on the active sheet, and either has to use the trigger onOpen or I have to set it to run every minute.

I found this solution saying I could use this in my code to run a loop through all my sheets (the first 6 tabs):

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (i = 0; i < 6; i++) {
  var sheet = ss.getSheetByName(sheets[i]);
}

However, when I add it, I get an error message saying:

Cannot call method "getRange" of null

Should I just use conditional formatting to highlight a specific date and remove previous highlighting?

https://docs.google.com/spreadsheets/d/1oUPWWA8NbT6pYve3pFvBlKm7PNCj3XxnM020yp-rRHs/edit?usp=sharing

My full function:

function jumpToAmtEaten() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (i = 0; i < 6; i++) {
    var sheet = ss.getSheetByName(sheets[i]);
    var r = sheet.getRange("B:B");
    var values = r.getValues();  
    var day = 24*3600*1000;  
    var today = parseInt((new Date().setHours(0,0,0,0))/day);  
    var ssdate; 
    for (var i=0; i<values.length; i++) {
      try {
        ssdate = values[i][0].getTime()/day;
      }
      catch(e) {
      }
      if (ssdate && Math.floor(ssdate) == today-1) {
        sheet.setActiveRange(r.offset(i,3,1,1)).setBackground("yellow"); 
        break;                               
      }                                       
    }
  }
}

Rafa Guillermo Rafa Guillermo · Accepted Answer · 2019-07-30T10:00:53

When you call

var sheet = ss.getSheetByName(sheets[i]);

You're passing a a Sheet object to the method getSheetByName() instead of a name. Also you don't need to keep calling a getSheet method once you've already obtained them once. Try this instead:

function jumpToAmtEaten() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    Logger.log("i = " + i);
    var r = sheets[i].getRange("B3:B")
    Logger.log("r = " + r.getA1Notation());
    var values = r.getValues();
    var day = 86400000;  
    var today = parseInt((new Date().setHours(0, 0, 0, 0)) / day);  
    var ssdate; 
    for (var j = 0; j < values.length; j++) {
      try {
        ssdate = values[j][0].getTime() / day;
      }
      catch(e) {
      }
      if (ssdate && Math.floor(ssdate) == today - 1) {
        Logger.log(Math.floor(ssdate));
        Logger.log(today);
        Logger.log(i);
        Logger.log(sheets[i]);
        Logger.log(r.offset(j, 3, 1, 1).getA1Notation());
        sheets[i].getRange(r.offset(j, 3, 1, 1).getA1Notation()).setBackground("yellow"); 
        break;                               
      }                                       
    }
  }
}

Also you can't use the same counter variable in nested loops as you will increment it every time you complete an interation of either one.

Google Sheets Script: How to run function on loop over all sheets in workbook and onOpen?

1 Answers