Access Google spreadsheet API without auth token

Question

I have created Google Spreadsheet, and given edit access to all (can edit even without login).

Here is the link. I would like to update this sheet with Google Spreadsheet API. But I am getting error. My requirement is update the sheet thru API even without access credential.

Answer 1

It is possible to write to spreadsheet without OAuth or API Keys. You need to use Service Account Keys.

Here is what I did for my Node.js environment.

1. Get a service account key from https://console.cloud.google.com/apis/credentials (You can here also restrict what this keys is allowed todo)
   1. When creating, make sure you click the Furnish a new private key
   2. Select JSON when it asks you how to download the key.
2. The service account key you have just generated includes a client_email.
   1. Go to you google spreadsheet and allow this client_email to have write access on this document

3. Use the following code to authenticate

   let jwtClient = new google.auth.JWT(client_email, null, private_key, [ "https://www.googleapis.com/auth/spreadsheets", ]); //authenticate request jwtClient.authorize(function(err, tokens) { // at this point the authentication is done you can now use `jwtClient` // to read or write to the spreadsheet });

client_email and private_key are part of the service account key

A more detailed description can be found here. http://isd-soft.com/tech_blog/accessing-google-apis-using-service-account-node-js/ Also, all credit goes to this page.

Answer 2

You need to be authorized to make such requests

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.

That's it. There's no bypassing authorization.

Answer 3

Finally digged deep enough and found the answer. Any kind of writing, even to publicly editable sheets requires an OAuth flow:

https://issuetracker.google.com/issues/36755576#comment3

Answer 4

Jürgen Brandstetter's answer above is completely right. Using Postman, I have been successful without using an OAuth token (I needed my personal API key and a service account) - I have written to a new sheet (in fact I did a batchUpdate operation with two steps, first create a new sheet and then pasteData on it). I followed the instructions here to create a service account, downloaded the credentials JSON and used it to create and sign a JWT string that is later used as Bearer.

Here is the Java code to obtain the JWT string:

    private static String getSignedJWT() throws IOException {
        InputStream in = YourClass.class.getResourceAsStream("/downloaded-service-account-creds.json");
        if (in == null) {
            throw new FileNotFoundException("Resource not found");
        }
        ServiceAccountCredentials serviceAccountCredentials = ServiceAccountCredentials.fromStream(in);
        GoogleCredentials googleCredentials = serviceAccountCredentials
                .createScoped(Collections.singletonList(SheetsScopes.SPREADSHEETS));

        PrivateKey privateKey = serviceAccountCredentials.getPrivateKey();
        String privateKeyId = serviceAccountCredentials.getPrivateKeyId();

        long now = System.currentTimeMillis();

        Algorithm algorithm = Algorithm.RSA256(null, (RSAPrivateKey)privateKey);
        String signedJwt = JWT.create()
                .withKeyId(privateKeyId)
                .withIssuer(serviceAccountCredentials.getClientEmail())
                .withSubject(serviceAccountCredentials.getClientEmail())
                .withAudience("https://sheets.googleapis.com/")
                .withIssuedAt(new Date(now))
                .withExpiresAt(new Date(now + 3600 * 1000L))
                .sign(algorithm);

        return signedJwt;
    }

Dependencies needed: com.auth0:java-jwt and com.google.auth:google-auth-library-oauth2-http.

Here's the curl that uses the JWT string generated above:

curl --location --request POST 'https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID:batchUpdate?key=ANY_PERSONAL_API_KEY' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer YOUR_JWT_STRING' \
--data-raw '{
    "requests": [
        {
            "addSheet": {
                "properties": {
                    "title": "newPred",
                    "sheetId": 0
                }
            }
        },
        {
            "pasteData": {
                "coordinate": {
                    "columnIndex": 0,
                    "rowIndex": 0,
                    "sheetId": 0
                },
                "delimiter": "\t",
                "data": "col1\tcol2\nPeter\t25",
                "type": "PASTE_NORMAL"
            }
        }
    ]
}'

Answer 5

Not exactly what asked but here is a solution with google-api-nodejs-client worked for me:

const { google } = require('googleapis');
const sheets = google.sheets('v4');
const spreadsheetId = '.........';

(async () => {

  const auth = new google.auth.GoogleAuth({
    keyFile: './my_service_account_privatekey.json',
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  });

  // get
  try {
    const getResult = await sheets.spreadsheets.values.get({
      auth,
      spreadsheetId,
      range: 'Sheet1!B2:C4'
    })
    console.log('Got values:', getResult.data.values)
  } catch (error) {
    console.error('Get error:', error)
  }


})()

docs: https://github.com/googleapis/google-api-nodejs-client#using-the-keyfile-property