run a time based script on multiple sheets in the same spreadsheet

Question

I´m new to GAS , i wrote an script to perform a price comparasion it basically get the price of a products , store them , and every day by a time based trigger check if this price changes , if price changes send an email , if nothing changes do nothing.

So right now i´m stuck , i want to add more sheets to the spreadsheet i need that the same script run in all sheets but i don´t know how to manage it.

As far as i know , now with my script it runs based on this feature

"var ss = SpreadsheetApp.getActiveSpreadsheet();" , the script know wich is the active spreadsheet , if i configure a time based trigger it works like a charm , but only works with the first sheet. I´m looking someway to run the script with a time based trigger and this script runs in all the sheets

How can i tell the script wich is the active sheet, it´s clear when i work with the open document wich is the active sheet , but i don´t how can i set the active sheet when the script is running.

I have read some documentation and some people suggest to run a loop that check all the sheets in the spreadsheet , get the names or id and the run the script in all this sheets. I have just started with javascript and Gas a few weeks ago and i been working with it but i was not able to make it works

I have found some examples of people that want to do something similar but i really don´t know all the functions of the code ,

Here is some example of a loop that i found here i expect that this code get the names of the sheets and excute a function , but i´m not shure.

  function CheckSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for(var k=0;k<sheets.length;k++) {  
   excute function checkprice???
   }

and here is my script , i don´t know if the example i have found is valid for my situation

function CheckPrice() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var source1 = ss.getRange("A27:A145");
var source2 = ss.getRange("B27:B145");
var source3 = ss.getRange("A28:A145").getValues();
var source4 = ss.getRange("E28:E145").getValues();
var source5 = ss.getRange("E27:E145");
var source6 = ss.getRange("A28:A145");




if (source6.isBlank()) {

Browser.msgBox('WAITING FOR DATA !', Browser.Buttons.OK); 
Utilities.sleep(30000);

}


if (source5.isBlank()) {

source1.copyTo(ss.getRange("E27:E145"), {contentsOnly: true});
source2.copyTo(ss.getRange("F27:F145"), {contentsOnly: true});
Browser.msgBox('VALUES COPIED !', Browser.Buttons.OK);
}

  var Inspector = false;
 for(var i=0;i<source3.length;i++)
  {
  if(source3[i][0].toString() !=  source4[i][0].toString())
  {
 Inspector = true;
 }
}

if(Inspector)
{

 MailApp.sendEmail("oker82@gmail.",ss.getSheetName() + " HAS ", ss.getSheetName() + " CHANGED " );

 }
 else
 {
 Browser.msgBox('NOTHING CHANGES !', Browser.Buttons.OK);
 }
 }

Munkey Munkey · Accepted Answer · 2016-04-05T15:45:10

This is untested, but I think it's along the lines you are looking for.

function CheckSheets(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for(var k=0;k<sheets.length;k++) {  
    CheckPrice(sheets[k].getName());
   }
 }

function CheckPrice(sheetName){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var source1 = sheet.getRange("A27:A145");
var source2 = sheet.getRange("B27:B145");
var source3 = sheet.getRange("A28:A145").getValues();
var source4 = sheet.getRange("E28:E145").getValues();
var source5 = sheet.getRange("E27:E145");
var source6 = sheet.getRange("A28:A145");




if (source6.isBlank()) {

Browser.msgBox('WAITING FOR DATA !', Browser.Buttons.OK); 
Utilities.sleep(30000);

}


if (source5.isBlank()) {

source1.copyTo(sheet.getRange("E27:E145"), {contentsOnly: true});
source2.copyTo(sheet.getRange("F27:F145"), {contentsOnly: true});
Browser.msgBox('VALUES COPIED !', Browser.Buttons.OK);
}

  var Inspector = false;
 for(var i=0;i<source3.length;i++)
  {
  if(source3[i][0].toString() !=  source4[i][0].toString())
  {
 Inspector = true;
 }
}
}

run a time based script on multiple sheets in the same spreadsheet

1 Answers