Tag Archives: google sheets

Next.js Google Spreadsheets

Failing Build of Next.js and Google Spreadsheets

Are you trying to connect Google Sheets with your Next.js app?

I have come across an issue with failing builds when deploying to Vercel and to save you hours of debugging, here is what worked for me.

The Issue

All I wanted to do is to load data from Google Spreadsheet and display them in my React component. I am using Next.js and google-spreadsheets package.

In next.config.js I am setting up the environmental variables to be able to connect to the Spreadsheet when the page is served .

module.exports = (phase) => {
    ...
    const env = {
        ...
        private_key: (() => {
            if (isDev) return process.env.private_key
            if (isProd) return process.env.private_key
            if (isStaging) return process.env.private_key
            return 'private_key:not (isDev,isProd && !isStaging,isProd && isStaging)'
        })(),
        ...
    }
    ...
}

This worked fine locally but the build was failing when it was deployed to Vercel.

Error: error:0909006C:PEM routines:get_name:no start line

Here is the screenshot of the log.

Next.js + Google Sheets build error

After hours of debugging, calling for help and swearing at it, I had a look at how gatsby-source-google-spreadsheets works and there was the answer.

The Fix

To fix the issue I had to add .replace(/(\\r)|(\\n)/g, '\n') after the private_key.

if (isDev) return process.env.private_key.replace(/(\\r)|(\\n)/g, '\n')
if (isProd) return process.env.private_key.replace(/(\\r)|(\\n)/g, '\n')
if (isStaging) return process.env.private_key.replace(/(\\r)|(\\n)/g, '\n')

Happy times.

I am sure someone smarter than me can explain in the comments what exactly this regex does, I just know that it replaces \n with real line breaks.