6

I have an app that allow me to read the data from Google Spreadsheet using API Key. I just make HTTP GET to this address and get a response with data.

https://sheets.googleapis.com/v4/spreadsheets/18soCZy9H4ZGuu**********BeHlNY1lD8at-Pbjmf8c/values/Sheet1!A1?key=AIzaSyAYJ***********pB-4iKZjYf4y0vhXP8OM

But when I try to do same to write data using HTTP PUT to address

https://sheets.googleapis.com/v4/spreadsheets/18soCZy9H4ZGuu**********BeHlNY1lD8at-Pbjmf8c/values/Sheet1!A4?valueInputOption=RAW?key=AIzaSyAYJ***********pB-4iKZjYf4y0vhXP8OM

its gives me 401 error. Code to make PUT request:

 using (WebClient wc = new WebClient())
 {
     byte[] res = wc.UploadData(link, "PUT", Encoding.ASCII.GetBytes(textBox1.Text));
     MessageBox.Show(Encoding.Default.GetString(res));
 }

Also spreadsheet is fully public with permission to read and write by anyone without auth. My guess is that I can't use API Key to write data to spreadsheet, and only way to do this is using OAuth.

UPDATE: So i've just tryed Google.Apis.Sheets.v4 to write values, and now i'm almost 100% sure that API Key can't be used to write data to Google Spreadsheet. Well, then I'll use OAuth 2.0.

Juhi Matta
  • 481
  • 1
  • 5
  • 15
Yura Belov
  • 116
  • 1
  • 5
  • Looks like this issue https://issuetracker.google.com/issues/36755576 covers this bug, though it doesn't seem to have been updated since April. Might be worth starring it so they know how much it's affecting people. I think I'm going to have to switch to using OAuth 2.0 too. – user3432422 Sep 09 '17 at 00:19

3 Answers3

3

Well, maybe you are correct and the problem here is the API_KEY itself.

If you check the Sheets API documentation, it is stated that every request your application sends to the Google Sheets API needs to identify your application to Google. There are two ways to identify your application: using an OAuth 2.0 token (which also authorizes the request) and/or using the application's API key. Here's how to determine which of those options to use:

  • If the request requires authorization (such as a request for an individual's private data), then the application must provide an OAuth 2.0 token with the request. The application may also provide the API key, but it doesn't have to.

  • If the request doesn't require authorization (such as a request for public data), then the application must provide either the API key or an OAuth 2.0 token, or both—whatever option is most convenient for you.

So meaning either the OAuth 2.0 token or API key will work in your case since the file is public. But the problem is in the PUT request that you are doing, we can assume here that the API key is not working with it. But, we have alternatives for it, and that is the OAuth.

I also found here a related SO question that might help you.

Community
  • 1
  • 1
KENdi
  • 7,576
  • 2
  • 16
  • 31
  • 1
    Kindly thank you for answer. But in my case I can get data from spreadsheet using that API Key, but I can't write anything to spreadsheet Also i've tryed built in API (that one on google documents page that allow you to make requests), and when I do GET to my spreadsheet, I recive data with both API Key and OAuth. But when i try to do same to write data only OAuth works. – Yura Belov Feb 03 '17 at 15:26
3

For anyone still hoping for a simple answer, it seems there won't be one - any writing to a sheet, irrespective of the sheets permissions, will require OAuth2:

'This is intentional behavior. While public sheets are anonymously readable, anonymous edits aren't currently supported for a variety of reasons.

In this context, "anyone" == anyone with a google account.' HERE

sideroxylon
  • 4,338
  • 1
  • 22
  • 40
  • this sucks. The documentation says you can use API key to do everything you can do with OAuth. https://developers.google.com/sheets/api/guides/authorizing is it lying? – John Henckel Dec 16 '21 at 03:50
2

One option that wasn't mentioned here is to use a service account instead. Service accounts are like users, but without being attached to a person. Instead, they're attached to a project.

Service accounts have an email address as well as a private key. Both can be used to create a JWTClientAuth, and this can be used to authenticate the API while it's being instantiated or to authenticate each and every request.

The advantage of the service account is that it works like an API KEY -- no need to ask a user to copy a URL to the browser and then copy a code back into the application -- but because it can act as an authenticated user, the service account email address can be added to the Google Sheet as an editor. With this in place, the application has full write access to the sheet but without having to deal with authorization codes and refresh tokens and copy/pasting.

You can see a Python example, Python With Google Sheets Service Account Step By Step, and a Node.js example, Accessing Google APIs Using Service Account in Node.js. I followed these examples to get setup.

Since you're using C#, you may find Writing to Google Sheets API Using .NET and a Service Account to be helpful.

This method reads the service account credentials from the JSON file to then instantiate the SheetsService:

private void ConnectToGoogle() {
    GoogleCredential credential;

    // Put your credentials json file in the root of the solution and make sure copy to output dir property is set to always copy 
    using (var stream = new FileStream(Path.Combine(HttpRuntime.BinDirectory, "credentials.json"), 
        FileMode.Open, FileAccess.Read)) {
            credential = GoogleCredential.FromStream(stream).CreateScoped(_scopes);
    }

    // Create Google Sheets API service.
    _sheetsService = new SheetsService(new BaseClientService.Initializer() { 
        HttpClientInitializer = credential, ApplicationName = _applicationName 
    });
}

Afterwards, you can use the Google Sheets .NET Client Library to write the data.

jamesmortensen
  • 33,636
  • 11
  • 99
  • 120