From Hubspot to Google Sheets with JavaScript - Part II

When is a spreadsheet more than a spreadsheet? When coding with Google Apps Script turns it into a dynamic dashboard. In my last blog post, I talked about how we came to the decision to use Google Sheets and Google Apps Scripts to build out our new sales lead tracker. This post will go more in depth on the actual code we used, what I wrote, and how you can use it. I’ve also provided a bare bones script on how to pull data from Hubspot. This should provide enough information to get started working with Google Sheets and Google Apps Script. The biggest challenge in all of this is getting your data, but I’ve included documentation to make the process as self-explanatory as possible.

More on the Code

Most of the code is written in plain JavaScript, though a few sections use Google Apps Script classes. The goal of the code is to pull data from Hubspot via an API call. The way I request data is by constructing a URL as a string in our Script. I add our API key as well as other options such as properties to the URL. How you format the URL determines what data you get back. In our case, how the URL should be formatted was explained in the Hubspot API documentation. From there, I clean up our data where it needs to be, then write it to our Google Sheet.

As I’ve already mentioned, getting the data is the hard part, at least when you are first starting off with Google Apps Script. There is an explanation in the Google Apps Script documentation, but it’s very general and skips certain steps, such as how to access data or parse the JSON. Most of the coding was a learning process, a lot of trial and error at first, but once you understand the basics (which is explained in our GitHub), building a dashboard becomes a simple exercise.

The code calls for data using a URL and the data is returned in JSON, which allows me to easily convert the data into JavaScript objects. From there I can access the properties of the objects. For example, I requested a batch of companies from Hubspot along with select properties. The companies were returned in JSON, which I then parsed into an array. Once I had all the data in an array, I looped through it and wrote our data to Google Sheets, which is as simple as getting the active spreadsheet and selecting which rows and columns you want to write to.

Something I found very helpful was to change the type of those properties. For example, Hubspot returns dates as millisecond timestamps. I like to loop through our Hubspot objects and convert these timestamps into JavaScript date objects. This makes it much simpler to manipulate data.

Now for the Good Stuff

Our provided code is very comprehensive and can be easily understood and adapted. It’s as simple as using our included functions and inserting your own API key to play with the features and gain a better understanding of how to create these scripts. In our provided code we deal with company objects. The concept is easily extended to other Hubspot object types such as contacts, deals, and so on. Occasionally you will need to do some data cleaning in Hubspot (or whatever data source you are using), but you should be well on your way to creating a robust dashboard with our code as a base.

Get started with our Google Sheets dashboard code.