Node.js when trying to access Google Spreadsheets

user_10641260 0
Viewed 1276

I'm trying to get a Discord bot to access a Google Spreadsheet, but this error keeps appearing and I can't work around it, I'm following this video (https://www.youtube.com/watch?v=UGN6EUi4Yio) and did everything up until the point where the program is ran. Any ideas how to solve this? Thank you in advance.

UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'jwtClient' of undefined
  at useServiceAccountAuth (Z:\GitHub\discordBot\node_modules\google-spreadsheet\lib\GoogleSpreadsheet.js:60:20)
  at internal/util.js:297:30
  at new Promise (<anonymous>)
  at useServiceAccountAuth (internal/util.js:296:12)
  at accessSpreadsheet (Z:\GitHub\discordBot\sheets\sheets.js:8:45)
  at Object.module.exports.run (Z:\GitHub\discordBot\comandos\duvida.js:51:5)
  at Client.<anonymous> (Z:\GitHub\discordBot\index.js:63:21)     
  at Client.emit (events.js:311:20)
  at MessageCreateHandler.handle (Z:\GitHub\discordBot\node_modules\discord.js\src\client\websocket\packets\handlers\MessageCreate.js:9:34)
  at WebSocketPacketManager.handle (Z:\GitHub\discordBot\node_modules\discord.js\src\client\websocket\packets\WebSocketPacketManager.js:105:65)

My sheets.js file:

const { GoogleSpreadsheet } = require('google-spreadsheet');
const { promisify } = require('util');

const creds = require('./client_secret.json');

async function accessSpreadsheet(){
  const doc = new GoogleSpreadsheet('1klBj2ZSk-IaDAT-w8kUKzmLw-o5oAk9otZL6xh1vnks');
  await promisify(doc.useServiceAccountAuth)(creds);
  const info = await promisify(doc.getInfo)();

  const sheet = info.worksheets[0];
  console.log(`Title: ${sheet.title}`);
}

module.exports.accessSpreadsheet = accessSpreadsheet;
discord.js node.js google-sheets
1 Answers
  • You want to retrieve the title of the 1st tab in the Spreadsheet using a module of google-spreadsheet.
  • client_secret.json is the credential file of the service account.
  • You have already been able to use Sheets API.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

If you are using the latest version of google-spreadsheet (in the current stage, it's google-spreadsheet@3.0.10.), how about the following modification?

Modified script:

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

const creds = require('./client_secret.json');

async function accessSpreadsheet() {
  const doc = new GoogleSpreadsheet('1klBj2ZSk-IaDAT-w8kUKzmLw-o5oAk9otZL6xh1vnks');
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  const sheet = doc.sheetsByIndex[0];
  console.log(`Title: ${sheet.title}`);
}

module.exports.accessSpreadsheet = accessSpreadsheet;

Note:

  • When client_secret.json is not for the service account, an error of No key or keyFile set occurs. Please be careful this.
  • When an error of The caller does not have permission occurs, please share the Spreadsheet with the email of the service account.

Reference:

user_71086530
Related