如何使用Google表格onEdit()触发器运行函数以更新Google表单 [英] How to use Google Sheets onEdit() trigger to run function to update Google Form

问题描述

我有一个Google表格工作簿，其中有一个我要用来保存该工作簿和Google表单设置的工作表.

I have a Google Sheets workbook in which I have a sheet that I want to use to save settings for both the workbook and a Google Form.

我已经制作了一个自定义的 onEdit()函数，该函数会触发两个函数-一个用于更新工作簿，另一个用于更新表单:

I've made a custom onEdit() function which triggers two functions - one to update the workbook, and one to update the form:

function onEdit(e){
  var range = e.range;
  if (range.getSheet().getName() == "Settings") {
    setMonthByMonth(); // Updates the workbook
    updateExpenseForm(); // Should update the form
  }
}

工作簿功能正常运行.表单功能没有.该函数如下所示:

The workbook function works fine. The form function doesn't. That function looks like this:

function updateExpenseForm() {
  var categoryId = "1098744733",
      currencyId = "595880786",
      responsibleId = "2043429480",
      formId = "1arB3yibHa_Y69fc_GxE8mU1FzNR3PYgsivYbrQ0LsQk";

  var form = FormApp.openById(formId);
  var ss = SpreadsheetApp.getActive();
  var settingsSheet = ss.getSheetByName("Settings");

  var categoryList = form.getItemById(categoryId).asListItem();
  var responsibleList = form.getItemById(responsibleId).asListItem();
  var currencyList = form.getItemById(currencyId).asListItem();

  var categoryValues = settingsSheet.getRange("A5:A").getValues();
  var categoryNames = [];

  var responsibleValues = settingsSheet.getRange("F5:F").getValues();
  var responsibleNames = [];

  var currencyValues = settingsSheet.getRange("K5:K").getValues();
  var currencyNames = [];

  for(var i = 0; i < categoryValues.length; i++) {
    if(categoryValues[i][0] != "") {
      categoryNames[i] = categoryValues[i][0];
    }
  }

  for(var i = 0; i < responsibleValues.length; i++) {
    if(responsibleValues[i][0] != "") {
      responsibleNames[i] = responsibleValues[i][0];
    }
  }

  for(var i = 0; i < currencyValues.length; i++) {
    if(currencyValues[i][0] != "") {
      currencyNames[i] = currencyValues[i][0];
    }
  }

  categoryList.setChoiceValues(categoryNames);
  responsibleList.setChoiceValues(responsibleNames);
  currencyList.setChoiceValues(currencyNames);
}

我可以手动运行它，而脚本编辑器没有任何问题.但是当通过 onEdit()函数触发时，它不会运行.而且，如果我将表单功能移到工作簿功能上方，那么工作簿功能也不会被调用.

I can run it manually without any problems from the script editor. But it doesn't run when triggered through the onEdit() function. And if I move the form function above the workbook function, the workbook function is not called either.

我想念什么吗?

推荐答案

您很有可能正在使用简单的触发器.他们无法访问需要授权的服务，例如表单服务.解决方案是改用可安装触发器.

It's very likely that you are using a simple trigger. They can't access services that require authorization, like the Forms Service. The solution is to use an installable trigger instead.

来自 https://developers.google.com/apps-script/guides/triggers/#restrictions

限制

因为简单的触发器会自动触发，而无需询问用户为了获得授权，它们受到以下限制:

Restrictions

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions:

...

• 他们无法访问需要授权的服务.例如，一个简单触发器无法发送电子邮件，因为Gmail服务需要授权，但简单的触发器可以将短语翻译为语言服务，这是匿名的.

这篇关于如何使用Google表格onEdit()触发器运行函数以更新Google表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！