该请求缺少有效的API密钥 [英] The request is missing a valid API key

问题描述

我正在尝试使用Google Sheets API.问题是，一旦我在google电子表格上调用脚本函数，就会出现以下错误:

I am trying to use the Google Sheets API. The problem is, once I call my script function on the google spreadsheet, I get the following error:

对sheets.spreadsheets.values.get的API调用失败，并出现以下错误:请求缺少有效的API密钥. (第5行).

API call to sheets.spreadsheets.values.get failed with error: The request is missing a valid API key. (line 5).

脚本中的line 5如下所示:

var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;

和spreadsheetId和rangeName在第一行中定义. 我认为问题可能在于我没有在任何地方复制API密钥，但是我真的不明白我在哪里以及如何去做.

and spreadsheetId and rangeName are defined in the first lines. I think the problem might be that I did not copy the API key anywhere, but I really do not understand where and how I can do it.

我只是在做=function()调用该函数.

I call the function just doing =function().

推荐答案

当您通过将=myFunction()之类的自定义函数放在表格中使用Sheets API时，会发生此类错误.运行自定义函数时，ScriptApp.getOAuthToken()返回null.我认为这是造成您问题的主要原因.不幸的是，我认为这是规范.为了避免此问题，我想提出2个解决方法.

When you use Sheets API by a custom function like =myFunction() put to a cell, such error occurs. When the custom function is run, ScriptApp.getOAuthToken() returns null. I think that this is the mainly reason of your issue. And unfortunately, I think that this is the specification. In order to avoid this issue, I would like to propose 2 workarounds.

自定义功能可以使用Spreadsheet Services的一部分.因此，使用var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;可以获得相同的结果.对于您的脚本，不能使用openById().因此脚本如下.

A part of Spreadsheet Services can be used at the custom function. So using this, it obtains the same result with var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;. In the case of your script, openById() cannot be used. So the script is as follows.

function customFunc() {
  var rangeName = "#####"; // Please set this.

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = sheet.getRange(rangeName).getValues();
  return values;
}

解决方法2:

如果要使用Sheets API，则需要访问令牌.但是必须直接向Sheets API的端点发出请求，因为访问令牌会在Advanced Google Services的内部自动使用.这种情况的一个问题是，当在自定义函数中运行ScriptApp.getOAuthToken()时，将返回null.为了避免这种情况，作为一种方法，可以使用PropertiesService将访问令牌直接提供给自定义函数.样本流如下.

Workaround 2:

If you want to use Sheets API, the access token is required. But it is required to directly request to the endpoint of Sheets API, because the access token is automatically used in internal at Advanced Google Services. As an issue of this case, there is that when ScriptApp.getOAuthToken() is run in the custom function, null is returned. In order to avoid this, as a method, the access token is directly given to the custom function using PropertiesService. The sample flow is as follows.

1. 打开电子表格时，它将通过OnOpen触发器将访问令牌放置到PropertiesService.
   • 使用此功能时，请在示例脚本中将OnOpen触发器安装到onOpenFunc().

1. When the Spreadsheet is opened, it puts the access token to PropertiesService by the OnOpen trigger.
   • When you use this, please install the OnOpen trigger to onOpenFunc() in the sample script.

通过这种方式，可以在自定义函数中使用Sheets API.

By this, Sheets API can be used in the custom function.

// Please install OnOpen trigger to this function.
function onOpenFunc() {
  PropertiesService.getScriptProperties().setProperty("accessToken", ScriptApp.getOAuthToken());
}

function customFunc() {
  var spreadsheetId = "#####"; // Please set this.
  var rangeName = "#####"; // Please set this.

  var accessToken = PropertiesService.getScriptProperties().getProperty("accessToken");
  var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + rangeName;
  var res = UrlFetchApp.fetch(url, {headers: {"Authorization": "Bearer " + accessToken}});
  var obj = JSON.parse(res.getContentText());
  var values = obj.values;
  return values;
}

• 访问令牌的到期时间为1小时.在此示例脚本中，使用PropertiesService.在这种情况下，打开电子表格后花了1个小时，则无法使用访问令牌.如果要连续使用访问令牌，还可以使用时间驱动的触发器对其进行更新.
• 使用表格API时，请在API控制台上启用表格API.
• Google表格中的自定义功能
• spreadsheets.values.get
• PropertiesService
• Custom Functions in Google Sheets
• spreadsheets.values.get
• PropertiesService

如果这些解决方法不是您想要的，我表示歉意.

If these workarounds were not what you want, I apologize.

这篇关于该请求缺少有效的API密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！