CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
John Smith
John Smith

Reputation: 397

Run Script on multiple sheets in the same spreadsheet

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. enter image description here

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: enter image description here

If I run log on 

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

I can see that the first value is "null": enter image description here

Upvotes: 1

Views: 1765

Answers (1)

Mogsdad
Mogsdad

Reputation: 45710

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)]);
  }
}

Upvotes: 4

Related Questions