Easily Build an Automated Hubspot Custom Dashboard Using Google Spreadsheet and Data Studio

A lot of resources are invested in bringing a company's brand, products, and services to market. But the more critical issue is, what will generate the most qualified leads? At what point do your leads convert or disappear? Ultimately, we want to invest in the channels that work best. The most likely way to get there is through profound data. 

In the past, we didn't always use metrics to make business decisions; we used our intuition, which is fine to a certain extent. There is, however, something surprising about what numbers can teach us about our businesses and often, our intuition is incorrect.

In this article, we will show you how to automate your data in an impressive way without relying on your technical team. Javascript knowledge is not required if you just copy & paste our code, but it might help you customize your dashboard - we'll use Google Spreadsheet and Google Data Studio to create an automated Hubspot custom dashboard from scratch.

Basic requirements:

1. Set up a Hubspot development account
2. Use a Google Spreadsheet to authenticate to Hubspot using its APIs
3. Utilize Hubspot's APIs to retrieve the deals
4. Use Google Spreadsheet to prepare the data according to our needs
5. Connect the Google Spreadsheet with Google Data Studio and create a report

Let's begin

1. Set up a Hubspot development account

The process of creating a Hubspot developer account is fairly straightforward.

As soon as our account is ready, we'll need to create an application to retrieve our Client ID and Client secret. Those will be used to connect our Hubspot CRM account with our Google Spreadsheet.

As shown below, make sure the right scopes are selected in your Hubspot app's settings:

2. Use a Google Spreadsheet to authenticate to Hubspot using its APIs

You can refer to this tutorial if you need more information, but we will go through each step together.

To begin, we need to create a new Google Spreadsheet in our Google Drive. Open the Spreadsheet and click Tools > Script editor, which will open a new tab.

For OAuth2, we'll need to add a library:

1. Navigate to Resources > Libraries...

2. In the Find a Library text box, enter the script ID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click the Select button. Check out this article if you want to add the code manually to the project. This is the simplest way to add the library to our project.

3. In the dropdown box, select a version (usually the most recent).

4. Save your changes.

Let's write some code step by step now. You might want to read the full code here if you are a techie with some JS expertise. As for the others, just follow the steps.

Authentication requires some variables to be defined first:

var CLIENT_ID = '…'; // Enter your Client ID
var CLIENT_SECRET = '…'; // Enter your 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';

To handle authentication, let's add these functions:

function getService() {
   return OAuth2.createService('hubspot')
      .setTokenUrl(TOKEN_URL)
      .setAuthorizationBaseUrl(AUTH_URL)
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)
      .setCallbackFunction('authCallback')
      .setPropertyStore(PropertiesService.getUserProperties())
      .setScope(SCOPE);
}
function authCallback(request) {
   var service = getService();
   var authorized = service.handleCallback(request);
   if (authorized) {
      return HtmlService.createHtmlOutput('Success!');
   } else {
      return HtmlService.createHtmlOutput('Denied.');
   }
}

To run the authentication, we need the following function:

function authenticate() {
   var service = getService();
   if (service.hasAccess()) {
      // … whatever needs to be done here …
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
   }
}

It's time to test the authentication part. As soon as you have filled in the Client ID and Client secret and run the authenticate function, you will need to:

1. Retrieve the Authorization URL that will is shown in the Logs (View > Logs).

2. Click on the URL and your Hubspot accounts will appear.

3. Select the Hubspot account you want to connect to your Spreadsheet

You're in, well done!

3. Utilize Hubspot's APIs to retrieve the deals

In order to populate our spreadsheet in the easiest way possible, let's just do the bare minimum to retrieve the data. Using Google Spreadsheet, we'll prepare the data according to our needs.

(If you're a developer, you may be tempted to use javascript to manipulate the data, but we suggest not reinventing the wheel and using Google Spreadsheet's built-in functions.)

Let's begin by getting your pipeline stages. The following function can be pasted underneath the existing code in your Script editor:

function getStages() {
  // Prepare authentication to Hubspot
  var service = getService();
  var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
  
  // API request
  var pipeline_id = "default"; // Enter your pipeline id here.
  var url = API_URL + "/crm-pipelines/v1/pipelines/deals";
  var response = UrlFetchApp.fetch(url, headers);
  var result = JSON.parse(response.getContentText());
  var stages = Array();
  
  // Looping through the different pipelines you might have in Hubspot
  result.results.forEach(function(item) {
    if (item.pipelineId == pipeline_id) {
      var result_stages = item.stages;
      // 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
      result_stages.forEach(function(stage) {
        stages.push([stage.stageId,stage.label]);  
      });
    }
  });
  
  return stages;
}

