In last post, we saw how to setup hubot. In this post, we will make hubot to read from a google sheet and respond it to us.

Scenario

As mentioned, hubot is going to fetch our google sheet and read the contents to us. Imagine a scenario in your company where you have week-day oncalls per team whose name and phone number is listed on a google sheet. Our hubot should be able to fetch oncall for a particular team when a user requests. When implemented, it should look something ike this,

myhubot> who is oncall from Team1
myhubot> Derek K, 556556565

Google sheets format will look something like this

sheets

The sheet name (Weekday in our case) and the position of the fields are quite important in our case, since its hardcoded in our script.

Hope you have already created the credential token for accessing google sheets api, if not head over to Google API docs.

const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
const OAuth2Client = google.auth.OAuth2;
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
const TOKEN_PATH = 'SPECIFY_CREDENTIALS_FILE';

// Load client secrets from a local file.

function getSheet(teamname,robo) {
  // body...
  fs.readFile('SPECIFY_CLIENT_SECRET_FILE', (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    // Authorize a client with credentials, then call the Google Sheets API.
    authorize(JSON.parse(content), listOncalls, teamname, robo);
  });

}

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
function authorize(credentials, callback, teamname, robo) {
  const {client_secret, client_id, redirect_uris} = credentials.installed;
  const oAuth2Client = new OAuth2Client(client_id, client_secret, redirect_uris[0]);

  // Check if we have previously stored a token.
  fs.readFile(TOKEN_PATH, (err, token) => {
    if (err) return getNewToken(oAuth2Client, callback);
    oAuth2Client.setCredentials(JSON.parse(token));
    callback(oAuth2Client,teamname,robo);
  });
}

// figure out fields

function findFields(robo,data) {
  // body...
  var validFields = []

  for (var i =0; i< data.length; i++) {
     s = data[i]
     validFields.push(s[1])
     validFields.push(s[4])
     robo.reply(validFields)
     s = []
  }
}
/**
 * Compare strings
 *
 */
function compareNames(rows,teamname,robo) {

    var data = [];
    var fulldata = [];

    var myJsonString = JSON.stringify(rows);
    var parsed = JSON.parse(myJsonString)

    rows.map((row) => {
      if (`${row[0]}`.toLowerCase() === teamname) {
              data.push(row);
          }
    })
    if (data.length) {
      // robo.reply(data)
      findFields(robo,data)
    }
    else {
    	robo.repy('')
    }
}


function listOncalls(auth,teamname,robo) {
  const sheets = google.sheets({version: 'v4', auth});

  var sheetId = 'SPECIFY_SHEETS_ID';
  var rangeName = 'Weekday';
  
  sheets.spreadsheets.values.get({
    spreadsheetId: sheetId,
    range: rangeName,
  }, (err, {data}) => {
    if (err) return console.log('The API returned an error: ' + err);
    const rows = data.values;
    if (rows.length) {
      compareNames(rows,teamname,robo)
      
    } else {
      console.log('No data found.');
    }
  });
}

module.exports = function(robot) {
  robot.hear(/who is oncall from (.*)/i, function(msg){
    teamname = msg.match[1]
    getSheet(teamname,msg)
    
  });
}

logic

Simple explanation of the logic used. Whenever a user asks hubot who is oncall from teamname , we capture everything after who is oncall. We then download the sheets using sheets api and read it row by row. In each row, we check if the teamname that we got from user input match with the one in sheets. If it matches, we extract the fields which we want - name and phone number in our case.

  1. robot.hear is responsible for listening for the keyword who is oncall
  2. It then passes the teamname to the function getSheet
  3. getSheet does the authorization with google sheets api and get auth token.
  4. listOncalls function will then get the content in google sheets with the auth token.
  5. Sheets content is passed to compareNames function where it is checked for matching with teamname.
  6. Once we figure out the row containing teamname, the entire row is passed to findFields to extract fields
  7. Fields are then extracted, which are at fixed indexes 1,4.
  8. Hubot responds the result with robo.reply

This snippet to query oncall sheet was put together in a short amount of time and this is the first time I’m writing something in js. I wouldn’t reccommend this in production but if you are going to use it, you can put this in scripts folder in our hubot dir. Also if you are using it straight away, you would need to change couple of things here.

  • SPECIFY_CREDENTIALS_FILE - Replace with credentials file location.
  • SPECIFY_CLIENT_SECRET_FILE - Replace with client secrets file
  • SPECIFY_SHEETS_ID - Replace with Google Sheet ID

Happy Hacking!