如何在“编辑时”询问用户输入Google电子表格中的可安装触发器？ [英] How to ask for user input in "on edit" installable trigger in Google Spreadsheet?

问题描述

当用户将特定列中的单元格编辑为特定值时，根据新值将消息发送到Telegram机器人。

这是通过

提交按钮应该是一个图像，通过上下文菜单为其分配功能 - 分配脚本（输入你的函数名称来处理表单数据）：

然后你的 onEdit 触发器应该激活form表单发生编辑时的当前用户。示例代码：

  function onEdit（）{
 SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Form'）。activate （）; 
} 
  

填写表单后，用户点击提交图片，即可发送数据到外部服务，示例代码：

  function submit（）{
 var response = UrlFetchApp.fetch（http ：//www.google.com/）; 
 SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Results'）。appendRow（[response.getResponseCode（），new Date（）]）; 
} 
  

When a user edits a cell in particular column to a particular value, messages are sent to a Telegram bot, depending on a new value.

This is implemented by an "installable trigger" on edit event. It is installed by the document owner and always runs under that account. There are other users editing the doc and triggering the action. All works fine.

Now for some action we need to request a few words from the user who made the edit.

But since November 2017 (see issue 68846962) "for security reasons" Google restricted their Apps Script and now Ui methods prompt() and showSideBar() are only available to the user who created the trigger. For others it causes "You do not have permission to call showSidebar()" error.

What workaround could there be to request and receive user input on the cell edit event for all users? Considering that further actions require permissions to access external services (call Telegram API).

解决方案

As a possible workaround, you can create separate sheet that will be your "form" for user input:

"Submit" button should be an image, assign function to it by context menu - "Assign script" (enter your function name to process "form" data):

Then your onEdit trigger should just activate "form" sheet for the current user when edit happened. Sample code:

function onEdit() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form').activate();
}

After filling the form user clicks on "Submit" image, then you can send data to external service, sample code:

function submit() {
  var response = UrlFetchApp.fetch("http://www.google.com/");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Results').appendRow([response.getResponseCode(), new Date()]);
}

这篇关于如何在“编辑时”询问用户输入Google电子表格中的可安装触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！