How to write to Google Sheets asynchronously using NodeJS?

To create your NodeJS application connecting and writing to Google Sheets, you need to generate your authentication credentials.

1. Go to Google Developer Console.
2. Select or create a project.
3. Navigate to the API libraries page here.
4. Search for “Google Sheets API”, then click the “Enable” button to enable the Google Sheets API.
5. Next, you need to generate OAuth client ID to use the API in code.
6. Get one in your project’s credential page by clicking here. Go to the page -> Click “Create Credentials” -> “OAuth Client ID”
7. It will open the “Create OAuth client ID” page, then choose the Other as we’re using this key in the node.js application.
8. Finally, click on create. Now, your client ID and client secret will appear on a popup. Dismiss it.
9. You should see a download button by now, click on it to download the credentials.
10. Now, there will be a JSON file downloaded with credentials. Move the file to your project folder.

Install the required node packages by using the following command,

npm install google-auth-library –save 
npm install googleapis –save

Here’s an example of Google authentication I’m using in one of my projects,

let fs = require('fs');
let readline = require('readline');
let { OAuth2Client } = require('google-auth-library');

let SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; //you can add more scopes according to your permission need. But in case you chang the scope, make sure you deleted the ~/.credentials/sheets.googleapis.com-nodejs-quickstart.json file
const TOKEN_DIR = __dirname + '/config/'; //the directory where we're going to save the token
const TOKEN_PATH = TOKEN_DIR + 'token.json'; //the file which will contain the token

class Authentication {
  authenticate(){
    return new Promise((resolve, reject)=>{
      let credentials = this.getClientSecret();
      let authorizePromise = this.authorize(credentials);
      authorizePromise.then(resolve, reject);
    });
  }
  getClientSecret(){
    return require(TOKEN_DIR + '/credentials.json');
  }
  authorize(credentials) {
    var clientSecret = credentials.installed.client_secret;
    var clientId = credentials.installed.client_id;
    var redirectUrl = credentials.installed.redirect_uris[0];
    var oauth2Client = new OAuth2Client(clientId, clientSecret, redirectUrl);

    return new Promise((resolve, reject)=>{
      // Check if we have previously stored a token.
      fs.readFile(TOKEN_PATH, (err, token) => {
        if (err) {
          this.getNewToken(oauth2Client).then((oauth2ClientNew)=>{
            resolve(oauth2ClientNew);
          }, (err)=>{
            reject(err);
          });
        } else {
          oauth2Client.credentials = JSON.parse(token);
          resolve(oauth2Client);
        }
      });
    });
  }
  getNewToken(oauth2Client) {
    return new Promise((resolve, reject)=>{
      var authUrl = oauth2Client.generateAuthUrl({
        access_type: 'offline',
        scope: SCOPES
      });
      console.log('Authorize this app by visiting this url: \n ', authUrl);
      var rl = readline.createInterface({
        input: process.stdin,
        output: process.stdout
      });
      rl.question('\n\nEnter the code from that page here: ', (code) => {
        rl.close();
        oauth2Client.getToken(code, (err, token) => {
          if (err) {
            console.log('Error while trying to retrieve access token', err);
            reject();
          }
          oauth2Client.credentials = token;
          this.storeToken(token);
          resolve(oauth2Client);
        });
      });
    });
  }
  storeToken(token) {
    try {
      fs.mkdirSync(TOKEN_DIR);
    } catch (err) {
      if (err.code != 'EEXIST') {
        throw err;
      }
    }
    fs.writeFile(TOKEN_PATH, JSON.stringify(token));
    console.log('Token stored to ' + TOKEN_PATH);
  }
}

module.exports = new Authentication();

Once the token is created, it will not ask again.

Update Google Sheet Asynchronously:

We need to authenticate every time before making a write operation to Google Sheet. So whenever a write operation is performed into a Google sheet, we need to go through an authentication step, so I’ve added an intermediate function doOperation().

const { google } = require("googleapis")
const authentication = require("./authentication");

class Sheets {
    async authenticate() {
        const auth = await authentication.authenticate();
        this.auth = auth;
    }

    async doOperation(spreadsheetId, range, data, operation) {
        await this.authenticate();
        if(operation === 'write') {
            await this._writeToGoogleSheet(spreadsheetId, range, data);
        }
    } 

    async _writeToGoogleSheet(spreadsheetId, range, data) {
        var sheets = google.sheets('v4');
        await sheets.spreadsheets.values.update({
            auth: this.auth,
            spreadsheetId: spreadsheetId,
            range: range, //Change Sheet1 if your worksheet's name is something else
            valueInputOption: "USER_ENTERED",
            resource: {
                values: data
            }
        }, (err, response) => {
            if (err) {
            console.log('The API returned an error: ' + err);
            return;
            } else {
                console.log("Data updated to the sheet successfully!");
            }
        });
    } 
}

module.exports = Sheets;

Here’s how to call it from an external node file,

const Sheets = require('sheets');

const dataToGoogleSheet = async function () {
  try {
    let spreadSheetId = '2ikWvEeWG7Pjbv_YJi5AMkjl14dFHMFcBfMfkEG65-p8'; //Google Sheet Id
    let range = 'Sheet1!A3:O'; //Sheet Name

    var activities = [
        ['Work', 9],
        ['Eat', 2],
        ['Commute', 2],
        ['Play Game', 2],
        ['Sleep', 7]
    ];

    let sheets = await new Sheets();
    sheets.doOperation(spreadSheetId, range, activities, 'write');
  } catch (error) {
    console.error(error);
  }
}

By now, you should be able to write the data to the sheet. Let me know what you think about this approach in the comments.