6

I have created a google sheet and have converted it into JSON using the URL. example: https://spreadsheets.google.com/feeds/list/SHEETID/od6/public/values?alt=json

I want to add a api key to this google sheet. I have generated a API key though Google Cloud Platform - https://console.cloud.google.com/flows/enableapi?apiid=sheets.googleapis.com

Now, how can i add this API key to the sheet?? Please could anyone help.

sfmc_newbie
  • 91
  • 1
  • 7

2 Answers2

6

I thought that your endpoint of https://spreadsheets.google.com/feeds/list/SHEETID/od6/public/values?alt=json is for Sheets API v3. When the official document of Sheets API v3 is seen, it says as follows.

Update: The Google Sheets v3 API will be shut down on June 8, 2021.

From this situation, I would like to propose to use Sheets API v4. If the Spreadsheet is publicly shared and you want to retrieve the values from the Spreadsheet, you can use the following endpoint using the API key. Ref

https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]/values/[RANGE]?key=[YOUR_API_KEY]

In this case, [SPREADSHEETID], [RANGE] and [YOUR_API_KEY] are the Spreadsheet ID, the range (you can also use the sheet name as [RANGE].) and your API key, respectively.

Note:

  • When the API key is used, the Spreadsheet is required to be publicly shared. Please be careful this. And, the API key cannot be used for the methods except for the GET method. Also, please be careful this.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • By doing this, I still get a 403 unauthorized. – Moebius Mar 31 '22 at 11:06
  • @Moebius About `By doing this, I still get a 403 unauthorized.`, when I tested my endpoint, no error occurs. But in this case, the Spreadsheet is required to be publicly shared as I have already mentioned in my answer. I'm worried about it. – Tanaike Mar 31 '22 at 12:12
  • Ok, I was trying to remove that and make my spreadsheet private. As far as I know you can't access a spreadsheet with an api key. I had to set up a service account, use the googleapi nodejs sdk and it worked. – Moebius Apr 01 '22 at 10:08
  • @Moebius Thank you for replying. About `Ok, I was trying to remove that and make my spreadsheet private. As far as I know you can't access a spreadsheet with an api key. I had to set up a service account, use the googleapi nodejs sdk and it worked.`, I think that when the Spreadsheet is publicly shared, the values can be retrieved from the publicly shared Spreadsheet using Sheets API with the API key. This OP's question is to retrieve the values from the publicly shared. So I proposed it. – Tanaike Apr 01 '22 at 11:53
  • @Moebius But, if you had been talking about writing values to Spreadsheet, in that case, it is required to use the access token retrieved by OAuth2 and the service account. – Tanaike Apr 01 '22 at 11:54
  • hopefully, I just need read access. Why is google making those authentication process so difficult – Moebius Apr 04 '22 at 09:26
3

If you'd like to make it simple, try sheetdb.io. This tool automises creating JSON API out of Google Sheets and lets you pull data from and push them to your spreadsheets.

MikoWhy
  • 31
  • 1