hubspot.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                                                  #
 * # Author: Alexis Bedoret                                                  #
 * # Modified by: Vitaliy Galushka                                           #
 * # Detail of the turorial: https://goo.gl/64hQZb                           #
 * ###########################################################################
 */

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

/**
 * Fill in the following variables
 */
var documentProperties = PropertiesService.getDocumentProperties();
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
 * "Stages"
 * "Deals"
 */
var sheetNameStages = "hs_stages";
var sheetNameDeals = "hs_deals";
var sheetNameContacts = "hs_contacts";
var sheetNameLogSources = "Log: Sources";
var sheetNameLogStages = "Log: Stages";

/**
 * ###########################################################################
 * # ----------------------------------------------------------------------- #
 * # --------------------------- Menu items -------------------------------- #
 * # ----------------------------------------------------------------------- #
 * ###########################################################################
 */



function onOpen() {
var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('M1 HubSpot Connector')
      .addItem('Set Client ID', 'setHSClientID')
      .addItem('Set Client Secret', 'setHSClientSecret')
      .addItem('Authenticate with HubSpot', 'authorizeInHubspot')
      .addSeparator()
      .addItem('Get Data', 'refresh')
      .addSeparator()
      .addItem('Reset Credentials', 'reset')
      .addToUi();
}


function setHSDevParams() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('You clicked the first menu item!');
}

function authorizeInHubspot() {
 var url =  getOAuth2Access();
  
  if (url.length > 0) {
      SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .alert('Please go to this URL to Authenticate with HubSpot:' + url);
  } else {
      SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .alert('Everithing is ok!');
  }
}


function setHSClientID() {
  var ui = SpreadsheetApp.getUi(); // Same variations.
 
  
  var result = ui.prompt(
      'HubSpot Client ID',
      'Please enter your HubSpot Client ID:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    documentProperties.setProperty("HS_CLIENT_ID", text);
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.

  }

}

