当用户选择范围内的单元格时，Google App Script 执行函数 [英] Google App Script execute function when a user selects a cell in a range

问题描述

我想运行一个在用户点击/选择给定范围内的单元格时需要授权的功能.由于授权问题，一个简单的 onSelectionChange(e) 触发器不起作用，并且可安装的触发器显然不包括 onSelectionChange.

I'd like to run a function that requires authorization when a user clicks/selects a cell in a given range. A simple onSelectionChange(e) trigger doesn't work because of the authorization problem, and installable triggers don't include onSelectionChange apparently.

请问还有其他方法吗?谢谢！

Is there another way to do so please? Thanks!

推荐答案

对于不涉及ui/HtmlService的函数，可以使用简单的触发器来运行一些需要授权的功能(比如特权功能)通过削弱安全性:

For functions that don't involve ui/HtmlService, Simple triggers can be used to run some functions that require authorization(say privileged functions) by weakening security:

• 流程:触发器=>onSelectionChange(没有获取/执行特权函数的权限)=>触发自定义函数(获得授权以获取/没有授权以执行特权函数)=>获取/发布 =>webapp(运行特权功能的完全授权)

• The flow: Trigger => onSelectionChange(no auth to fetch/execute privileged functions) => trigger custom functions(gain auth to fetch/no auth to execute privileged functions) => fetch/post => webapp(full auth to run privileged functions)

此解决方案的灵感来自this，它直接使用可安装的触发器和普通访问令牌来授权自定义函数.从安全角度来看，不建议这样做.

This solution is inspired by this, which directly uses installable triggers and plain access tokens to authorize custom functions. This is not recommended from a security perspective.

尽管已努力确保执行以下脚本的用户的安全和隐私，但并未考虑所有攻击媒介.该脚本可能在很多方面都存在漏洞，尤其是在平台缺乏加密模块支持的情况下.在替代解决方案不可行的情况下，使用风险自负.

Although efforts have been taken to ensure security and privacy of user executing the following script, all attack vectors haven't been considered. The script might be vulnerable in a lot of areas, especially given the lack of crypto module support in the platform. Use at your own risk, where alternate solutions are infeasible.

在大多数情况下，首选使用菜单/按钮/时间触发器/可安装触发器(始终在完全身份验证下运行)的替代解决方案.使用 onEdit 可安装触发器 + 复选框可以实现类似的流程

In most cases, alternate solutions using menu/button/time triggers/installable triggers(which always runs under full auth) is preferred. A similar flow can be achieved using onEdit installable trigger + checkbox

要使用示例脚本，请按照以下步骤操作:

To use the sample script, follow the following steps:

• 在清单文件中设置必要的范围.对于示例脚本，

 "oauthScopes": ["https://www.googleapis.com/auth/script.send_mail"],

发布网络应用，明确目的是执行需要授权的功能

Publish a webapp for the explicit purpose of executing a function that requires authorization

• 以我"的身份执行
• 访问:任何人"

创建服务帐户没有为从自定义函数授权 web 应用程序的明确目的的角色/权限

Create a service account with no roles/permissions for the explicit purpose of authorizing webapps from custom functions

创建服务帐户密钥 并将其复制到示例脚本中的 creds 对象.

Create a service account key and copy it to the creds object in the sample script.

与服务帐户 (client_email) 共享您的项目/电子表格

Share your project/spreadsheet with the service account (client_email)

安装 Oauth2 库 以创建/签署 jwt 令牌服务帐号

Install Oauth2 library to create/sign jwt tokens for service account

创建一个用于设置自定义函数的hiddenSheet，该函数将设置为该sheet的A1 onSelectionChange

Create a hiddenSheet for setting a custom function, which will be set to set to A1 of this sheet onSelectionChange

以下脚本会在有人触摸您电子表格中的任何内容时发送电子邮件.

The following script sends email, when someone touches anything in your spreadsheet.

/**
 * Gets Oauth2 service based on service account with drive scope
 * Drive scope needed to access webapp with access:anyone
 * This does not grant access to the user's drive but the service
 *     account's drive, which will only contain the file shared with it
 */
function getService_() {
  const creds = {
    private_key: '[PRIVATE_KEY]',
    client_email: '[CLIENT_EMAIL]',
  };
  const PRIVATE_KEY = creds['private_key'];
  const CLIENT_EMAIL = creds['client_email'];
  return OAuth2.createService('GoogleDrive:')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(PRIVATE_KEY)
    .setIssuer(CLIENT_EMAIL)
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('https://www.googleapis.com/auth/drive');
}
/**
 * @returns {string} base64 encoded string of SHA_512 digest of random uuidstring
 */
const getRandHashKey_ = () =>
  Utilities.base64EncodeWebSafe(
    Utilities.computeDigest(
      Utilities.DigestAlgorithm.SHA_512,
      Utilities.getUuid() //type 4 advertised crypto secure
    )
  );

/**
 * @param {GoogleAppsScript.Events.SheetsOnSelectionChange} e
 */
const onSelectionChange = e => {
  const sCache = CacheService.getScriptCache();
  e.rangestr = e.range.getSheet().getName() + '!' + e.range.getA1Notation();
  const hashRandom = getRandHashKey_();
  sCache.put(hashRandom, JSON.stringify(e), 20);//expires in 20 seconds
  e.source
    .getSheetByName('hiddenSheet')
    .getRange('A1')
    .setValue(`=CALLWEBAPP("${hashRandom}")`);
};
/**
 * Calls published webapp(Access:Anyone) with service account token
 * @customfunction
 * @returns void
 */
const callwebapp = randomHash => {
  const webAppScriptId = '[SCRIPT_ID]';
  UrlFetchApp.fetch(
    `https://script.google.com/macros/s/${webAppScriptId}/exec`,
    {
      method: 'post',
      payload: { e: randomHash },
      headers: { Authorization: `Bearer ${getService_().getAccessToken()}` },
    }
  );
};

/**
 * @param {GoogleAppsScript.Events.AppsScriptHttpRequestEvent} e
 */
const doPost = e => {
  const hashRandom = e.parameter.e;
  const sCache = CacheService.getScriptCache();
  const encodedSelectionEvent = sCache.get(hashRandom);
  if (encodedSelectionEvent) {
    const selectionEvent = JSON.parse(encodedSelectionEvent);
    MailApp.sendEmail(
      '[EMAIL_TO_SEND_NOTIFICATION_TO]',
      'Someone touched your spreadsheet',
      `Wanna take a look? ${selectionEvent.rangestr} was touched without your permission`
    );
  }
};

这篇关于当用户选择范围内的单元格时，Google App Script 执行函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！