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

问题描述

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

这是由

提交"按钮应该是一个图像，通过上下文菜单为其分配功能 - 分配脚本"(输入您的功能名称以处理表单"数据):

然后你的 onEdit 触发器应该在编辑发生时激活当前用户的表单"表.示例代码:

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屋！