Top Tips for Getting Started with HubSpot and Google Sheets

An integration between HubSpot and Google Sheets is a powerful way to securely supercharge your HubSpot reporting and share data with users outside of HubSpot.

Using Integration Fox to connect HubSpot and Google Sheets, you can export any HubSpot property in real time to a Google Sheet in 3 easy steps:

1. Select your Google Sheet
2. Select the Object to Sync
3. Select the properties to sync

Once set up, you can use this data in other Google Sheets to do advanced data manipulation and to create complex reports where HubSpot reporting doesn't quite provide what you want out of the box.

Top Tips To Get Started

Here are some helpful tips to help kick things off:

1) Create a Master Spreadsheet for Syncing

You pay for each Object you sync in Integration Fox - so having a central Spreadsheet with all synced data can really keep your costs down, but also you don’t want to have data all over the place in Google Drive.

We recommend that you create a single clearly named spreadsheet with a Sheet per Synced object. You may have sensitive data in this spreadsheet, so ensure that you review your permissions and limit who this spreadsheet is shared with.

2) Selecting Properties to Export - Keep Them Secure

One of the other benefits of using this integration is that you can limit Export permissions on your HubSpot users. Allowing exporting from your platforms is a major security concern - with this integration, you can export only the data that is required for your reporting and sharing and restrict carte blanche access to export your sensitive data.

When selecting data to export, keep security in mind, try to only sync the critical data and avoid where possible sensitive data that could be stolen or used for unsavory purposes. i.e. do you really need to export individual emails, phone numbers, etc?

3) Manipulating Data in Google Sheets

The Google Sheet Tab that you sync the data to will overwrite every time data updates, therefore if you want to manipulate the data, e.g. use Google Sheet Functions to do advanced calculations or look-ups, you will want to take a copy of the data to another tab or spreadsheet and add these function here.

Hint: Use the IMPORTRANGE function to import your synced data to another sheet - IMPORTANT NOTE you will need to hover over the cell and Allow Access once you enter this function:

IMPORTRANGE(“spreadsheet_url”, “range_string”)

For example:

IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Hh2grfB6rp9OQ2yAIu3S5YF_CCFJGwyqPGveABlOZKg/edit", "HubSpot Contacts!A:N")

This will import the data into your new sheet in real time.

4) Useful Functions

Google Sheets has close to 500 functions that you can use to manipulate data, however, these are some of our favorites when it comes to manipulating data for reporting:

Date

• EOMONTH: Returns a date representing the last day of a month which falls a specified number of months before or after another date.
• NETWORKDAYS: Returns the number of net working days between two provided days.
• WORKDAY: Calculates the end date after a specified number of working days.

Filter

• FILTER: Returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
• SORT: Sorts the rows of a given array or range by the values in one or more columns.
• UNIQUE: Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.

Google

• GOOGLEFINANCE: Fetches current or historical securities information from Google Finance.

Logical

• IFS: Evaluates multiple conditions and returns a value that corresponds to the first true condition.
• IFERROR: Good for removing errors. Returns the first argument if it is not an error value, otherwise returns the second argument if present or a blank if the second argument is absent.
• SWITCH: Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.

Lookup

• INDEX: Returns the content of a cell specified by row and column offset.
• INDIRECT: Returns a cell reference specified by a string.
• MATCH: Returns the relative position of an item in a range that matches a specified value.

Maths

• COUNTIFS: Returns the count of a range depending on multiple criteria.
• PRODUCT: Returns the result of multiplying a series of numbers together.
• SUMIFS: Returns the sum of a range depending on multiple criteria.
• COUNTUNIQUE: Counts the number of unique values in a list of specified values and ranges.

Text

• CONCATENATE: Appends strings to one another to create a statement.
• JOIN: Concatenates the elements of one or more one-dimensional arrays using a specified delimiter.
• REPLACE: Replaces part of a text string with a different text string.
• SPLIT: Divides text around a specified character or string and puts each fragment into a separate cell in the row.
• SUBSTITUTE: Replaces existing text with new text in a string.

Array

• SUMPRODUCT: Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges.
• TRANSPOSE: Transposes the rows and columns of an array or range of cells.

Web

• IMPORTRANGE: Imports a range of cells from a specified spreadsheet.

These top tips are just the 'tip' of the iceberg, so if you're interested in learning more, or if you need some assistance with reporting using this tool, feel free to contact us.

Do you have any tips that you've found especially helpful? Leave a comment below.