Google脚本-在不同的工作表中触发和起作用 [英] Google Scripts - trigger and function in different sheets

问题描述

我需要：

• 创建新工作表


• 调用主函数


• 设置一个将调用主函数的触发器

基本上是这样：

function new_campaign(){
    var sheet_name = new_sheet();
    main_function(sheet_name);
    trigger(sheet_name);
}

使用一张纸不会有任何问题，因为我可以在纸上放置名称作为全局变量。
问题是我必须创建多个工作表，并且所有工作都必须继续进行。

With a single sheet there would be no problem because I could set before the sheet name as a global variable. The problem is that I have to create multiple sheets and all have to keep on working.

我创建了新工作表的功能，以便它返回工作表的名称，因此我可以调用传递工作表的 main_function 。
不幸的是，触发器并不是那么容易，因为我不知道如何通过工作表。

I created the function of the new sheet so that it's returning the name of the sheet, so I can call the main_function passing the sheet. Unfortunately for the trigger is not that easy because I don't understand how to pass the sheet.

function trigger(sheetName) {
  ScriptApp.newTrigger("main_function")
  .timeBased()
  .everyMinutes(1)
  .create();
}

更新

根据建议，我尝试使用 PropertiesService 将触发器的ID映射到我需要在函数中使用的参数（在我的情况下是sheetName）

As suggested, I tried tu use PropertiesService mapping the ID of the trigger with the parameter I need to use in the function (which in my case is the sheetName)

function trigger(sheetName) {
  var triggerID = ScriptApp.newTrigger("main_function")
  .timeBased()
  .everyMinutes(1)
  .create()
  .getUniqueId();
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty(triggerID, sheetName);
}

更新2

我试图从事件对象中提取触发器ID，以从 scriptProperties 中获取sheetName。现在我的 main_function 看起来像这样

I tried to extract the trigger ID from the event object to get the sheetName from the scriptProperties. Now my main_function look like this

function main_function() {
    var e = arguments[0];
    var scriptProperties = PropertiesService.getScriptProperties();
    var sheetName = scriptProperties.getProperty(e.triggerUid);
}

推荐答案

解决方案

有一个函数可以处理其他函数和触发器：

There is a function which handles the other functions and the trigger:

function new_campaign(){
    var sheet_name = new_sheet(); //function to create a new sheet
    main_function(); //to execute main_function without delay the first time
    trigger(sheet_name); //set the trigger
}

触发函数：

function trigger(sheetName) {
  var triggerID = ScriptApp.newTrigger("main_function")
  .timeBased()
  .everyMinutes(1)
  .create()
  .getUniqueId();
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty(triggerID, sheetName); //association between sheetName and triggerID to call it back
}

和main_function函数：

and the main_function function:

function main_function() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName;

    var e = arguments[0]; //argument[0] contains the event object

    //if else to handle the first case
    //if the event object is defined, it extract the sheetName associated to the triggerID in the ScriptProperties
    //otherwise take the active sheet
    if (!(e == undefined)){
        var scriptProperties = PropertiesService.getScriptProperties();
        sheetName = scriptProperties.getProperty(e.triggerUid);
    }else{
        var sheet = ss.getActiveSheet();
        sheetName = sheet.getSheetName();
    }

    var sheet = ss.getSheetByName(sheetName);
    //...
}

这篇关于Google脚本-在不同的工作表中触发和起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！