Run Script on multiple sheets in the same spreadsheet

Question

I have a running script that updates the D:N column with current months. It does currently only update the ActiveSheet, I would like to refactor it so it runs on all 4 sheets in the spreadsheet.

Current Code:

function setDates(){
  var monthNames = ["Jan (Qty)", "Feb (Qty)", "Mar (Qty)",
                    "Apr (Qty)", "May (Qty)", "Jun (Qty)",
                    "Jul (Qty)", "Aug (Qty)", "Sep (Qty)",
                    "Oct (Qty)", "Nov (Qty)", "Dec (Qty)"];

  var currentMonths = SpreadsheetApp.getActiveSpreadsheet().getRange("D1:N1").getValues()[0];
  var currentMonth = new Date().getMonth();

  if (currentMonths[0] == monthNames[currentMonth]){return;}

  for (var col = 0; col < currentMonths.length; col++){
    SpreadsheetApp.getActiveSpreadsheet()
                  .getActiveSheet()
                  .getRange(1,4+col)
                  .setValue(monthNames[(currentMonth + col > 11 ?
                                        currentMonth + col - 12 :
                                        currentMonth + col)]);
  }
}

What I've tried:

function sheetNamesFunction(){
            var sheets = ['Sheet1',
                          'Sheet2',
                          'Sheet3',
                          'Sheet4']; 
            for (var s in sheets){
          setDates(sheets[s]);
            }
          }


 function setDates(sheetName){
  var monthNames = ["Jan (Qty)", "Feb (Qty)", "Mar (Qty)",
                    "Apr (Qty)", "May (Qty)", "Jun (Qty)",
                    "Jul (Qty)", "Aug (Qty)", "Sep (Qty)",
                    "Oct (Qty)", "Nov (Qty)", "Dec (Qty)"];

  var ss = SpreadsheetApp.openById('1Ed_x52cQx5A0RSqwxsB925wzbGt5kh0Gsi0ybl');
  var sh = ss.getSheetByName(sheetName);
  var currentMonths = sh.getRange("D1:N1").getValues()[0];

  var currentMonth = new Date().getMonth();
  if (currentMonths[0] == monthNames[currentMonth]){return;}

  for (var col = 0; col < currentMonths.length; col++){
    SpreadsheetApp.getActiveSpreadsheet()
    .getActiveSheet()
    .getRange(1,4+col)
    .setValue(monthNames[(currentMonth + col > 11 ?
                          currentMonth + col - 12 :
                          currentMonth + col)]);
  }
}

When I run this I get the following:

If I run log on

var sh = ss.getSheetByName(sheetName);
      Logger.log(sh);

I can see that the first value is "null":

score 4 · Accepted Answer · edited May 23 '17 at 10:27

If you don't have a sheet named EXACTLY Sheet1, then the null result is expected. (Have you checked that name? Is there an extra space, maybe?)

It's not the best form to use the for..in construct with arrays, instead you should use a standard for loop. Personally, I find it helps me differentiate between arrays and objects ("associative arrays"). You might want to read: Why is using "for...in" with array iteration a bad idea?

You can avoid the problem by changing your approach a bit. Instead of dealing with sheet names, you can use Sheet Objects.

function setDatesOnAllSheets(){
  //  var ss = SpreadsheetApp.openById('1Ed_x52cQx5A0RSqwxsB925wzbGt5kh0Gsi0ybl');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var s=0; s<sheets.length; s++) {
     setDates(sheets[s]);
  }
}


function setDates(sheet){
  var monthNames = ["Jan (Qty)", "Feb (Qty)", "Mar (Qty)",
                    "Apr (Qty)", "May (Qty)", "Jun (Qty)",
                    "Jul (Qty)", "Aug (Qty)", "Sep (Qty)",
                    "Oct (Qty)", "Nov (Qty)", "Dec (Qty)"];

  var currentMonths = sh.getRange("D1:N1").getValues()[0];

  var currentMonth = new Date().getMonth();
  if (currentMonths[0] == monthNames[currentMonth]){return;}

  for (var col = 0; col < currentMonths.length; col++){
    sheet
    .getRange(1,4+col)
    .setValue(monthNames[(currentMonth + col > 11 ?
                          currentMonth + col - 12 :
                          currentMonth + col)]);
  }
}

Mogsdad you are a fuc*ing **GOD** ! That worked, i just needed to change sh.getRange to sheet.getRange. Thank you! — John Smith, Oct 28 '15 at 15:53
I had the same issue with my script and this array notation solved it: `var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0,1];` whereas before I had `var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");` The array notation `[0,1]` with the `.getSheets()[0,1]` action gets sheets 0 and 1 (in my case Sheet1 and Sheet2) because arrays begin from zero, not from one. Hope that can help new viewers! — Lod, Jun 18 '19 at 06:38

Run Script on multiple sheets in the same spreadsheet

1 Answers1