HTTP Google Sheets API v4 how to access without OAuth 2.0?

Question

my idea is to create a google sheet, make it public and then access it from my work computer linux/bash to read/write values on a daily basis.

i have a public google doc sheet that anyone can find/edit. this is the sheet ID: 1F6jh6756xNDlDYIvZm_3TrXb59EFEFHGEC7jdWz-Nx0

doing it by the book https://developers.google.com/sheets/api/samples/reading

curl 'https://sheets.googleapis.com/v4/spreadsheets/1F6jh6756xNDlDYIvZm_3TrXb59EFEFHGEC7jdWz-Nx0/values/Sheet1!A1:A3'

returns:

{
  "error": {
  "code": 403,
  "message": "The request is missing a valid API key.",
  "status": "PERMISSION_DENIED"
  }
}

i've read a lot and especialy here Google Sheet API v4 i've found a complicated solution. that is if you want to access your public sheet in a short 1 hour period.

you browse to https://developers.google.com/oauthplayground/ get authorization for the v4 api, then get "Authorization code", then get "Refresh token", and finally "Access token".

using this "Access token" you can access the public sheet like this
curl 'https://sheets.googleapis.com/v4/spreadsheets/1F6jh6756xNDlDYIvZm_3TrXb59EFEFHGEC7jdWz-Nx0/values/Sheet1!A1:A3' -H "Authorization: Bearer ya29.GlvaBLjrTdsSuSllr3u2nAiC-BOsjvIOE1x5afU3xiafB-FTOdLWDtfabuIMGF1rId5BsZxiTXxrx7VDEtxww4Q1uvW9zRndkfm3I2LZnT1HK2nTWzX_6oXu-NAG"

returns:

{
  "range": "Sheet1!A1:A3",
  "majorDimension": "ROWS",
  "values": [
        [
      "a1"
    ],
    [
      "a2"
    ],
    [
      "a3"
    ]
  ]
}

perfect. in theory the "Access token" expires after an hour, the "Refresh token" never expires. so you would save the tokens, try to read the sheet with the "Access token", if it fails use the "Refresh token" to gain a new "Access token" and carry on.

but, i've had a dozen of "Refresh token"s that were redeemed/expired, "Authorization code"s expired, all in all nothing works after a few hours. why?

how can i access my google sheet form bash with curl without this kind of authorization? especially since my sheet is public and can be edited by anyone with a browser.

is there another way to do this with some other permanent authorization? why not use email and pass?

"API key" is mentioned but never explained. can some one please explain this method step by step?

Linda Lawton - DaImTo · Accepted Answer · 2021-05-07T09:51:52.400

32

All Google APIs require that you create a project on Google developer console and identify yourself and your application, even to access public data. Since you have set the sheet to public you can just go to google developer console and create a public api key remember to active the google sheets api. Then just add key=[YourKey] as a parameter on your request.

Update Dev console:

Create project and get key:

Google developer console -> create a project -> credentials drop down -> API Key

Enable it:

Google developer console -> library find sheets enable it.

Update:

{ "error": { "code": 403, "message": "The request is missing a valid API key.", "status": "PERMISSION_DENIED" } }

