Skip to content
Advertisement

NodeJS accessing Google SpreadSheets

I made this code (index.js):

const { GoogleSpreadsheet } = require('google-spreadsheet')
const credentials = require('./credentials.json')
const { promisify } = require('util')

const docId = '1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c'
const accessSheet = async() => {
    const doc = new GoogleSpreadsheet(docId)
    await promisify(doc.useServiceAccountAuth)(credentials)
    const info = await promisify(doc.getInfo)()
    const worksheet = info.worksheets[0]
    const rows = await promisify(worksheet.getRows)({

    })
    rows.forEach(row => {
        console.log(row.Title)
    })
}
accessSheet()

I’m trying to connect my code with a google spreadsheet. For the connection, I’m using node.js, but I’m getting this error message.

C:Usersrodrigo.pasiniDesktoprobo_grprnode_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:101
    this.jwtClient = new JWT({
                   ^

TypeError: Cannot set properties of undefined (setting 'jwtClient')
    at useServiceAccountAuth (C:Usersrodrigo.pasiniDesktoprobo_grprnode_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:101:20)
    at node:internal/util:360:7
    at new Promise (<anonymous>)
    at useServiceAccountAuth (node:internal/util:346:12)
    at accessSheet (C:Usersrodrigo.pasiniDesktoprobo_grprindex.js:9:47)
    at Object.<anonymous> (C:Usersrodrigo.pasiniDesktoprobo_grprindex.js:19:1)
    at Module._compile (node:internal/modules/cjs/loader:1101:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)

Does anyone know why I’m getting this error? What am I doing wrong?

Answer

When I saw your showing script and your error message, I’m worried that the current version of google-spreadsheet you are using might be different from your showing script. So, in this answer, I would like to propose a sample script converted from your showing script using the latest version of google-spreadsheet.

When I check the latest version of google-spreadsheet, it seems that it’s 3.2.0. So, first, please check the version of google-spreadsheet you are using. And, please install the latest one.

When your script is modified for using with the latest one (v3.2.0), it becomes as follows.

Modified script:

const { GoogleSpreadsheet } = require("google-spreadsheet");

const creds = require('./credentials.json');
const doc = new GoogleSpreadsheet('###'); // Please set your Spreadsheet ID.

const accessSheet = async () => {
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  const worksheet = doc.sheetsByIndex[0];
  const rows = await worksheet.getRows();
  rows.forEach((row) => {
    console.log(row.Title);
  });
};
accessSheet();

Reference:

Advertisement