Using this code, you will be able to:

1. Connect to Hubspot.
2. Get the complete default pipeline configuration, including its stages.
3. The stages will be sorted accordingly.
4. Array the results so we can use them later.

Multiple pipelines can be configured in Hubspot CRM. It is recommended that you change the default pipeline id to any other pipeline id you may have created. For more information about Hubspot's CRM pipeline API, visit this page.

We can now retrieve all the deals flowing through our pipeline as well. The following code will take care of that:

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();
   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?properties=dealstage&properties=source&properties=amount&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 : "unknown";
         var source = (deal.properties.hasOwnProperty("source")) ? deal.properties.source.value : "unknown";
         var amount = (deal.properties.hasOwnProperty("amount")) ? deal.properties.amount.value : 0;
         deals.push([stageId,source,amount]);
      });
   }
   return deals;
}

This piece of code will:

1. Connect to Hubspot.
2. Add the following properties to all the deals: stage, source, and amount.
3. Array the results so we can use them later.

Here is more information about retrieving the deals.

Now that we have the data in arrays, we need to print the data into sheets if we want to use Google Spreadsheet to manipulate them. We will first create two sheets in our spreadsheet, Stages and Deals.

var sheetNameStages = "Stages";
var sheetNameDeals = "Deals";
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","Label"]);
   matrix = matrix.concat(stages);
   // Writing the table to the spreadsheet
   var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
   range.setValues(matrix);
}
function writeDeals(deals) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(sheetNameDeals);
   // Let’s put some headers and add the deals to our table
   var matrix = Array(["StageID","Source", "Amount"]);
   matrix = matrix.concat(deals);
   // Writing the table to the spreadsheet
   var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
   range.setValues(matrix);
}

In this piece of code, the stages and deals will be written into the appropriate sheets as follows:

1. Connect to the right sheet.
2. Create a table with the right columns.
3. Inputting the data into the table.
4. Data entry into the spreadsheet.

There you go! The code is mostly complete, we just need to create a simple routine that can be called recurrently by Project triggers:

function refresh() {
   var service = getService();
   if (service.hasAccess()) {
      var stages = getStages();
      writeStages(stages);
      var deals = getDeals();
      writeDeals(deals);
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
   }
}

This routine will:

1. Try to connect to Hubspot
2. In the event that it succeeds, it will print the data to the appropriate sheets
3. It will print the Authorization URL in the Logs (View > Logs) if it fails (probably the first time you run the script). You will need to copy & paste this into your browser.

To keep retrieving the most up-to-date data, we can tell our script to run the refresh() function every hour. Go to Edit > Current Project's Triggers.

Here is the code as well as the Google Spreadsheet for your convenience.

Let's test our script and see what happens. Don't forget to authenticate to Hubspot the first time you run the script. After the code runs without errors (which it should if we did our job properly), you can see the populated data in your Google Spreadsheet.

4. Use Google Spreadsheet to prepare the data according to our needs

Using the above script, we were able to retrieve data from Hubspot. We can now prepare the data in Google Spreadsheet before sending it to Google Data Studio. In this case, what information do we need?

- Do you have any bottlenecks in your pipeline? To put it another way: how many deals are currently in the pipeline at each stage?

- Do you have enough money in your pipeline to forecast cash flow? Also, what is the current amount of money in each stage?

- Is there a channel that works best and in which one should you invest more? Or, to put it another way: How much money is generated per source and at what conversion rate.

In order to make sense of all of those numbers and Hubspot's data, you just have to create a few pivot tables. We can create one by going to our Google Spreadsheet and clicking on Data > Pivot table.

In order to create our first Pivot table, we need to link our Stages sheet with our Deals sheet. While our Deals sheet contains our StageIDs, we'd rather have a table containing the labels of our Stages as well, otherwise our report will only display the IDs.

Put this line in the first cell of the D column on the Deals sheet:

=ARRAYFORMULA(IFERROR(VLOOKUP(A:A,Stages!A:B,2,FALSE),"Unknown"))

