Reading Google Sheets with Node

Let’s learn how can we read the data from a google sheet in your drive

In this article we’ll look into how can we read data from google sheets. Why you ask?

Sometimes creating and maintaining a database is not just worth it. The data is small, it’s just easier to maintain a sheet (or in this case google sheet).

We will implement a simple spike to read data from google sheets with node server and display the data in react.

If you are just looking for the code. The code can be found here.

Reading google sheets data with Node

Our objective is to develop an application where user can login using their google account and access a sample sheet to which they have access.

Prerequisites

1. Google account
2. Node environment

I will use my boilerplate for node express with react which you can find here.

We’ll divide the whole thing into 2 sections

1. Google Setup
2. Node Code

Google Setup

We need to setup the google environment for the application. We need to let google know that we will be using the api’s in our application.

In return google will provide us secret keys using which we will allow user to login and read the data from a google sheet (to which user has access).

We follow the below steps to config the project

1. Create a project (or choose a project)
2. Enable google drive api’s for the project
3. Configure OAuth setup screen with name and icon of your application
4. Create new credentials for OAuth Client ID with application type Other
5. Save it and google shows you a dialogue with the secret keys
6. We need to download the credential json file

Refer to the step by step screenshots here.

Node Implementation

Now comes the coding part, we have received authorisation keys. Place the downloaded json file to server/google-credentials/credentials.json

We have created a file server/utils/google-utils.js

const { google } = require('googleapis');
const googleConfig = {
  clientId: '<<YOUR_CLIENT_ID>>',
  clientSecret: '<<YOUR_CLIENT_SECRET>>',
  redirect: 'http://localhost:3000/loggedin' // this must match your google api settings
};
/**
 * Create the google auth object which gives us access to talk to google's apis.
 */
const createConnection = () => {
  return new google.auth.OAuth2(
    googleConfig.clientId,
    googleConfig.clientSecret,
    googleConfig.redirect
  );
}
/**
 * This scope tells google what information we want to request.
 */
const defaultScope = [
  'https://www.googleapis.com/auth/plus.me',
  'https://www.googleapis.com/auth/userinfo.email',
  'https://www.googleapis.com/auth/spreadsheets'
];
/**
 * Get a url which will open the google sign-in page and request access to the scope provided (such as calendar events).
 */
const getConnectionUrl = (auth) => {
  return auth.generateAuthUrl({
    access_type: 'offline',
    prompt: 'consent', // access type and approval prompt will force a new refresh token to be made each time signs in
    scope: defaultScope
  });
}
/**
 * Create the google url to be sent to the client.
 */
exports.urlGoogle = () => {
  const auth = createConnection(); // this is from previous step
  const url = getConnectionUrl(auth);
  return url;
}

In the APIs server/controllers/api.js

const { logger } = require('./../utils/logger');
const fs = require('fs');
const path = require('path');
const readline = require('readline');
const { google } = require('googleapis');
const { urlGoogle } = require('./../utils/google-utils');
const TOKEN_PATH = path.join(__dirname, '..', 'google-credentials', 'token.json');
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
exports.index = (req, res) => {
  logger.info('api request received');
  console.log('req', req.body.token);
  const filePath = path.join(__dirname, '..', 'google-credentials', 'credentials.json');
  fs.readFile(filePath, (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
    const credentials = JSON.parse(content);
    console.log('credentials', credentials);
    const {client_secret, client_id, redirect_uris} = credentials.installed;
    const oAuth2Client = new google.auth.OAuth2(
        client_id, client_secret, 'http://localhost:3000/loggedin');
oAuth2Client.setCredentials(req.body.token);
    console.log('oAuth2Client', oAuth2Client);
    listMajors(oAuth2Client, res);
  });
};
exports.getGoogleLoginUrl = (req, res) => {
  console.log('getGoogleLoginUrl');
  res.send(JSON.stringify({ url: urlGoogle() }));
};
exports.getGoogleTokenAfterLogin = (req, res) => {
  const filePath = path.join(__dirname, '..', 'google-credentials', 'credentials.json');
  fs.readFile(filePath, (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
    const credentials = JSON.parse(content);
    const {client_secret, client_id, redirect_uris} = credentials.installed;
    const oAuth2Client = new google.auth.OAuth2(
        client_id, client_secret, 'http://localhost:3000/loggedin');
oAuth2Client.getToken(req.body.code, (err, token) => {
      if (err) return console.error('Error while trying to retrieve access token', err);
      res.send(JSON.stringify({ token: token }));
    });
  });
};
function getNewToken(oAuth2Client, code, callback) {
    oAuth2Client.getToken(code, (err, token) => {
      if (err) return console.error('Error while trying to retrieve access token', err);
      oAuth2Client.setCredentials(token);
      // Store the token to disk for later program executions
      callback(oAuth2Client);
    });
  // });
}
function listMajors(auth, apiRes) {
  const sheets = google.sheets({version: 'v4', auth});
  sheets.spreadsheets.values.get({
    spreadsheetId: '<<SPREAD_SHEET_ID>>',
    range: 'Class Data!A2:E',
  }, (err, res) => {
    if (err) return console.log('The API returned an error: ' + err);
    const rows = res.data.values;
    apiRes.send(JSON.stringify({ success: true, rows: rows }));
    if (rows.length) {
      console.log('Name, Major:');
      // Print columns A and E, which correspond to indices 0 and 4.
      rows.map((row) => {
        console.log(`${row[0]}, ${row[4]}`);
      });
    } else {
      console.log('No data found.');
    }
  });
}

With our code we

1. Allow user to login from the google account
2. Get Code and Scope from authentication (will be required to generate the Token for step 3)
3. Get Token after login (this token will be used for the rest of the session)
4. Use the token to get data from the google sheets

The code can be found here.

Running Program

If you run the code above

1. We land on the login screen with login link
2. Clicking on login link takes you to google login
3. Google shows you the information that will be shared
4. After login you are redirected to the home page with a button to load
5. Once you load it, you should be able to see json for the google sheet

Cheers!