Google Sheets, "The request is missing a valid API key"

Question

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:

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

where line 5 in the script looks like this:

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

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.

I call the function just using = function().

Scuta

See developers.google.com/sheets/api/quickstart/apps-script and developers.google.com/sheets/api/guides/authorizing – Scrummage

yes, I saw them already. In the second link, they say: "After you have an API key, your application can append the query parameter key=yourAPIKey to all request URLs." Either I don't understand this step, or I'm doing it wrong. What exactly is meant? – Scuta

Did you Turn on the Google Sheets API advanced service for this script ? – Scrummage

yes, yes, I did – Scuta

And did you enable said API in this Apps Script's Google Cloud Project? How exactly are you calling the script via the spreadsheet? Menu option? Custom function? – Landmark

yes that is enabled. I call the function just doing =function(). Is that wrong? – Scuta

Answer 1

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.

Workaround 1:

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.

Sample script:

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

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

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. 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.
2. When the custom function is run, the access token retrieved by PropertiesService is used for using Sheets API.

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

Sample script:

// 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;
}

• The expilation time of access token is 1 hour. In this sample script, PropertiesService is used. In this case, when 1 hour is spent after the Spreadsheet was opened, the access token cannot be used. If you want to use continuously the access token, you can also update it using the time-driven trigger.

Note:

• When you use Sheets API, please enable Sheets API at API console.

References:

• Custom Functions in Google Sheets
• spreadsheets.values.get
• PropertiesService

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

Blakeslee

ScriptApp.getOAuthToken() returns the token when called from a menu function, but when called in onOpen(), it returns null. Any ideas? – Topography

@Dan Dascalescu About your question of ScriptApp.getOAuthToken() returns the token when called from a menu function, but when called in onOpen(), it returns null. Any ideas?, if onOpen is to execute by a simple trigger, the reason for the issue is due to this. In this case, please use the installable OnOpen trigger. But, in that case, please rename the function name. In my answer, I have proposed to use the installable trigger. I apologize for this. – Blakeslee

Answer 2

I want to thank you, @Tanaike, for your response (I don't have enough 'points' to upvote or comment, so my only option is an 'Answer') I know this thread is several years old, but I thought others might be interested in my personal experience.

First of all: "Workaround 1" worked for me!

The function/method "Sheets.Spreadsheets.Values.get(spreadsheetID, RangeName).values" was giving me an "missing a valid API key" error, so I swapped it for "sheet.getRange(RangeName).getValues()". Most of the above was set as Global Variables, i.e. outside of any functions.

Weird thing was that this error occured only when running from within the [container] sheet, not from the "embedded" script.

For instance: If I had an active onOpen() function, and I opened/refreshed the Sheet, the script would log a "The request is missing a valid API key." error, and the UI/Menu I had built therein would not load. I could, however run the onOpen() function from within the script itself, and the menu would appear, and function, within the Sheet. If I disabled/renamed the onOpen() function, and reloaded the Sheet, I would not get the error message. Makes sense, as the simple loading of the Sheet does not appear to run the script, but when one does access it (the script), i.e. through the onOpen() function, then the initial global variables are read (and the error occurs). However, when I ran the same function, or others, from within the script itself, they would run ok. This permissions conundrum is what has led me on a wild goose chase all over the Internet, ultimately landing here.

All this after numerous other issues, in the course of whose resolution I built a Google Cloud Project, added APIs (e.g. for Sheets), added scopes to the oauthScopes section of the manifest, and more.

It was only after I made the replacements described above that everything worked, both from the script, and its container spreadsheet! So, THANKS!

Anyway... Sorry for the long post, but I hope others may benefit from your solution, and in which context it helped me.

Cheers, -Paul

Fetish