Hubspot // Google Sheets: Engagement Data

getHubspotEngagements.js

/**
 * ###########################################################################
 * # Name: Hubspot Automation                                                #
 * # Description: This script let's you connect to Hubspot CRM and retrieve  #
 * #              its data to populate a Google Spreadsheet.                 #
 * # Date: March 11th, 2018 (Updated 8/28/2018)                              #
 * # Author: Alexis Bedoret (Adapted to Engagements by Philip Johnson)       #
 * # Source: https://gist.github.com/pjatx/2f5a4df0885332ff753519ddc69a141f  #
 * # Detail of the original turorial: https://goo.gl/64hQZb                  #
 * ###########################################################################
 */

/**
 * ###########################################################################
 * # ----------------------------------------------------------------------- #
 * # ------------------------------- CONFIG -------------------------------- #
 * # ----------------------------------------------------------------------- #
 * ###########################################################################
 */

/**
 * Fill in the following variables
 */
var CLIENT_ID = '';
var CLIENT_SECRET = '';
var SCOPE = 'contacts';
var AUTH_URL = "https://app.hubspot.com/oauth/authorize";
var TOKEN_URL = "https://api.hubapi.com/oauth/v1/token";
var API_URL = "https://api.hubapi.com";

/**
 * Create the following sheets in your spreadsheet
 * "Engagements"
 */
var sheetNameEngagements = "Engagements";

/**
 * ###########################################################################
 * # ----------------------------------------------------------------------- #
 * # --------------------------- AUTHENTICATION ---------------------------- #
 * # ----------------------------------------------------------------------- #
 * ###########################################################################
 */

/**
 * Authorizes and makes a request to get the deals from Hubspot.
 */
function  getOAuth2Access() {
  var service = getService();
  if (service.hasAccess()) {
    // ... do whatever ...
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
        authorizationUrl);
  }
}

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  getService().reset();
}

/**
 * Configures the service.
 */
function getService() {
  return OAuth2.createService('hubspot')
      // Set the endpoint URLs.
      .setTokenUrl(TOKEN_URL)
      .setAuthorizationBaseUrl(AUTH_URL)

      // Set the client ID and secret.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

      // Set the name of the callback function in the script referenced
      // above that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())
      .setScope(SCOPE);
}

/**
 * Handles the OAuth2 callback.
 */
function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied.');
  }
}

/**
 * Logs the redict URI to register.
 */
function logRedirectUri() {
  Logger.log(getService().getRedirectUri());
}

/**
 * ###########################################################################
 * # ----------------------------------------------------------------------- #
 * # ------------------------------- GET DATA ------------------------------ #
 * # ----------------------------------------------------------------------- #
 * ###########################################################################
 */

function getEngagements(){
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
  
  // Define how recent of engagements we want returned (if using the recent engagements endpoint).
  var since = "UNIX TIMESTAMP";
  
  // Prepare pagination
  // Hubspot lets you take max 250 deals per request. 
  // We need to make multiple request until we get all the deals.
  var keep_going = true;
  var offset = 0;
  var engagements = Array();

  while(keep_going)
  {
    // Recent Engagements Endpoint
    // var url = API_URL + "/engagements/v1/engagements/recent/modified?count=100&since=" + since + "&offset=" + offset;
    
    // All Engagements Endpoint
    var url = API_URL + "/engagements/v1/engagements/paged?limit=250&offset=" + offset;
    
    var response = UrlFetchApp.fetch(url, headers);
    var result = JSON.parse(response.getContentText());
    
    // Are there any more results, should we stop the pagination ?
    keep_going = result.hasMore;
    offset = result.offset;
    
    result.results.forEach(function(e) {
      var id = e.engagement.id;
      var active = e.engagement.active;
      var createdAt = e.engagement.createdAt;
      var lastUpdated = e.engagement.lastUpdated;
      var createdBy = e.engagement.createdBy;
      var type = e.engagement.type;
      var timestamp = e.engagement.timestamp;
      engagements.push([id, active, createdAt, lastUpdated, createdBy, type, timestamp]);
    });
  } 
  
  return engagements;
}


/**
* ###########################################################################
* # ----------------------------------------------------------------------- #
* # -------------------------- WRITE TO SPREADSHEET ----------------------- #
* # ----------------------------------------------------------------------- #
* ###########################################################################
*/

function writeEngagements(engagements) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetNameEngagements);
  
  // Let's put some headers and add the engagements to our table
  var matrix = Array(["ID"," Active"," CreatedAt"," LastUpdated"," CreatedBy"," Type"," Timestamp"]);
  matrix = matrix.concat(engagements);
  
  // Writing the table to the spreadsheet
  var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
  range.setValues(matrix);
}

/**
* ###########################################################################
* # ----------------------------------------------------------------------- #
* # -------------------------------- ROUTINE ------------------------------ #
* # ----------------------------------------------------------------------- #
* ###########################################################################
*/

/**
 * This function will update the spreadsheet. This function should be called
 * every hour or so with the Project Triggers.
 */
function refresh() {
  var service = getService();
  
  if (service.hasAccess()) {
    var engagements = getEngagements();
    writeEngagements(engagements);
    
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
        authorizationUrl);
  }
}