google-apps-script - 如何在同一个电子表格中的多个工作表上自动运行谷歌脚本

Question

我有以下 script .

情况:

我有一个包含 10 个工作表和 15 个用户登录并修改它的电子表格。

脚本功能:

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.

问题:

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

测试用例:

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.

我需要每 15 分钟为所有工作表或事件工作表运行此脚本，因为我还有另外两个运行 OnEdit 的脚本。当我也在 OnEdit 中设置此脚本时，电子表格缓慢转动。

    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
    }

编辑:工作

嗨，斯里克，

非常感谢现在正在工作。

    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
    }

Answer 1

当您的函数作为触发器运行时，没有事件工作表。事件工作表仅在用户打开电子表格时适用。修改您的脚本以使用 getSheetByName一切正常。

如果你想让它在多张纸上工作，只需从另一个函数调用这个函数..

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

并更改您的 toTrigger接收工作表名称作为参数的函数。

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