在Google Apps脚本上使用具有自定义功能的触发器 [英] Using triggers with custom functions on Google Apps Script

问题描述

我的脚本有一个问题，在

I had a prob with my script, which was greatly answered in this question. Basically custom functions cannot call services that require authorization. However, as far as I understood if I use simple triggers, such as onEdit it could work.

我检查了上一个问题中建议的文档，但是我没有成功将其应用于我的代码，您可以在下面看到它:

I checked the documentation suggested in the previous question, however I wasn't successful applying that to my code, which you can see below:

function FileName (id) {
  var ss = DriveApp.getFileById(id);
  return ss.getName();
}

如何修改我的代码以使用简单的触发器?

How could I adapt my code to use simple triggers?

这里是一个样本表，它重复了问题

推荐答案

我相信您的目标如下.

• 您想将 FileName 函数用作Google Spreadsheet的自定义函数.
  • 您希望在将文件ID放入"B"列时自动检索文件名.
  • 您要将文件名放在"C"列中.
  • You want to use your function of FileName as the custom function of Google Spreadsheet.
    • You want to automatically retrieve the filename when the file ID is put to the column "B".
    • You want to put the filename to the column "C".

    不幸的是，在使用自定义功能时，在当前规范中，除需要授权的几种方法(例如，其中一种是UrlFetchApp)外，大多数方法都无法使用.这样，脚本中的 DriveApp.getFileById(id)不能与自定义函数一起使用.但是有一种解决方法.在自定义功能处，可以使用UrlFetchApp.在这个答案中，我想建议使用带有UrlFetchApp的Web应用作为授权包装.这样，可以使用Web Apps进行授权.因此，您的功能可以通过自定义功能运行.

    Unfortunately, when the custom function is used, in the current specification, the most methods except several methods (for example, one of them is UrlFetchApp.) that the authorization is required cannot be used. By this, DriveApp.getFileById(id) in your script cannot be used with the custom function. But there is a workaround. At the custom function, UrlFetchApp can be used. In this answer, I would like to propose to use the Web Apps with UrlFetchApp as the wrapper for authorizing. By this, the authorization can be done with the Web Apps. So your function can be run by the custom function.

    请将以下脚本复制并粘贴到脚本编辑器中并保存.

    Please copy and paste the following script to the script editor and save it.

    const key = "samplekey"; // This is a key for using Web Apps. You can freely modify this.
    
    // This is your function.
    function FileName_(id) {
      var ss = DriveApp.getFileById(id);
      return ss.getName();
    }
    
    // Web Apps using as the wrapper for authorizing.
    function doGet(e) {
      let res = "";
      if (e.parameter.key === key) {
        try {
          res = FileName_(e.parameter.id);
        } catch (err) {
          res = `Error: ${err.message}`;
        }
      } else {
        res = "Key error.";
      }
      return ContentService.createTextOutput(JSON.stringify({value: res}));
    }
    
    function Filename(id) {
      const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set the URL of Web Apps after you set the Web Apps.
    
      const res = UrlFetchApp.fetch(`${webAppsUrl}?id=${id}&key=${key}`);
      if (res.getResponseCode() != 200) throw new Error(res.getContentText());
      return JSON.parse(res.getContentText()).value;
    }
    

    2.部署Web应用.

    1. 在脚本编辑器上，通过发布"打开对话框.->部署为网络应用".
    2. 选择我" 作为将应用程序执行为:" .
       • 通过这种方式，脚本以所有者身份运行.
    • 在这种情况下，不需要访问令牌即可向Web Apps请求.但是在此示例脚本中，使用了用于请求Web Apps的密钥.
    1. 点击查看权限".
    2. 选择自己的帐户.
    3. 点击高级"在此应用未验证"中.
    4. 点击转到###项目名称###(不安全)"
    5. 点击允许"按钮.

  • 点击确定".
  • 复制Web应用程序的URL.就像 https://script.google.com/macros/s/###/exec .
    • 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.

    3.测试此替代方法.

    将文件ID放在单元格"A1"中时，请将 = filename(A1)放在单元格中作为自定义函数.这样，脚本将运行并返回响应值.

    3. Test this workaround.

    When the file ID is put to the cell "A1", please put =filename(A1) to a cell as the custom function. By this, the script is run and the response value is returned.

    • 以上示例脚本是用于测试脚本的简单示例脚本.因此，当您想使用各种方法时，这篇文章可能会有用.

    • Above sample script is a simple sample script for testing your script. So when you want to use the various methods, this post might be useful.

    请在启用V8的情况下使用此脚本.

    Please use this script with enabling V8.

    作为另一种方法，我认为当将文件ID手动放入"B"列时，可以使用可安装的OnEdit触发器.示例脚本如下.请设置工作表名称.并将触发器安装到 installedOnEdit 的功能上.参考这样，将文件ID放入列"B" sheetName 的文件ID，则将文件ID放在"C"列中.

    As other method, I think that when the file ID is manually put to the column "B", the installable OnEdit trigger can be used. The sample script is as follows. Please set the sheet name. And please install the trigger to the function of installedOnEdit. Ref By this, when the file ID is put to the column "B" of sheetName, the file ID is put to the column "C".

      function installedOnEdit(e) {
        const sheetName = "Sheet1";
        const range = e.range;
        const sheet = range.getSheet();
        if (!(sheet.getSheetName() == sheetName && range.getColumn() == 2 && range.getRow() > 1)) return;
        const value = range.getValue();
        let res = "";
        try {
          res = DriveApp.getFileById(value).getName();
        } catch(e) {
          res = e.message;
        }
        range.offset(0, 1).setValue(res);
      }
    

    • 网络应用
    • 利用Google的Web Apps优势Apps脚本
    • 增强的自定义功能使用Web Apps作为包装器的Google Spreadsheets
    • 相关问题
      • 您可以编写可绘制内容的Google表格功能吗?
      • 在App脚本(js)中运行Youtube数据服务时发生错误-超出了未经身份验证的使用的每日限制
      • 如何使未经授权的用户保护电子表格
      • 更改工作表的所有者，无论复制者如何
      • Web Apps
      • Taking advantage of Web Apps with Google Apps Script
      • Enhanced Custom Function for Google Spreadsheet using Web Apps as Wrapper
      • Related questions
        • Can you write a Google Sheets function that draws something?
        • Error when running Youtube Data Service in App Scripts (js) – Daily Limit for Unauthenticated Use Exceeded
        • How to enable not authorized users to protect the spreadsheet
        • Changing Owner of the Sheet irrespective of the duplicator

        这篇关于在Google Apps脚本上使用具有自定义功能的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！