用户选择范围内的单元格时的Google App脚本执行功能 [英] 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 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.

在大多数情况下，首选使用菜单/按钮/时间触发器/可安装触发器(始终在完全授权下运行)的替代解决方案.

In most cases, alternate solutions using menu/button/time triggers/installable triggers(which always runs under full auth) is preferred.

要使用示例脚本，请执行以下步骤:

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

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

创建服务帐户没有角色/权限的明确目的是通过自定义功能授权Webapps

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来设置自定义功能，该功能将设置为此工作表的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脚本执行功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！