Error in connection with google sheet

Hi
Unfortunately, I have below error when I want to connect to google sheet api:

ERROR: error:0909006C:PEM routines:get_name:no start line
Error: error:0909006C:PEM routines:get_name:no start line
at Sign.sign (internal/crypto/sig.js:80:26)
at Object.sign (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\jwa\index.js:152:45)
at Object.jwsSign [as sign] (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\jws\lib\sign-stream.js:32:24)
at GoogleToken.requestToken (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\gtoken\build\src\index.js:188:31)
at GoogleToken.getTokenAsync (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\gtoken\build\src\index.js:127:21)
at GoogleToken.getToken (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\gtoken\build\src\index.js:69:21)
at JWT.refreshTokenNoCache (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\jwtclient.js:129:36)
at JWT.refreshToken (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\oauth2client.js:140:25)
at JWT.authorizeAsync (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\jwtclient.js:112:35)
at JWT.authorize (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\jwtclient.js:108:25)

Can anyone help to solve this error?

Sorry are on the train right now and do not have internet on my notebook. Hope I am anyway able to debug that from my phone.

It could have to do with the private key. In the JSON file which does get downloaded from Google. It contains the newline characters \n instead of actual new lines. When copying it you have to make sure to replace them with actual new lines. So best to hover over the input and click on the edit-icon-window which appears on the right. In the then appearing multil-ine input you can then simply replace and save the changed key. Hope that works!

Ah and just to be sure. The email address in the Google credentials is the one from the service account which can also be found in the JSON file.

Something like: [email protected]

I try all you said but unfortunately I yet see this error :pensive::pensive::pensive:

Actually still looks like everything is just one line instead of multiple ones.You really have to replace all “\n” with an actual new line. So that you have mutlple lines which all have exactly the same length.

So have to look like that:
Screenshot%20from%202019-09-12%2019-30-44

As long as there is anywhere still a \n in there it will not work.

thanks
I checked again and unfortunately now I have below error:

ERROR: error:09091064:PEM routines:PEM_read_bio_ex:bad base64 decode

Error: error:09091064:PEM routines:PEM_read_bio_ex:bad base64 decode
    at Sign.sign (internal/crypto/sig.js:80:26)
    at Object.sign (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\jwa\index.js:152:45)
    at Object.jwsSign [as sign] (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\jws\lib\sign-stream.js:32:24)
    at GoogleToken.requestToken (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\gtoken\build\src\index.js:188:31)
    at GoogleToken.getTokenAsync (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\gtoken\build\src\index.js:127:21)
    at GoogleToken.getToken (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\gtoken\build\src\index.js:69:21)
    at JWT.refreshTokenNoCache (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\jwtclient.js:129:36)
    at JWT.refreshToken (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\oauth2client.js:140:25)
    at JWT.authorizeAsync (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\jwtclient.js:112:35)
    at JWT.authorize (C:\Users\Administrator\AppData\Roaming\npm\node_modules\n8n\node_modules\google-auth-library\build\src\auth\jwtclient.js:108:25)

In that case it means you still have a \ backslash in the “Private Key”. You really have to make sure that there are no \n left ANYWHERE. I can reproduce the above error very easily by just adding a backslash anywhere in the “Private Key” as it is not part of base64 and so causes this error.

It is probably best if you simply delete what you currently have as key and then copy it again from the file. Then really go through and make sure you do not forget to replace any. Just to be sure copy it in some kind of text editor and search for \n that you can really not overlook any.

All lines have exactly the same length except the first and the two last ones. So maybe your current error is in any of those lines.

1 Like

hey guys,
I spent an hour searching but don’t find it: Where do you get the private key from?
When I download the credentials JSON there is only this:
{“installed”:{“client_id”:“76669051XXXX-i15ttbigtaismmpgm42ri5hlh0vqd4ql.apps.googleusercontent.com”,“project_id”:“quickstart-156865780XXXX”,“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”:“bylq-DIV_CqmCZ-XXXXXXXXX”,“redirect_uris”:[“urn:ietf:wg:oauth:2.0:oob”,"http://localhost”]}}

Sorry right now in transit and do not have a computer. Look on Google for creating a Google Service Account. And when you did that, you can choose to download the credentials as JSON which then contains the key.

They describe how to create it very good:

2 Likes

Thanks for the useful link. I got a service account with the owner role and a private key. But I tried everything under the moon to get the message away. Private key does not contain \ or \n anymore. I tried it with and without the -----BEGIN PRIVATE KEY-----. I shared the doc with the service accounts e-mail address. What am I missing here?

solve:
actually it is not all in one line but:
-----BEGIN PRIVATE KEY-----
HERECOMESTHEENTIREKEYINONELINE
-----END PRIVATE KEY-----

Glad that you found the solution yourself even though you could probably have saved a lot of time if you would have read the messages above :wink:

I wish the nodes would point to the right place where to obtain the credentials. I clearly did not create the right kind of API key.

Would be great to have a link to that tutorial shown in the node.

1 Like

Yes totally agree! Is on my list since months to create instructions how to create credentials for each service. Any contributions there are welcome.

The Google credentials are actually the only ones for which a tutorial exists as @svenjanssen created one which can be found here: https://www.youtube.com/watch?v=KFqx8OmkqVE

1 Like

just let me know in the video-comments when you experience issues