Google Sheet can't use the function that I developed in App Script due to Missing API Key

Kevin Lu

unread,

Dec 4, 2022, 4:48:24 PM12/4/22

to Google Apps Script Community

Dear Google App Script Experts,

I have completed a function in Google App Script this script is within the Google Sheet (this script is under this Google Sheet's Extensions->App Script).

As you can see the first screenshot below, it says GoogleJsonResponseException: API call to sheets.spreadsheets.values.get failed with error: The request is missing a valid API key. (line 22).

The second screenshot is the App Script. As you can see, the issue comes from accessing the sheet.

How do I get the missing API key?

Thank you!

page110 copy.png

laurie.nason

unread,

Dec 4, 2022, 11:48:42 PM12/4/22

to google-apps-sc...@googlegroups.com

Dear Kevin,

If you are purposefully using the “Sheets.Spreadsheets.values” call - you will need to provide an API key for the project via GCP (I believe - I haven’t done much with this, but it’s required)

If you are only trying to get your sheet information you can use the regular

SpreadsheetApp.getActiveSpreadsheet().getSheetByName(’Teacher Info’).getRange(‘A2:Y100’).getValues();

And do the same for your teacher availability sheet as well.

Then you won’t need to go through the whole create API etc and it will still work fine.

Laurie

------ Original Message ------

From "Kevin Lu" <kevin.lu...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 12/5/2022 12:48:24 AM

Subject [Apps-Script] Google Sheet can't use the function that I developed in App Script due to Missing API Key

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a0c800a6-7df4-4df9-87bb-f6a07ae56d7cn%40googlegroups.com.

Kevin Lu

unread,

Dec 5, 2022, 9:30:39 AM12/5/22

to Google Apps Script Community

Dear Laurie,

But it causes an issue. Now the Apps Script no longer runs my myFunction() and I can't put myFunction() in the Google Sheet's cell as it doesn't read it (using =myFunction() in the cell). What do I do now?

Kevin Lu

unread,

Dec 5, 2022, 11:29:31 AM12/5/22

to Google Apps Script Community

Dear Laurie,

Nevermind, I have figured out the issue and have it fixed. Now I can see the data in the cell using the formula that I developed.