From Hubspot to Google Sheets with JavaScript

Sometimes the projects that seem the most simple can turn out to be the most involved. For instance, a request to create a dashboard to track sales leads. While it may have been less straightforward than just feeding data into some charts and graphs, the end result allowed our sales and marketing team to turn what had been a simple lead-tracking exercise into a tool to drive sales intelligence.

We use Hubspot at DOOR3, which generally does a good job of tracking sales and leads, but we wanted a dynamic way to actively show all the latest data in one place. We decided it was time to build a dashboard that could pull everything together for the team.

Finding the Right Solution

I started by exploring available out-of-the-box options. I experimented with a few platforms, such as Databox and Power BI, but ultimately found they could not do what we wanted. Existing platforms do provide their own tools for pulling data. However, we have seen that our team occasionally wants a feature that is very specific to our organization, and this meant having full control would be best. In particular, it offered us the ability to integrate other data sources in the future as well.

In the end, after all my research, I decided to go with a Google Sheets implementation using Google Apps Script. “You settled on a simple spreadsheet?” you say. Formatting in a spreadsheet actually has many benefits, including readability and chart creation. Google Apps Script is a rapid application development platform, which allowed me to write code in JavaScript and have access to built-in libraries for Google Workspace applications, giving us control over every aspect of our Google spreadsheet. It helped that our implementation could be programmed in a language I was already familiar with, and offered the ability to create the robust customization options and data control we were looking for with low overhead and at speed. Everything lined up perfectly.

Despite this, I did still run into some challenges in building the dashboard. The main issue was the learning curve: There was not much information online on how to do something like this. I had to read a lot of documentation and compare my work to that of others on Stack Overflow and other similar forums. I started with trial and error, making small queries requesting data from Hubspot from within my scripts. Hubspot returns data in JSON, which allows us to work with Hubspot data as Javascript Objects. Once the queries were set up, it was easy to run calculations and cater the dashboard to our needs. You simply sort, format and display the data how you wish. I added design elements such as charts, colors, etc. once the team was happy with the preliminary work.

A Powerful Platform

What we ended up with was a robust dashboard that allows users to click on a deal or company and go directly to that profile within Hubspot, where they can make changes that are dynamically reflected in the dashboard. This makes sales and marketing meetings more streamlined and gives the team the ability to easily see what everyone is working on. These dashboards are making our sales projections smarter and easier to generate too. At the same time, we are really benefiting from how easy it is to communicate around our dashboards. Since everyone on the team knows how to use Google Sheets, they can easily tell me how they wish to see data or any design improvements they want.

Even better, the code we created is a bare bones example of how to pull and manipulate data from Hubspot, and a great base for us to build on. Quite frankly it is what I wished I had in the beginning. For more on how the code works, including a link to the code repository, be sure to stay tuned for my next post.