CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LAD Service Desk
LAD Service Desk

Reputation: 289

How can I run a google script automatically on multiple sheets in the same spreadsheet

I have the following script.

Situation:

I have a spreadsheet with 10 worksheets and 15 users logging in and modifying it.

Script Function:

Inserts the number of days between two days in the activesheet in a specific column, Script compare date from column J and TodayDay and inserts the difference in the column F.

Problem:

I can not run this script with using timed triggers. I want to run this script every 15 min for the concurrent sheet.

Test Case:

The trigger only runs with timing if I have only one sheet in the spreadsheet. The trigger doesn't run with timing if I have more than one sheet. The trigger only runs for more than one sheet if I set the same trigger OnEdit.

I need to run this script for all sheets or active sheet every 15mins because I have two other scripts running OnEdit. When I set this script also in OnEdit the spreadsheet turn slowly.

    function onOpen() {
      var menuEntries = [ {name: "UpdateAge", functionName: "toTrigger"},
                         ];
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      ss.addMenu("Tools",menuEntries);//
    }

    // create a timer trigger that will call "toTrigger" every 15 minutes

    function toTrigger(){
      var sh = SpreadsheetApp.getActiveSheet();
      var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
      for(var n=0;n<data.length;++n){
      if(typeof(data[n][9])=='object'){
        data[n][5]=dayToToday(data[n][9])
          }
        }
       sh.getRange(1,1,data.length,data[0].length).setValues(data) 
    }

    function dayToToday(x){
      var refcell = x;;// get value in column A to get the reference date
      var refTime = new Date(refcell);
      var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
      var today = new Date();
      var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
      var day = parseInt(TD-ref);// get the difference in days (integer value )
      return day ; // return result that will be in cell
    }

EDIT: WORKING

Hi Srik,

Thanks so much Now is Working.

    function onOpen() {
      var menuEntries = [ {name: "UpdateAge", functionName: "shellFunction"},
                         ];
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      ss.addMenu("Tools",menuEntries);//
    }

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

      // create a timer trigger that will call "toTrigger" every 15 minutes

      function toTrigger(sheetName){
      var ss = SpreadsheetApp.openById('SHEET ID');
      var sh = ss.getSheetByName(sheetName);
      var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
      for(var n=0;n<data.length;++n){
      if(typeof(data[n][9])=='object'){
        data[n][5]=dayToToday(data[n][9])
          }
        }
       sh.getRange(1,1,data.length,data[0].length).setValues(data) 
    }

    function dayToToday(x){
      var refcell = x;;// get value in column A to get the reference date
      var refTime = new Date(refcell);
      var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
      var today = new Date();
      var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
      var day = parseInt(TD-ref);// get the difference in days (integer value )
      return day ; // return result that will be in cell
    }

Upvotes: 2

Views: 12511

Answers (1)

Srik
Srik

Reputation: 7965

There is no active sheet when your function runs as a trigger. The active sheet is applicable only when a user has the spreadsheet open. Modify your script to use getSheetByName and everything should work fine.

If you want this to work on multiple sheets, just call this function from another function.. 

function shellFunction(){
  var sheets = ['sheet1','sheet2',etc]; 
  for (var s in sheets){
    toTrigger(sheets[s]);
  }
}

And change your toTrigger function to receive the sheet name as argument.

function toTrigger(sheetName){
  var sh = SpreadsheetApp.openById(ID OF SPREADSHEET).getSheetByName(sheetName);
  /* Your regular code */
}

Upvotes: 3

Related Questions