Google Sheets and JavaScript with Node.js



If you’d like to use the data in your Google Sheets projects from your Node applications, this is the guide for you.

We’ll use the google-spreadsheet module (https://www.npmjs.com/package/google-spreadsheet) by Theo Ephraim (https://twitter.com/theozero) to access an example spreadsheet inside of Google Sheets.

Original source

20 thoughts on “Google Sheets and JavaScript with Node.js

  • April 22, 2020 at 07:58
    Permalink

    Got a Google Sheet that you need to access from your code? Let us know if this guide helped you get it done!

  • April 22, 2020 at 07:58
    Permalink

    How would I protect/encrypt the client_secret.json if I wanted to upload my project to a public GitHub repo? Should I use something like env, gitignore, git-secret?

  • April 22, 2020 at 07:58
    Permalink

    hi, so for some reason, I tried using this for discord.js, or a node app if you're wondering, and I'm getting this error: TypeError: Cannot set property 'jwtClient' of undefined

  • April 22, 2020 at 07:58
    Permalink

    I got error of

    UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'jwtClient' of undefined

    at useServiceAccountAuth (C:Usersboopathi.tTrainnigsgoogle-sheets-nodenode_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:60:20)

    at internal/util.js:278:30

    at new Promise (<anonymous>)

    at useServiceAccountAuth (internal/util.js:277:12)

    at accessSpreadsheet (C:Usersboopathi.tTrainnigsgoogle-sheets-nodespreadsheet.js:8:47)

    at Object.<anonymous> (C:Usersboopathi.tTrainnigsgoogle-sheets-nodespreadsheet.js:14:1)

    at Module._compile (internal/modules/cjs/loader.js:959:30)

    at Object.Module._extensions..js (internal/modules/cjs/loader.js:995:10)

    at Module.load (internal/modules/cjs/loader.js:815:32)

    at Function.Module._load (internal/modules/cjs/loader.js:727:14)

  • April 22, 2020 at 07:58
    Permalink

    const GoogleSpreadsheet = require('google-spreadsheet');
    const doc = new GoogleSpreadsheet('id');
    oogleSpreadsheet is not a constructor

    at accessPreadsheets
    plzz help.

  • April 22, 2020 at 07:58
    Permalink

    Thank you for the amazing video. I understand this is a old video but when i'm working on it error pop outs and said googleSpreadsheet is not a constructor is there any way to solve this?

  • April 22, 2020 at 07:58
    Permalink

    Hello, I'm having a problem and that I cannot work around:
    UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'jwtClient' of undefined

    at useServiceAccountAuth (Z:GitHubdiscordBotnode_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:60:20)

    at internal/util.js:297:30

    at new Promise (<anonymous>)

    at useServiceAccountAuth (internal/util.js:296:12)

    at accessSpreadsheet (Z:GitHubdiscordBotsheetssheets.js:8:45)

    at Object.module.exports.run (Z:GitHubdiscordBotcomandosduvida.js:51:5)

    at Client.<anonymous> (Z:GitHubdiscordBotindex.js:63:21)

    at Client.emit (events.js:311:20)

    at MessageCreateHandler.handle (Z:GitHubdiscordBotnode_modulesdiscord.jssrcclientwebsocketpacketshandlersMessageCreate.js:9:34)

    at WebSocketPacketManager.handle (Z:GitHubdiscordBotnode_modulesdiscord.jssrcclientwebsocketpacketsWebSocketPacketManager.js:105:65)

    I'm trying to get to work on a Discord bot, any ideas to why this is happening?
    Thank you in advance!

  • April 22, 2020 at 07:58
    Permalink

    I got error of

    UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'jwtClient' of undefined

    at useServiceAccountAuth (C:Usersboopathi.tTrainnigsgoogle-sheets-nodenode_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:60:20)

    at internal/util.js:278:30

    at new Promise (<anonymous>)

    at useServiceAccountAuth (internal/util.js:277:12)

    at accessSpreadsheet (C:Usersboopathi.tTrainnigsgoogle-sheets-nodespreadsheet.js:8:47)

    at Object.<anonymous> (C:Usersboopathi.tTrainnigsgoogle-sheets-nodespreadsheet.js:14:1)

    at Module._compile (internal/modules/cjs/loader.js:959:30)

    at Object.Module._extensions..js (internal/modules/cjs/loader.js:995:10)

    at Module.load (internal/modules/cjs/loader.js:815:32)

    at Function.Module._load (internal/modules/cjs/loader.js:727:14)

    anyone have solution

  • April 22, 2020 at 07:58
    Permalink

    Hi. i got "Cannot set property 'jwtClient' of undefined" on the " await promisify(doc.useServiceAccountAuth)(creds);" line. Please help

  • April 22, 2020 at 07:58
    Permalink

    Hi, thanks for this video. Although I think something has changed in Google-Spreadsheet. I'm using the exact same code like you but getting an error back each time.

    This is the code:
    const { GoogleSpreadsheet } = require('google-spreadsheet');

    const { promisify } = require('util');

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

    async function accessSpreadsheet() {

    const doc = new GoogleSpreadsheet('xxxxxxxxxxxxxxxxxxxxxxx')

    await promisify(doc.useServiceAccountAuth)(creds)

    const info = await promisify(doc.getInfo)();

    const sheet = info.worksheets[0];

    console.log(`Title: ${sheet.title}, Rows: ${sheet.rowCount}`)

    }

    accessSpreadsheet();

    The error I get back over and over again is:
    (node:5132) UnhandledPromiseRejectionWarning: TypeError: Cannot set property 'jwtClient' of undefined

    at useServiceAccountAuth (C:UsersNilsDesktopnodenode_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:53:20)

    at internal/util.js:278:30

    at new Promise (<anonymous>)

    at useServiceAccountAuth (internal/util.js:277:12)

    at accessSpreadsheet (C:UsersNilsDesktopnodecryptopremiumsheet.js:8:47)

    at Object.<anonymous> (C:UsersNilsDesktopnodecryptopremiumsheet.js:14:1)

    at Module._compile (internal/modules/cjs/loader.js:956:30)

    at Object.Module._extensions..js (internal/modules/cjs/loader.js:973:10)

    at Module.load (internal/modules/cjs/loader.js:812:32)

    at Function.Module._load (internal/modules/cjs/loader.js:724:14)

    (node:5132) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)

    (node:5132) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

    Anyone has a solution?

  • April 22, 2020 at 07:58
    Permalink

    Hi I'm getting this error;
    TypeError: Cannot set property 'jwtClient' of
    undefined

    at useServiceAccountAuth (C:Users*******node_modulesgoogle-spreadsheetlibGoogleSpreadsheet.js:53:20)

  • April 22, 2020 at 07:58
    Permalink

    Hii. Thankyou, your video is amazing S2.

    I have a issue when i try to use the code below:
    `————————–
    async function accessSpreadsheet() {

    await promisify (doc.useServiceAccountAuth)(creds);

    const info = await promisify(doc.getInfo)();

    const sheet = info.worksheets[0]; ///busca as informações

    const data = await promisify(sheet.getRows)({
    query: `data = ${datainput}`
    });

    return data.length;

    }

    var total = accessSpreadsheet();

    console.log(total);
    `——————————
    But it return: Promise { <pending> }

    I need to use the value "data.lenght" in another piece of code. What i need to do to use this variable?

  • April 22, 2020 at 07:58
    Permalink

    Amazing. This helped me automate the data from a Google spreadsheet into a JSON file for our internal system to use. Thank you so much!

Leave a Reply