如何使用openByID（）获得访问Google Apps脚本中的外部电子表格的权限 [英] How to get permission to access an external spreadsheet in Google Apps Script using openByID()

问题描述

我正在尝试访问一个Google表格电子表格中的数据，以更新另一个Google表格电子表格中的条目。

I'm attempting to access data from one Google Sheets spreadsheet in order to update entries in another Google Sheets spreadsheet.

我为实现此目的而编写的代码到目前为止如下：

The code I've written to accomplish this so far is as follows:

var ss1 = SpreadsheetApp.openById("123456789").getSheetByName('Sheet1');

function onEdit (e) {
  var sheet = e.source.getActiveSheet();
  sheet.getRange("C4").setValue(ss1.getUrl());
}

但是我在目标中编辑条目时遇到以下错误触发脚本的电子表格：

But I'm getting the following error when I edit an entry in the target spreadsheet to trigger the script:

例外：您无权调用SpreadsheetApp.openById。所需权限： https://www.googleapis.com/auth/spreadsheets
在未知功能处

Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets at unknown function

如何获取运行openByID（）方法的权限，或者有更好的方法来访问外部Google表格电子表格中的数据？

How do I obtain permissions to run the openByID() method or is there a better way to access an external Google Sheets spreadsheet's data?

推荐答案

/*
 * The trigger function to call
 */
function updateOtherSheet(e) {
  var ss1 = SpreadsheetApp
  .openById("123456789")
  .getSheetByName('Sheet1');

  var sheet = e.source.getActiveSheet();
  sheet.getRange("C4").setValue(ss1.getUrl());

}

/*
 * Install the trigger by script `Run once`
 * or use the edit menu 'current project triggers'
 */
function createTrigger() {
  ScriptApp
  .newTrigger('updateOtherSheet')
  .forSpreadsheet(ss) // <- spreadsheet object var goes here !not string
  .onEdit()
  .create()
}

这篇关于如何使用openByID（）获得访问Google Apps脚本中的外部电子表格的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！