How to add API key to the google spreadsheet?

Question

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.

Liponis

Answer 1

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:

• Google Sheets API v3
• Migrate to the Google Sheets API
• Method: spreadsheets.values.get of Sheets API v4

Bordy

By doing this, I still get a 403 unauthorized. – Minnick

@Minnick 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. – Bordy

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. – Minnick

@Minnick 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. – Bordy

@Minnick 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. – Bordy

hopefully, I just need read access. Why is google making those authentication process so difficult 😅 – Minnick

"When the API key is used, the Spreadsheet is required to be publicly shared" > any reference for this? My URL looks like this: sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}/values/${SHEET_NAME}?key=${API_KEY} Should I add some headers too? I am trying to setup a service account to allow keeping the spreadsheet private – Teerell

@Eric Burel About your questions, I would like to support you. So, can you post it as a new question? If you can cooperate to resolve your questions, I'm glad. Can you cooperate to do it? – Bordy

Answer 2

Here is example with npm google-spreadsheet@^4.1.2 package: //Shared google sheet doc for [https]://docs.google.com/spreadsheets/d/[SPREADSHEETID]/edit?usp=sharing

import { GoogleSpreadsheet } from 'google-spreadsheet'

const spreadsheetId = "[SPREADSHEETID]";
const auth = {
    apiKey: '[YOUR_API_KEY]'
}

const doc = new GoogleSpreadsheet(spreadsheetId, auth);

await doc.loadInfo(); // loads document properties and worksheets
console.log(doc.title);

Topotype