function setHSClientSecret() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'HubSpot Client Secret',
      'Please enter your HubSpot Client Secret:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    documentProperties.setProperty("HS_CLIENT_SECRET", text);
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.

  }

}


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

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

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  
  documentProperties.deleteProperty("HS_CLIENT_ID");
  documentProperties.deleteProperty("HS_CLIENT_SECRET");
  
  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(documentProperties.getProperty("HS_CLIENT_ID"))
      .setClientSecret(documentProperties.getProperty("HS_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 ------------------------------ #
 * # ----------------------------------------------------------------------- #
 * ###########################################################################
 */

/**
 * Get the different stages in your Hubspot pipeline
 * API & Documentation URL: https://developers.hubspot.com/docs/methods/deal-pipelines/get-deal-pipeline
 */
function getPipelines() {
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
  
  // API request
  var url = API_URL + "/crm-pipelines/v1/pipelines/deals"; // :pipelineId: = default 
  var response = UrlFetchApp.fetch(url, headers);
  var result = JSON.parse(response.getContentText());
  
    
  //   Logger.log(result.results);
  
  
  // Let's sort the stages by displayOrder
  result.results.sort(function(a,b) {
    return a.displayOrder-b.displayOrder;
  });
  
  // Let's put all the used stages (id & label) in an array
  var pipelines = Array();
  result.results.forEach(function(pipeline) {
     Logger.log(pipeline);
    die();
    pipelines.push([pipeline.pipelineId,pipeline.label]);  
  });
  
  Logger.log("PipeLines here: --------------");
  
     Logger.log(pipelines);
  return pipelines;
}


/**
 * Get the different stages in your Hubspot pipeline
 * API & Documentation URL: https://developers.hubspot.com/docs/methods/deal-pipelines/get-deal-pipeline
 */
function getStages() {
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
  
  // API request
  var url = API_URL + "/deals/v1/pipelines/default"; // :pipelineId: = default 
  var response = UrlFetchApp.fetch(url, headers);
  var result = JSON.parse(response.getContentText());
  
  // Let's sort the stages by displayOrder
  result.stages.sort(function(a,b) {
    return a.displayOrder-b.displayOrder;
  });
  
  // Let's put all the used stages (id & label) in an array
  var stages = Array();
  result.stages.forEach(function(stage) {
    stages.push([stage.stageId,stage.label]);  
  });
  
  return stages;
}




/**
 * Get the contacts from your Hubspot
 * API & Documentation URL: https://developers.hubspot.com/docs/methods/deals/get-all-deals
 */
function getContacts() {
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
  
  // 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 contacts = Array();

  while(keep_going)
  {
    // We'll take three properties from the deals: the source, the stage & the amount of the deal
    var url = API_URL + "/contacts/v1/lists/all/contacts/all?propertyMode=value_and_history&property=hs_lead_status&includeAssociations=true&property=createdate&property=vid&property=hs_content_membership_notes&property=num_contacted_notes&property=num_notes&property=email&property=lifecyclestage&property=c2c_first_page_seen&property=c2c_referrer&property=associatedcompanyid&property=hubspot_owner_id&property=contact_type&property=hs_lifecyclestage_lead_date&property=hs_lifecyclestage_marketingqualifiedlead_date&property=hs_lifecyclestage_salesqualifiedlead_date&property=hs_lifecyclestage_opportunity_date&property=hs_lifecyclestage_customer_date&property=channel&property=source&property=organic_referral_url&property=utm_campaign&property=utm_medium&property=utm_source&property=country&property=hs_analytics_source&property=hs_analytics_first_url&property=hs_analytics_first_referrer&property=hs_analytics_num_page_views&count=100&vidOffset="+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["has-more"];
    offset = result["vid-offset"];

       Logger.log("-----------------------getting contacts --------------------------------- ");
    
    // For each deal, we take the stageId, source & amount
    result.contacts.forEach(function(contact) {

      
      var createdate = (contact.properties.hasOwnProperty("createdate")) ? new Date(Number(contact.properties.createdate.value)).toLocaleDateString() : "";
      var company = (contact.properties.hasOwnProperty("associatedcompanyid")) ? contact.properties.associatedcompanyid.value : "";
      var email = (contact.properties.hasOwnProperty("email")) ? contact.properties.email.value : "";
      var lifecyclestage = (contact.properties.hasOwnProperty("lifecyclestage")) ? contact.properties.lifecyclestage.value : "";
      var c2c_first_page_seen = (contact.properties.hasOwnProperty("c2c_first_page_seen")) ? contact.properties.c2c_first_page_seen.value : "";
      var c2c_referrer = (contact.properties.hasOwnProperty("c2c_referrer")) ? contact.properties.c2c_referrer.value : "";
      var hubspot_owner_id = (contact.properties.hasOwnProperty("hubspot_owner_id")) ? contact.properties.hubspot_owner_id.value : "";
      var contact_type = (contact.properties.hasOwnProperty("contact_type")) ? contact.properties.contact_type.value : "";
      var hs_lifecyclestage_lead_date = (contact.properties.hasOwnProperty("hs_lifecyclestage_lead_date")) ? new Date(Number(contact.properties.hs_lifecyclestage_lead_date.value)).toLocaleDateString() : "";
      var hs_lifecyclestage_marketingqualifiedlead_date = (contact.properties.hasOwnProperty("hs_lifecyclestage_marketingqualifiedlead_date")) ? new Date(Number(contact.properties.hs_lifecyclestage_marketingqualifiedlead_date.value)).toLocaleDateString() : "";
      var hs_lifecyclestage_salesqualifiedlead_date = (contact.properties.hasOwnProperty("hs_lifecyclestage_salesqualifiedlead_date")) ? new Date(Number(contact.properties.hs_lifecyclestage_salesqualifiedlead_date.value)).toLocaleDateString() : "";
      var hs_lifecyclestage_opportunity_date = (contact.properties.hasOwnProperty("hs_lifecyclestage_opportunity_date")) ? new Date(Number(contact.properties.hs_lifecyclestage_opportunity_date.value)).toLocaleDateString() : "";
      var hs_lifecyclestage_customer_date = (contact.properties.hasOwnProperty("hs_lifecyclestage_customer_date")) ? new Date(Number(contact.properties.hs_lifecyclestage_customer_date.value)).toLocaleDateString() : "";
      var channel = (contact.properties.hasOwnProperty("channel")) ? contact.properties.channel.value : "";
      var source = (contact.properties.hasOwnProperty("source")) ? contact.properties.source.value : "";
      var organic_referral_url = (contact.properties.hasOwnProperty("organic_referral_url")) ? contact.properties.organic_referral_url.value : "";
      var utm_campaign = (contact.properties.hasOwnProperty("utm_campaign")) ? contact.properties.utm_campaign.value : "";
      var utm_medium = (contact.properties.hasOwnProperty("utm_medium")) ? contact.properties.utm_medium.value : "";
      var utm_source = (contact.properties.hasOwnProperty("utm_source")) ? contact.properties.utm_source.value : "";
      var country = (contact.properties.hasOwnProperty("country")) ? contact.properties.country.value : "";
      var hs_analytics_source = (contact.properties.hasOwnProperty("hs_analytics_source")) ? contact.properties.hs_analytics_source.value : "";
      var hs_analytics_first_referrer = (contact.properties.hasOwnProperty("hs_analytics_first_referrer")) ? contact.properties.hs_analytics_first_referrer.value : "";
      var hs_analytics_first_url = (contact.properties.hasOwnProperty("hs_analytics_first_url")) ? contact.properties.hs_analytics_first_url.value : "";
      var hs_analytics_num_page_views = (contact.properties.hasOwnProperty("hs_analytics_num_page_views")) ? contact.properties.hs_analytics_num_page_views.value : "";
      var vid = (contact.hasOwnProperty("vid")) ? contact.vid : "";
      
      var hs_content_membership_notes = (contact.properties.hasOwnProperty("hs_content_membership_notes")) ? contact.properties.hs_content_membership_notes.value : "";
      var num_contacted_notes = (contact.properties.hasOwnProperty("num_contacted_notes")) ? contact.properties.num_contacted_notes.value : "";
      var num_notes = (contact.properties.hasOwnProperty("num_notes")) ? contact.properties.num_notes.value : "";
      var hs_lead_status = (contact.properties.hasOwnProperty("hs_lead_status")) ? contact.properties.hs_lead_status.value : "";
     
      var hs_lead_status_become_in_progress = '';
      if (contact.properties.hasOwnProperty("hs_lead_status") && contact.properties.hs_lead_status.hasOwnProperty("versions")) {
        for (var i in contact.properties.hs_lead_status.versions) {
          if (contact.properties.hs_lead_status.versions[i].value == "IN_PROGRESS") {
            hs_lead_status_become_in_progress = new Date(Number(contact.properties.hs_lead_status.versions[i].timestamp)).toLocaleDateString();
            break;
          }
        }

      }
      
      
      
      contacts.push([createdate,email,lifecyclestage,c2c_first_page_seen,c2c_referrer,company,hubspot_owner_id,contact_type,hs_lifecyclestage_lead_date,hs_lifecyclestage_marketingqualifiedlead_date,hs_lifecyclestage_salesqualifiedlead_date,hs_lifecyclestage_opportunity_date,hs_lifecyclestage_customer_date,channel,source,organic_referral_url,utm_campaign,utm_medium,utm_source,country,hs_analytics_source, hs_analytics_first_referrer, hs_analytics_first_url, hs_analytics_num_page_views,vid,hs_content_membership_notes,num_contacted_notes,num_notes,hs_lead_status,hs_lead_status_become_in_progress]);

      
      
    });
    
  //  break;
  }

  return contacts;
}




/**
 * Get the deals from your Hubspot pipeline
 * API & Documentation URL: https://developers.hubspot.com/docs/methods/deals/get-all-deals
 */
function getDeals() {
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
  
  // 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 deals = Array();

  
   Logger.log("-----------------------getting deals --------------------------------- ");
  
  while(keep_going)
  {
    // We'll take three properties from the deals: the source, the stage & the amount of the deal
    var url = API_URL + "/deals/v1/deal/paged?dealname&properties=order_id&properties=source_platform&properties=support_manager&properties=paypro_customer_email&properties=target_platform&properties=product&properties=source&properties=design&property=hs_object_id&includeAssociations=true&properties=migration_link&properties=link&properties=paypro_invoice_link&properties=paypro_next_charge_date&properties=paypro_order_id&properties=paypro_order_name&properties=paypro_payment_method&properties=paypro_product_id&properties=paypro_subscription_id&properties=paypro_vendor_amount&properties=amount&properties=pipeline&properties=dealname&properties=hubspot_owner_id&properties=dealstage&properties=createdate&properties=closedate&properties=num_notes&properties=num_contacted_notes&properties=recurring_deal_status&properties=recurring_deal_type&properties=agreement&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;
    
    // For each deal, we take the stageId, source & amount
    result.deals.forEach(function(deal) {
      var stageId = (deal.properties.hasOwnProperty("dealstage")) ? deal.properties.dealstage.value : "";
     
      var amount = (deal.properties.hasOwnProperty("amount")) ? deal.properties.amount.value : 0;
      var dealname = (deal.properties.hasOwnProperty("dealname")) ? deal.properties.dealname.value : "";
     
      var hubspot_owner_id = (deal.properties.hasOwnProperty("hubspot_owner_id")) ? deal.properties.hubspot_owner_id.value : "";
      var dealstage = (deal.properties.hasOwnProperty("dealstage")) ? deal.properties.dealstage.value : "";
      var createdate = (deal.properties.hasOwnProperty("createdate")) ? new Date(Number(deal.properties.createdate.value)).toISOString().substring(0, 10) : "";
      var closedate = (deal.properties.hasOwnProperty("closedate")) ? new Date(Number(deal.properties.closedate.value)).toISOString().substring(0, 10) : "";
      var num_notes = (deal.properties.hasOwnProperty("num_notes")) ? deal.properties.num_notes.value : "";
      var num_contacted_notes = (deal.properties.hasOwnProperty("num_contacted_notes")) ? deal.properties.num_contacted_notes.value : "";
      var link = (deal.properties.hasOwnProperty("link")) ? deal.properties.link.value : "";
      
      // Payment information
      var associatedCompanyIds = (deal.associations.hasOwnProperty("associatedCompanyIds")) ? deal.associations.associatedCompanyIds : "";
      var associatedVids = (deal.associations.hasOwnProperty("associatedVids")) ? deal.associations.associatedVids : "";
      var migration_link = (deal.properties.hasOwnProperty("migration_link")) ? deal.properties.migration_link.value : "";
     
      var paypro_order_id = (deal.properties.hasOwnProperty("order_id")) ? deal.properties.order_id.value : "";
      var paypro_order_name = (deal.properties.hasOwnProperty("paypro_order_name")) ? deal.properties.paypro_order_name.value : "";
      var design = (deal.properties.hasOwnProperty("design")) ? deal.properties.design.value : ""; 
     var source_platform = (deal.properties.hasOwnProperty("source_platform")) ? deal.properties.source_platform.value : "";
      var target_platform = (deal.properties.hasOwnProperty("target_platform")) ? deal.properties.target_platform.value : "";
      
      var product = (deal.properties.hasOwnProperty("product")) ? deal.properties.product.value : "";
      var support_manager = (deal.properties.hasOwnProperty("support_manager")) ? deal.properties.support_manager.value : ""; 
     
      
      var Agreement = (deal.properties.hasOwnProperty("agreement")) ? deal.properties.agreement.value : ""; 
      var dealId = (deal.hasOwnProperty("dealId")) ? deal.dealId : "";

      if (closedate=="") {
    return null;
} else {


      deals.push([
        closedate,
        paypro_order_name,
        associatedVids,
        associatedCompanyIds,
        paypro_order_id,
        hubspot_owner_id,
        parseFloat(amount),
        dealId,
        stageId,
        dealname,
        dealstage, 
        createdate, 
        num_notes, 
        num_contacted_notes, 
        link, 
        migration_link,
       design,
        source_platform,
        target_platform,
        
        product,
        support_manager,
         Agreement
        
     ])};
    });
  }
  
  return deals;
}



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

/**
 * Print the different stages in your pipeline to the spreadsheet
 */
function writeStages(stages) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetNameStages);
  
  // Let's put some headers and add the stages to our table
  var matrix = Array(["StageID","StageName"]);
  matrix = matrix.concat(stages);
  
  // Writing the table to the spreadsheet
  var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
  range.setValues(matrix);
}

