Can't get google sheets node to authenticate

Questions
1

I’m a newbie here, on a new windows install of the n8n app.
I thought I should have enough experience to figure this out but I guess not, so I give up and and I’m asking for help. :slight_smile: Thank you to anyone willing to lend their time.

I started with a “Append, Lookup, Update, and Read Data from a Google Sheets Spreadsheet” template (I haven’t really changed it at all yet, other than trying to connect my google account). I FINALLY got though the trouble of creating a google API service account and connecting it to n8n. Thanks to the last couple comments in the thread here:

I’m pretty sure the service account connected correctly, because (I think) I finally got past that error message, but I’m still getting this error when I try to execute the google sheets append node:

{

“library”: “PEM routines”,

“function”: “OPENSSL_internal”,

“reason”: “NO_START_LINE”,

“code”: “ERR_OSSL_PEM_NO_START_LINE”,

“statusCode”: “401”

}

Here’s my workflow:

Here’s the full output from that node:

Time

3/8/2022, 10:11:30 AM

HTTP Code

401

Cause
Data below may contain sensitive information. Proceed with caution when sharing.

{

“library”: “PEM routines”,

“function”: “OPENSSL_internal”,

“reason”: “NO_START_LINE”,

“code”: “ERR_OSSL_PEM_NO_START_LINE”,

“statusCode”: “401”

}

Stack

NodeApiError: Authorization failed - please check your credentials
    at Object.googleApiRequest (C:\Users\mj\AppData\Local\Programs\n8n\resources\app\node_modules\n8n-nodes-base\dist\nodes\Google\Sheet\GenericFunctions.js:43:15)
    at async GoogleSheet.getData (C:\Users\mj\AppData\Local\Programs\n8n\resources\app\node_modules\n8n-nodes-base\dist\nodes\Google\Sheet\GoogleSheet.js:34:26)
    at async GoogleSheet.convertStructuredDataToArray (C:\Users\mj\AppData\Local\Programs\n8n\resources\app\node_modules\n8n-nodes-base\dist\nodes\Google\Sheet\GoogleSheet.js:242:31)
    at async GoogleSheet.appendSheetData (C:\Users\mj\AppData\Local\Programs\n8n\resources\app\node_modules\n8n-nodes-base\dist\nodes\Google\Sheet\GoogleSheet.js:106:22)
    at async Object.execute (C:\Users\mj\AppData\Local\Programs\n8n\resources\app\node_modules\n8n-nodes-base\dist\nodes\Google\Sheet\GoogleSheets.node.js:980:34)
    at async C:\Users\mj\AppData\Local\Programs\n8n\resources\app\node_modules\n8n-core\dist\src\WorkflowExecute.js:451:47
  • **n8n version: 1.3.0

Note sure about these two questsions:

  • Database you’re using (default: SQLite):
  • Running n8n with the execution process [own(default), main]:
2

Hey @MJ_33,

Welcome to the community :tada:

I suspect the issue is with the private key in the credentials, Did you remove the \n characters and does it start with ----BEGIN PRIVATE KEY-----?

1 Like
3

Just fyi. Removing the \n across the key is not necessary anymore in newer n8n versions.

2 Likes
4

OK guys. I feel kinda stupid but also like maybe… we need another step added in the instructions.

I was following the guide here:

And then later the thread here:

I ended up figuring it out after I watched the video from the guide above. (Google credentials | n8n Docs)

While the written instructions stop with step 14. “Save your credentials.”
It kinda leaves you hanging there. But the video at the end of the article finishes off explaining what you need to do. (Thank you for that! Sorry I ignored it the first couple go-rounds. I tend to lean toward text instructions when they’re available.)

If you wanted, you could add a couple steps to the text instructions.
I.e.
Step 15:
Open the saved json file using a text editor, ignore or delete everything preceding "-----BEGIN PRIVATE KEY-----
and ignore or delete everything after “-----END PRIVATE KEY-----”

So what you should copy from the file is just this (with no brackets or anything):

-----BEGIN PRIVATE KEY-----
(Your google service account’s private key)
-----END PRIVATE KEY-----

and paste that in to the private key field for your new google account connection in the n8n app.

I know… you shouldn’t have to dumb it down so much for me. :slight_smile:

When you know you know. And when you don’t… you struggle. lol

I had trouble with oauth too, which is why I ended up trying this. I guess at least I got one of the two solved.

Thank you for the help!

*** I highly recommend watching the video in the above link if you’re having trouble. Even though the version he’s using in the video doesn’t look quite like the windows version, it’s close enough I was able to glean the needed details.

1 Like