How to apply a loop on a script for multiple/various sheet names in Google spreadsheet?

Question

I'm still learning GoogleApp scripting. Can anyone guide me in the right direction how to apply the same codes on a Google spreadsheet with multiple sheets with different sheet names? Maybe I need a script for a loop?

Thank you for your help in advance!

This is the script I have so far:

function MakeRowGray() { 

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); 
    var columnD = sheet.getRange(2, 2, sheet.getLastRow()-1, 1); // Row B
    var dValues = columnD.getValues(); 
    var columnE = sheet.getRange(2, 3, sheet.getLastRow()-1, 1); // Row C
    var eValues = columnE.getValues(); 

  for (var i = 2; i < dValues.length + 2; i++) {

    if (dValues[i-2][0].toUpperCase() == 'Y' && eValues[i-2][0].toUpperCase() == 'Y') { // Checks for 'Y' in both D and E columns (Participated & Received)

      // If they're both yes, make them gray...
        sheet.getRange(i, 1, 1, 7).setBackgroundColor("#CCCCCC"); // Make A through H gray

      }
         else if (dValues[i-2][0].toUpperCase() == 'Y' && eValues[i-2][0].toUpperCase() != 'Y' && eValues[i-2][0].toUpperCase() != 'W' && eValues[i-2][0].toUpperCase() != 'W?') // IN PROGRESS CODE -- MAKE ROW BLUE??
      {
      sheet.getRange(i, 1, 1, 7).setBackgroundColor("#AAAAFF"); // Make A through H blue

      }
    else if (dValues[i-2][0].toUpperCase() == 'Y' && eValues[i-2][0].toUpperCase() == 'W?') // Not sure if Waiting or not (W?)
      {
      sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FFBB00"); // Make A through H slightly orange

      }
    else if (dValues[i-2][0].toUpperCase() == 'X' && eValues[i-2][0].toUpperCase() == 'X') { 

              sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FF0000"); // Red

      }
    else if (dValues[i-2][0].toUpperCase() == 'Y' && eValues[i-2][0].toUpperCase() == 'W') { 

            sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FFFF00"); // Yellow

        }
    else
          { // Reset...
          sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FFFFFF");

    }

  }
};

Answer 1

You've identified the need to change a function you've written so it can be applied in a broader way than it currently supports. This type of work is generally referred to as refactoring.

In your case, this could be the thought process to follow...

1. Since you want to do the same thing on multiple sheets, generalize the current function to operate on an arbitrary Sheet. The definition of the MakeRowGray() function should be changed to accept a sheetName as a parameter. If you still want the existing behavior to be preserved, i.e. calling MakeRowGray() without any parameter will operate on Sheet1, that can be accomodated.

   function MakeRowGray(sheetName) { 
     sheetName = sheetName || 'Sheet1';  // Default to operate on Sheet1
   
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
     ... 
   

   Make that change, and test it. Does the function still behave as it used to? Can you pass in the name of one of your other sheets, and does it work there?

2. Next, write a new function that handles the problem of iterating through your various sheets. This function will pass off work to the refactored MakeRowGray().

   function makeAllSheetsGray() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheets = ss.getSheets();
   
     for (var i=0; i<sheets.length; i++) {
       MakeRowGray( sheet.getName() );
     }
   }
   

   Test that this function does what you expect. Does it find all sheets, regardless of name? Do the right names get passed to MakeRowGray()?

3. Improve things / tidy up.

   This is an important step for future maintenance and re-usability.

   • Do the function names make sense?

     For example, MakeRowGray() does not clearly indicate what the function is actually doing, probably because of previous refactoring. A name like conditionallyColorSheetRows would be an improvement. The new function, makeAllSheetsGray(), should adapt as well, since it was based on the previous inappropriate name.

   • Do variable names make sense?

   • Are you doing work you don't need to?

     For example, in makeAllSheetsGray() we're getting an array of Sheet instances, then passing the name of the sheet to MakeRowGray() where we use the name to get a handle on a Sheet instance. A further refactoring to use just the Sheet instances would save some processing. There may be reasons to leave things as they are, but since Google Apps Scripts have limited execution time, it's always smart to look for ways to reduce cycles.