React JS and Google Spreadsheets: Cannot Read Property '2' of Null

Question

I'm currently working with React JS and Google Spreadsheets and am following something similar to this article. However, I'm running into a problem. Whenever I try to connect to my spreadsheet, my React app runs successfully for a few seconds and then crashes, with this error message:

Currently, this is my code:

import {
  GoogleSpreadsheet
} from 'google-spreadsheet';

function App() {
  const setup = async () => {
    const doc = new GoogleSpreadsheet('my spreadsheet-id'); // Obviously putting in my real spreadsheet id and data instead of this in my real code
    await doc.useServiceAccountAuth({
      client_email: 'my client-email',
      private_key: 'my private-key'
    });

    await doc.loadInfo(); // loads document properties and worksheets

    const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id]
    console.log(sheet.title);
    console.log(sheet.rowCount);
  }

My app function does have a render method and I do eventually run the setup function to connect to my spreadsheet.

Does anyone know why this is happening and how I can fix it? Thanks!

I fixed it (the issue was with my private key), but thanks for the help! — Tejas_hooray, Oct 19 '20 at 19:38
Hi @HelloWorld, I'm having the same problem. Do you remember what you did to make it work? — Jota Renan, Jan 04 '21 at 18:33
@JotaRenan I was able to get this to work by storing the variables in a .env file, and then accessing them from there. I also had my private key equal to everything that the google dev console gave me, including the part where it says "Begin Private Key". Hope that helps. — Tejas_hooray, Jan 04 '21 at 20:01
I appreciate your reply! Apparently I had a trailing '\n' character on my private key. This was causing a regex mismatch. Still, it's not working yet due to another exception, now, haha. — Jota Renan, Jan 05 '21 at 15:06
@JotaRenan mind telling me what's wrong? I might be able to help... — Tejas_hooray, Jan 06 '21 at 02:01

score 8 · Answer 1 · answered Feb 09 '21 at 07:15

I figured this out after a while... To those who found this StackOverflow thread as their only hope, I'll leave this answer here to save some of your hours.

Apparently, if you are storing the private token inside your .env, the \n character will be parsed in as \\n. You would need to make sure that the slashes are NOT escaped.

For my project, this is the solution:

await doc.useServiceAccountAuth({
  client_email: 'my client-email',
  private_key: process.env.REACT_APP_SHEET_PRIVATE_KEY.replace(/\\n/g, '\n')
});

Pawel · Answer 2 · 2021-01-22T14:38:21.730

2

Make sure you have turned on your api library as it gives the same error when api that you use is turned off.

edited Jan 22 '21 at 14:38

answered Jan 21 '21 at 12:35

Pawel

78
9

This reads more like a Comment than an Answer. As such, it nasty be subject to deletion. Consider editing if you have more insights to share. – SherylHohman Jan 21 '21 at 13:05

score 1 · Answer 3 · answered Sep 03 '21 at 13:56

1

Also check to have the latest version of this library, because old versions use v3 Google API, which is been deprecated. The latest versions use v4, and avoid auth problems. https://theoephraim.github.io/node-google-spreadsheet/#/

answered Sep 03 '21 at 13:56

Manuel Gonzalez Garcia

37
5

score 0 · Accepted Answer · answered Feb 10 '21 at 17:17

0

For me, the issue was also with the API key. I was under the impression that I was supposed to delete the part which said "Begin Private Key...". People having this issue should check their API keys.

answered Feb 10 '21 at 17:17

Tejas_hooray

606
1
6
16

so do we need to delete that part? – coinhndp Feb 02 '22 at 14:10
No, you leave that part and just copy and paste what they give you. – Tejas_hooray Feb 08 '22 at 02:08

score 0 · Answer 5 · answered Jun 12 '22 at 20:33

I've encoded the private key in base64, then pasted it in .env

Then put in my code:

const private_key = Buffer.from(GOOGLE_SA_PRIVATE_KEY, 'base64').toString('utf8')

const doc = new GoogleSpreadsheet(SPREADSHEET_ID)

doc.useServiceAccountAuth({
  // env var values are copied from service account credentials generated by google
  // see "Authentication" section in docs for more info
  client_email: GOOGLE_SA_EMAIL,
  private_key: private_key.replace(/\\n/g, '\n'),
})

React JS and Google Spreadsheets: Cannot Read Property '2' of Null

5 Answers5