2

I am trying to use Google Spreadsheet API v4 to "add a row" in spreadsheet using an "ApiKey" directly without using an OAuth 2.0.

If I am using OAuth 2.0 its successfully "adding a row" but my requirement is to use "ApiKey"

Referred this URL: https://developers.google.com/sheets/api/guides/migration#add_a_new_row_of_data

But there is no proper document for using "APIKey ". When I tried using "Apikey" it is throwing me an error of "Unauthorized (401)".

halfer
  • 18,701
  • 13
  • 79
  • 158
snehamayi senapati
  • 85
  • 7
  • Do you know Google app script? You can write your own API's / functions for the spreadsheet CRUD operations and deploy the script. – Nitin Dhomse Jan 08 '18 at 05:50
  • https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values/A1:append?valueInputOption=RAW&key=${mykey} -->i am using this url to adding a row in spreadsheet bt its showing me 401 unauthorized..Please help me out. – snehamayi senapati Jan 08 '18 at 06:35
  • Did you give the write permissions to spreadsheet? – Nitin Dhomse Jan 08 '18 at 06:42
  • yes ... bt for once recheck from my side can you please tell me the steps to give write permission. – snehamayi senapati Jan 08 '18 at 07:38
  • Follow the steps given here (1st answer) https://stackoverflow.com/questions/37315266/google-sheets-api-v4-receives-http-401-responses-for-public-feeds – Nitin Dhomse Jan 08 '18 at 07:40
  • I made the spreadsheet "Published To The Web" but still i am facing same issue. i want to add a row in spreadsheet using the apikey not Oauth2.0.Used teh following url. https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values/A1:append?valueInputOption=RAW&key=${key}..plz check – snehamayi senapati Jan 08 '18 at 08:58
  • Is this possible using api key to insert a row in spreadsheet using the POST URL? not using an oAuth 2.0 – snehamayi senapati Jan 08 '18 at 09:11
  • Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer Jan 08 '18 at 18:24
  • Hi ,anyone could reply please as this is my priority task.. – snehamayi senapati Jan 09 '18 at 06:27

1 Answers1

2

While the documentation isn't entirely clear, it is possible to add rows to a spreadsheet without needing to go through the OAuth flow where the app has to obtain consent from a specific user.

The flow, when using a service account, is very similar to the flow when using an APIKey, except a service account can be given explicit authorization to write to the sheet.

This works because a service account is like a user, just one that is not human. The process is as follows:

  • Go to the Google Cloud Console and create a service account in your GCP project. (If you don't have a project, you may need to create one.)
  • Create a key and download it as a JSON file. The JSON file will contain a private key, a key id, an email address for the service account, and other information. Keep this in a safe place. It's sensitive data.
  • Next, in your spreadsheet, add the service account email address as a user with edit access.

Afterwards, you can now use the service account client_email and the private_key from the JSON file in order to create a JWT token to authorize the request. You can now write to the sheet, since the service account is an authorized editor in the sheet.

See Using OAuth 2.0 to Access Google APIs - Service Accounts for more information. Note that although the documentation makes it sound like the service account can't access user data, it can in this case since we add the service account as an editor on the sheet.

Also, see Using OAuth 2.0 for Server to Server Applications.

Now, I will say that your question states that you "require using an APIKey". Instead, I'm assuming that your requirement is that the app must be able to write to the sheet without prompting users for authentication. If that's the case, then this solves your problem, but if the question really involves using an APIKey, then the only way to do that is to make the sheet publicly writeable to all unauthenticated users.

I also don't know what programming language you're using, so I'll leave the creation of a JWT token as an exercise for the reader, and the linked documentation also describes the process in Java, Python, and as a raw HTTP/REST request. Hope this helps!

jmort253
  • 32,054
  • 10
  • 92
  • 114