Let’s decompose this line:

1. VLOOKUP(A:A,Stages!A:B,2,FALSE): Please match the A column with the table in Stages and get me the value in the second column.

2. IFERROR: If you don’t find any match, just let me know as ‘Unknown’

3. ARRAYFORMULA: Apply this rule to all my column.

Now that we have all the data, let's create our first pivot table to see how many deals are in each stage.

Create a new Pivot table, which will generate a new sheet that can be renamed by Stages: Count. In the Pivot table editor, follow these steps:

1. Choose a data range. Your Deals sheet should have the entire data range.

2. Add a Row and choose StageName

3. You can add a value and choose StageID and Summarize by: COUNTA

The amounts of deals per stage are now displayed in a table. Our Google Data Studio report will use this table.

The amount of money in each stage can be retrieved in the same way. Now let's create a new Pivot table, but instead of StageID, let's choose Amount and set Summarize by: SUM.

Here are the parameters we need to create a Pivot table to get the conversion rates per source:

- Row: Source
- Column: StageName
- Value: StageID Summarize by COUNTA

The conversion rate can then be calculated in a column at the end of the pivot table. The stage where conversions happen in our case is Closed Won, but it will depend on yours. Therefore, we divide the Closed Won column by the Grand Total column to calculate conversion rate.

To determine the amount of money generated by source, we create a Pivot table with the following parameters:

- Row: Source
- Column: StageName
- Value: Amount Summarize by SUM

Each source's Closed Won column shows how much money it generated.

Now that we have all the datasets needed, let's go to Google Data Studio for the final step.

5. Connect the Google Spreadsheet with Google Data Studio and create a report

Using OAuth2, we retrieved data from Hubspot's API using the above steps. We then prepared the data using Google Spreadsheet. With Google Data Studio, we can now display that data in a nice custom dashboard.

It's pretty straightforward, so we won't go into too much detail, just the basics. The first thing we need to do is create a new report. Next, we'll need to connect a data source. It lets you choose between different data sources, including a Google Spreadsheet, which we'll need.

Additionally, you might want to check all the others, especially Google Analytics, Adwords, or any other tools you might already be using. Even community connectors exist (unfortunately, there is no Hubspot connector at this time).

Here is our first bar chart showing the conversion rate by source.

Adding a plot opens a menu where we can edit the data source. We need to add the data source from the Sources: Count & Conversion Rates sheet where we created the pivot table with conversion rates.

You can configure the different fields detected from the selected sheet when you add a Google Spreadsheet data source. When it detects numbers, it usually fills the Aggregation column with SUM. Most of the time, we toggle all those fields to None.

As soon as the connection has been made, we should add the right Dimensions and Metrics. The above graph can be generated by setting Source for Dimension and Conversion Rate as metrics.

We need to add a data source to Data Studio for each sheet or pivot table we created in Google Spreadsheet. As soon as all the data sources have been added, we can simply replicate the above steps to create all the graphs, scorecards, and other fancy tools you desire.

Could we have the data over time to see how our performance evolves day by day?

For example, we want to know how leads evolve over time, where our team is improving or lacking, what channels I should review since no leads have been sent since a few days or what the impact was when one sales person of our team was sick last week on the sales pipeline.

Data can be logged over time easily. To achieve this, you can add the following code to the script:

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);
}

It accomplishes the following:

1. An array is created by copying the right column in the right sheet.
2. A date is added to the array at the beginning.
3. The last row of the Log: Sources sheet is selected and pasted.

The Stages can be treated the same way. Finally, we can create project triggers to log data once a day.

In the same manner as explained above, we can then plug the new data into our Google Data Studio.

The final outcome

See the screenshot of our final custom dashboard below. We already have a pretty good overview of what's going on in our sales pipeline, and it's pretty straightforward. It will only take a couple of days to see how our sales team performs over time.

The following used documents are provided for your convenience:

- Complete Google App Script: click here
- Sample Google Spreadsheet: click here
- Sample Google Data Studio dashboard: click here

Datastudio and Hubspot CRM did not have a community connector at the time this tutorial was written. Creating it for others' benefit might be a good idea. However, using Google Spreadsheet as an intermediate step gives you more power and flexibility. If Hubspot is your data source and Data Studio is your dashboard, you might want to consider using your spreadsheet as a "brain" before sending the data to Data Studio.