/**
 * Print the different deals that are in your pipeline to the spreadsheet
 */
function writeDeals(deals) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetNameDeals);
  
  
    var sheet = ss.getSheetByName(sheetNameDeals);
  
  if (!sheet) {
      ss.insertSheet(sheetNameDeals);
    sheet = ss.getSheetByName(sheetNameDeals);
      
  }
  
  
  // Let's put some headers and add the deals to our table
  var matrix = Array(["closedate","paypro_order_name","associatedVids","associatedCompanyIds","paypro_order_id","hubspot_owner_id","amount","hs_object_id","stageId","dealname","dealstage","createdate","num_notes","num_contacted_notes","link","migration_link","design","source_platform","target_platform","product","support_manager","Agreement"]);
  matrix = matrix.concat(deals);
  
  // Writing the table to the spreadsheet
  var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
  range.setValues(matrix);
}

/**
 * Print the contacts 
 */
function writeContacts(deals) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  
  var sheet = ss.getSheetByName(sheetNameContacts);
  
  if (!sheet) {
      ss.insertSheet(sheetNameContacts);
    sheet = ss.getSheetByName(sheetNameContacts);
      
  }
  
  // Let's put some headers and add the deals to our table
  var matrix = Array(["createdate","email","lifecyclestage","c2c_first_page_seen","c2c_referrer","company","hubspot_owner_id","contact_type","hs_lifecyclestage_lead_date","hs_lifecyclestage_marketingqualifiedlead_date","hs_lifecyclestage_salesqualifiedlead_date","hs_lifecyclestage_opportunity_date","hs_lifecyclestage_customer_date","channel","source","organic_referral_url","utm_campaign","utm_medium","utm_source","country","hs_analytics_source","hs_analytics_first_referrer", "hs_analytics_first_url","hs_analytics_num_page_views","vid","hs_content_membership_notes","num_contacted_notes","num_notes","hs_lead_status","hs_lead_status_become_in_progress"]);
  matrix = matrix.concat(deals);
  
  // 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() {
  
  
  if (!documentProperties.getProperty("HS_CLIENT_ID")) {  
    setHSClientID();
  }

  
  if (!documentProperties.getProperty("HS_CLIENT_SECRET")) {  
    setHSClientSecret();
  }  
  
  var service = getService();

  if (service.hasAccess()) {
    Logger.log('Getting Stages.....');
   // var stages = getStages();
    //Logger.log(stages);
   // writeStages(stages);
    
    Logger.log('Getting Deals ');
    var deals = getDeals();
    writeDeals(deals);
    
    Logger.log('Getting Contacts ');
    var contacts = getContacts();
    writeContacts(contacts);
    
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
    
    SpreadsheetApp.getUi().alert('Open the following URL and re-run the script: ' + (authorizationUrl));
    
    
  }
  Logger.log('Done! ');
}

/**
 * This function will log the amount of leads per stage over time
 * and print it into the sheet "Log: Stages"
 * It should be called once a day with a Project Trigger
 */
function logStages() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Stages: Count");
  var getRange = sheet.getRange("B2:B12");
  var row = getRange.getValues();
  row.unshift(new Date);
  var matrix = [row];
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Log: Stages");
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
    
    // Writing at the end of the spreadsheet
  var setRange = sheet.getRange(lastRow+1,1,1,row.length);
  setRange.setValues(matrix);
}

/**
 * This function will log the amount of leads per source over time
 * and print it into the sheet "Log: Sources"
 * It should be called once a day with a Project Trigger
 */
function logSources() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sources: Count & Conversion Rates");
  var getRange = sheet.getRange("M3:M13");
  var row = getRange.getValues();
  row.unshift(new Date);
  var matrix = [row];
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Log: Sources");
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
    
    // Writing at the end of the spreadsheet
  var setRange = sheet.getRange(lastRow+1,1,1,row.length);
  setRange.setValues(matrix);
}