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

Question

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
    }

Why not use a spreadsheet function? You can calculate the day difference for each row without the fuss. Just remember to send the reference date and toda's date as a function parameter. — Taras
I can not use a formula Array for ex because the spreadsheet is under modification every time. When the activity on that row is completed we delete it. <br> Example: If we use formula someone need to drag the to the new row, to avoid any error or a complex sheet I use this script to automatically insert the number without any issue is someone delete the cell where the formula is. <br> Using Array formula but when you add new row the formula not drag automatically also using array. <br> For my case I can not implement a formula. If you have any suggestion plese let me know. — LAD Service Desk

Srik Srik · Accepted Answer · 2013-06-08T00:46:19

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 */
}