Google Sheets API authentication on Vercel

[wbm4563]

I am trying to deploy a Next js app on Vercel that has a google sheets integration. The two environment variables I need in order for it to work are the client_email and the private_key from google. However, after inputting them on the vercel env variables dashboard my api just gives this error

I have tried many different formats for the private key but none remove the error. Some common suggestions I've seen are calling the var with a replace(/\n/g, '\n') at the end, but that doesnt seem to change anything. All of it works on localhost though including the env vars inside a .env.local file getting called by process.env.VAR

[icyJoseph]

Is the key something like: -----BEGIN PRIVATE KEY-----\n[the private key]\n-----END PRIVATE KEY-----\n or more like random gibberish characters? 7c2c4a539deef1d83b222f89 for example (generic dump generated from a lambda, not an actual Private Key or anything)

[icyJoseph]

I'd try something like, and this is from a previous experience where I ended up finding a hidden character on a JS file:

1. manually write out, or copy this: -----BEGIN PRIVATE KEY-----, and -----END PRIVATE KEY-----
2. paste the key in between

I know it seems silly, but it is worth trying.

Also who's printing this error? In the api route, I guess it is the connection to GoogleSheets API? Maybe they don't want a PEM file as API key? Perhaps this is the wrong thing to use? Although you say it works locally.

One additional thing you could try is to encrypt the key, and decrypt it in the API.

[wbm4563]

The error is shown if I go to the api route yeah, vercel-domain/api/sheet. I'll give the above a try but I believe I did something along the lines of that while fiddling around with it before. I could also try hard coding the encrypted key which would probably circumvent the need to using vercels env variables dashboard.

[CrabBucket]

Hey I had the same issue here was all the things I did to make it work.

I removed all environment variables from Vercel ui on the project

I used the command vercel env add GOOGLE_API_KEY

I copied the entire string WITHOUT QUOTATIONS (VERY IMPORTANT) when asked for the value of the key.

I added this to the end of my auth insertion private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n')

I also generated an openssl NEXTAUTH_SECRET via https://www.cryptool.org/en/cto/openssl and the suggested secret command on https://next-auth.js.org/configuration/options#secret and added that key via the same vercel command.

Then deploy via vercel cli vercel --prod

[Bandikyu]

I found a rudimentary solution.

Explanation:
the private_key of the google credentials use "\n", so the key is processed in chunks by the GoogleSheets API.

Solution:

1. open Word,
2. paste the key
3. use "replace"
4. change the breaks "\n" with Word line breaks "^p"
5. copy all and use as variable in vercel

Result:
old key - example

Note:
don't forget to remove ".replace(/\n/g, '\n')", to get process.env.GOOGLE_PRIVATE_KEY

[ayfatoye]

@Bandikyu, whoever you are, I'm actually going to name my first kid after you 😭

Thank you so much for your insight on this, are you able to explain why this fixed the error?

My current understanding is that it's just a formatting thing? Like vercel expects for private keys from google that have newlines to simply not actually use the "\n" newline character?