Unable to use Google Sheets API, can't generate correct credential elements

Question

I'm a big fan of Google Sheets API v4. I did a project there four months ago, worked great. No go now. Not sure what happened, but the credentials created via https://console.developers.google.com/apis/credentials?project=my-project-sheets-api don't seem to have the same content as things I set up just four or five months ago.

5 month old project credentials:

{
    "installed": {
        "client_id": "4xxxxxxxxxx1-3xxxxxxxxxxg.apps.googleusercontent.com",
        "project_id": "previous-project",
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://www.googleapis.com/oauth2/v3/token",
        "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
        "client_secret": "wxxxxxt",
        "redirect_uris": [
            "urn:ietf:wg:oauth:2.0:oob",
            "http://localhost"
        ]
    }
}

New project credentials:

{
  "type": "service_account",
  "project_id": "my-new-project-sheets-api",
  "private_key_id": "9xxxa",
  "private_key": "-----BEGIN PRIVATE KEY-----\nxxxxxxQ=\n-----END PRIVATE KEY-----\n",
  "client_email": "my-new-project-sheets-api@my-new-project-sheets-api.iam.gserviceaccount.com",
  "client_id": "1xxx7",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/my-new-project-sheets-api%40my-new-project-sheets-api.iam.gserviceaccount.com"
}

I don't use these consoles everyday. In this case I used the "help me choose" wizard at https://console.cloud.google.com/apis/credentials?folder=&organizationId=&project=my-new-project-sheets-api

From what I can see of the sheets API v4, is that I'm unable to generate token without a client_id, client_secret, redirect_uris[0]

Using the wizard doesn't generate a client-secret, nor ever ask for redirect_uris . How do I generate the correct set of credentials for a Google sheets read/write? Note: I'm only trying to read/write on a Google sheet that I've created, not other users' individual sheets. And I'm only accessing the content on that Google sheet from a Node.js server, not from a client.

And as to the specific error that got me here, it's TypeError: Cannot destructure property 'client_secret' of 'undefined' or 'null' at authorize(); reference Quick start code shown at https://developers.google.com/sheets/api/quickstart/nodejs

function authorize(credentials, callback) { const {client_secret, client_id, redirect_uris} = credentials.installed;

How does one generate the correct credentials for Google Sheets API v4 ?

You are downloading / creating the wrong type of credentials. You now have a service account. Use the option "Help me choose" or the option "OAuth Client ID". Note: You can use service account credentials, but the code that uses them will be different. — John Hanley
Yes I know those are the wrong type of credentials. Help me choose --> led me to this credential. OAuth Client ID = "The consent screen tells your users who is requesting access to their data and what kind of data you're asking to access." Thats NOT what I'm doing. I'm ONLY using MY OWN private Google Sheet. I think the problem is in recent changes to the Google Console credential generator (and / or the "Help Me Choose" wizard design) I will try the OAuth Client ID (and API key) and see what happens. — zipzit

zipzit zipzit · Accepted Answer · 2019-04-25T23:28:16

So I did have to generate OAuth 2.0 client IDs. That gave me a client_id and client_secret.

Again, my use case is:

Data stored in Google sheet under my Google login and ownership.
Google sheet is private
All data access is via nodejs server and not ever via client.

Update: I originally attempted to use Web Client type of credentials. I also created my own custom credentials.json file. Total fail.

Here is how I created the correct client_secret.json file:

Over at https://console.cloud.google.com/apis/credentials, select your project in the upper blue bar pulldown.
Hit the button to Create Credentials.
Select OAuth Client ID.
Application Type = Other.
I renamed my new credential "Node Server".
The system will create a client_id and client_secret.
After you refresh the page, you will see an overview of your credentials.
On the right hand side of OAuth 2.0 client IDs, "Node Server" there is a pencil icon (edit). Click on the pencil.
At the top of the next screen click on the "Download JSON" link.

The product you receive will look like:

{
    "installed": {
        "client_id": "xxx.apps.googleusercontent.com",
        "project_id": "xxx-sheets-api",
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://oauth2.googleapis.com/token",
        "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
        "client_secret": "xxx",
        "redirect_uris": ["urn:ietf:wg:oauth:2.0:oob", "http://localhost"]
    }
}

And this content, exactly matches the formats and functions called out in the samples code at https://developers.google.com/sheets/api/quickstart/nodejs.

The "help me choose" wizard wasn't the right choice for my use case.

Unable to use Google Sheets API, can't generate correct credential elements

1 Answers