Automate Your Google Sheets Work

February 9, 2020 (4y ago)

Lets Begin

Many people and business's use Google Sheets to manage important data. Whether it be inventory count, expense history, or a list of clients, google sheets is a powerful tool which can be used to store this type of data and so much more!

Gone are the days of spending countless hours manually inserting your precious data. With Google's API you can automate certain tasks that would otherwise cost you time and money.

In this article I will teach you 3 things:

  1. How to Create a new sheet
  2. How to Write to a sheet
  3. How to Read from a sheet

Before we begin you need to enable the Google Sheets API. Go into your developer console, click "library" from the menu, and search for the Google Sheets API. Click on it and hit "enable". This will give you your "Client Configurations" which will give your script or application the ability to use the API with your google account. Download these configurations and save the "credentials.json" file to your working directory.

After this you will need to run the following NPM command to install the libraries:

'npm install googleapis@39 --save'

The first step before each script is generating authentication which is dependent on the Scope you are allowing your script to have. We will be giving our script the ability to create, write, and read from a sheet by setting our scope to 'https://www.googleapis.com/auth/spreadsheets'.

The authentication at the beginning of each script will look for a file called 'token.json' to authorize your script. If the file doesn't exhist it will prompt you in the console to authorize your scipt and create the file. Now it is time to begin!

Create A New Sheet

const fs = require('fs');
const { google } = require('googleapis');
const readline = require('readline-promise').default;

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'token.json';

//authorization
async function authorize(credentials) {
  const { client_secret, client_id, redirect_uris } = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
    client_id,
    client_secret,
    redirect_uris[0]
  );

  try {
    const token = fs.readFileSync(TOKEN_PATH);
    oAuth2Client.setCredentials(JSON.parse(token));
    return oAuth2Client;
  } catch (err) {
    const authorizedClient = await getNewToken(oAuth2Client);
    return authorizedClient;
  }
}

async function getNewToken(oAuth2Client) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });

  const code = await rl.questionAsync('Enter the code from that page here: ');
  rl.close();
  const { tokens } = await oAuth2Client.getToken(code);
  oAuth2Client.setCredentials(tokens);
  fs.writeFileSync(TOKEN_PATH, JSON.stringify(tokens));
  console.log('Token stored to', TOKEN_PATH);
  return oAuth2Client;
}

async function createSpreadsheet(sheets, title) {
  const resource = {
    properties: {
      title,
    },
  };
  const { data } = await sheets.spreadsheets.create({ resource });

  console.log(`Created new spreadsheet with ID: ${data.spreadsheetId}`);
  return data.spreadsheetId;
}

const main = async () => {
  const content = fs.readFileSync('credentials.json');
  const auth = await authorize(JSON.parse(content));
  const sheets = google.sheets({ version: 'v4', auth });
  const title = 'test';

  await createSpreadsheet(sheets, title);
};

main();

This will create a new spreadsheet and give you an ID to associate it with.

Write to A Sheet

Use the sheetId from the previous script for the script below.

const fs = require('fs');
const { google } = require('googleapis');
const readline = require('readline-promise').default;

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'token.json';

//authorization
async function authorize(credentials) {
  const { client_secret, client_id, redirect_uris } = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
    client_id,
    client_secret,
    redirect_uris[0]
  );

  try {
    const token = fs.readFileSync(TOKEN_PATH);
    oAuth2Client.setCredentials(JSON.parse(token));
    return oAuth2Client;
  } catch (err) {
    const authorizedClient = await getNewToken(oAuth2Client);
    return authorizedClient;
  }
}

async function getNewToken(oAuth2Client) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });

  const code = await rl.questionAsync('Enter the code from that page here: ');
  rl.close();
  const { tokens } = await oAuth2Client.getToken(code);
  oAuth2Client.setCredentials(tokens);
  fs.writeFileSync(TOKEN_PATH, JSON.stringify(tokens));
  console.log('Token stored to', TOKEN_PATH);
  return oAuth2Client;
}

async function writeToSheet(sheets, spreadsheetId) {
  const values = [['Name', 'Meals A Day', 'Body Fat Percentage']];
  const resource = {
    values,
  };
  const range = 'A1:C1';
  const valueInputOption = 'USER_ENTERED';

  const { data } = await sheets.spreadsheets.values.update({
    spreadsheetId,
    range,
    resource,
    valueInputOption,
  });
  console.log('Updated cells: ' + data.updatedCells);
}

const main = async () => {
  const content = fs.readFileSync('credentials.json');
  const auth = await authorize(JSON.parse(content));
  const sheets = google.sheets({ version: 'v4', auth });
  const sheetId = '1UXvLBLbKSUlyQV3A3m-r-lmBoxN2TEUzH6USfc6XF6g';

  await writeToSheet(sheets, sheetId);
};

main();

Read From A Sheet

const fs = require('fs');
const readline = require('readline-promise').default;
const { google } = require('googleapis');

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'token.json';

//authorization
async function authorize(credentials) {
  const { client_secret, client_id, redirect_uris } = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
    client_id,
    client_secret,
    redirect_uris[0]
  );

  try {
    const token = fs.readFileSync(TOKEN_PATH);
    oAuth2Client.setCredentials(JSON.parse(token));
    return oAuth2Client;
  } catch (err) {
    const authorizedClient = await getNewToken(oAuth2Client);
    return authorizedClient;
  }
}

async function getNewToken(oAuth2Client) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });

  const code = await rl.questionAsync('Enter the code form that page here: ');
  rl.close();
  const { tokens } = await oAuth2Client.getToken(code);
  oAuth2Client.setCredentials(tokens);
  fs.writeFileSync(TOKEN_PATH, JSON.stringify(tokens));
  console.log('Token stored to', TOKEN_PATH);
  return oAuth2Client;
}

async function readSheet(sheets) {
  const ranges = ['Sheet1!A2:B'];
  const { data } = await sheets.spreadsheets.values.batchGet({
    spreadsheetId: '1UXvLBLbKSUlyQV3A3m-r-lmBoxN2TEUzH6USfc6XF6g',
    ranges,
  });
  console.log(data.valueRanges[0].values);
}

const main = async () => {
  const content = fs.readFileSync('credentials.json');
  const auth = await authorize(JSON.parse(content));
  const sheets = google.sheets({ version: 'v4', auth });

  await readSheet(sheets);
};

main();

You should see the results displayed in your console from when you wrote to your spreadsheet.

And Thats It!

If you are interested in reading anything else I have written, head over to my blog to check it out!

I hope you found this article useful, now go create!