Means that you have not identified yourself to Google. In 2015 Google Start to require that we identify ourselves you cant just use a Google API without telling google who you are. You do that by creating a project on [Google developer console1. Create an API key and use that API key in all of your requests. This only works with Public data.

https://sheets.googleapis.com/v4/spreadsheets/1F6jh6756xNDlDYIvZm_3TrXb59EFEFHGEC7jdWz-Nx0/values/Sheet1!A1:A3?key=YOurKEY

Note: With private user data you would need to use OAuth and use either access_token=your token or set the header

Authorization: Bearer ya29.GlvaBLjrTdsSuSllr3u2nAiC-BOsjvIOE1x5afU3xiafB-FTOdLWDtfabuIMGF1rId5BsZxiTXxrx7VDEtxww4Q1uvW9zRndkfm3I2LZnT1HK2nTWzX_6oXu-NAG.

An access token is not the same as a API Key.

edited May 07 '21 at 09:51

answered Oct 05 '17 at 10:21

Linda Lawton - DaImTo

106,405
32
180
449

can you please describe in steps how would you activate the google sheets api and create a public api key for the public sheet i have created with sheetID = `1F6jh6756xNDlDYIvZm_3TrXb59EFEFHGEC7jdWz-Nx0` . i just followed your link and i got "Failed to load" message. – Oct 05 '17 at 13:42
The pictures are probably a bit out of date but this is all i have at the moment http://www.daimto.com/google-developer-console-create-public-api-key/ – Linda Lawton - DaImTo Oct 05 '17 at 13:45
The first sentence is incorrect. [Sheets API v3](https://developers.google.com/sheets/api/v3/), which is still available and not yet deprecated, allows anonymous access to the sheets that were made available to everyone. (Which may be something for @b'stard to use,) – Oct 05 '17 at 14:15
1

all Google API require a key now it won't matter v3 or v4. I suggest you try it. – Linda Lawton - DaImTo Oct 05 '17 at 14:32
i've found some examples on stackoverflow saying they cannot do it with v4 but that it is working with v3 API, like [link](https://stackoverflow.com/questions/39470039/permission-denied-on-google-spreadsheet-rest-api-v4?noredirect=1&lq=1) and [link](https://stackoverflow.com/questions/44750946/google-sheet-api-v4) but for me it was request denied. – Oct 05 '17 at 14:39
I suspect what you are looking at is prior to 2015 when Google changed things – Linda Lawton - DaImTo Oct 05 '17 at 14:45
@DaimTo i've read the link you've posted and the 3 prequels to it. i still don't get it. i somehow created unrestricted API key 2 days ago, but it does not work when i try to use it like this `curl 'https://sheets.googleapis.com/v4/spreadsheets/1F6jh6756xNDlDYIvZm_3TrXb59EFEFHGEC7jdWz-Nx0/values/Sheet1!A1:A3&key=[MY_KEY]'` i think this is the way you would use it with v3 API. can you give me an example for v4 API? do i have to embed the google sheet into my Project? the sheet was created another 2 days before the Project. – Oct 05 '17 at 14:55
i get this `{ "error": { "code": 403, "message": "The request is missing a valid API key.", "status": "PERMISSION_DENIED" } } ` also look at this [thread](https://stackoverflow.com/questions/37315266/google-sheets-api-v4-receives-http-401-responses-for-public-feeds) – Oct 05 '17 at 15:08
You are missing a valid api key check Update Dev console: – Linda Lawton - DaImTo Oct 06 '17 at 06:33
2

When I am trying to write a sheet using API key that time getting error { "error": { "code": 401, "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.", "status": "UNAUTHENTICATED" } } POST: https://sheets.googleapis.com/v4/spreadsheets/1dgdfgdgdftry59595euDfxtqkC56656544o:batchUpdate?key=:FGzaSyC3B_kjvqoFJKC1oo – Mahesh Vayak Nov 06 '18 at 05:01
You need to be logged in and have permission to access the users sheet before you can read or write to it – Linda Lawton - DaImTo Nov 06 '18 at 07:35

score 3 · Answer 2 · answered Apr 13 '21 at 15:16

If you don't want to mess with OAuth2, maybe the simpler solution is to use third-party soft. https://sheetdb.io allows you to work with Google spreadsheets and does OAuth2 for you. Additionally, the API is simpler than Google, check out the docs here: https://docs.sheetdb.io

Tom Larkworthy · Answer 3 · 2020-03-22T08:39:52.403

1

Another approach is to share the sheet with a Google Cloud service account, and then use the service account to authorize with the API client. If the document is public you do not need to share it.

Instructions on creating a service account and reading a Google doc, which should also apply to sheets https://www.futurice.com/blog/read-goog-doc-using-service-account

edited Mar 22 '20 at 08:39

answered Mar 22 '20 at 08:28

Tom Larkworthy

2,104
1
20
29

George Smith · Answer 4 · 2023-02-20T12:09:03.597

A piece of advice - NEVER allow write access to a public Google Sheet. Anyone can add lots of data and cause all sort of problems.

Always control access to who can edit.

Allow arbitrary edits by anyone programmatically is even worse. I can whip up a script that fill in your Google Sheet to capacity in seconds with garbage data. To everyone else viewing it, the sheet will look like a jumble of data.

Now to answer your question. Here are the basic steps for creating an OAuth 2.0 ClientID:

In the Google Cloud console, go to Menu menu > APIs & Services > Credentials.

Go to Credentials Click Create Credentials > OAuth client ID. Click Application type > Desktop app.
In the Name field, type a name for the credential. This name is only shown in the Google Cloud console.
Click Create. The OAuth client created screen appears, showing your new Client ID and Client secret.
Click OK. The newly created credential appears under OAuth 2.0 Client IDs.
Save the downloaded JSON file as credentials.json, and move the file to your working directory.

Follow the rest of the instructions for setting up the environment for a Go app to authenticate, authorize and manipulate the Google Sheet via Golang. For other languages, select the corresponding entry in the left sidebar of the web page.

HTTP Google Sheets API v4 how to access without OAuth 2.0?

4 Answers4

Linked