Use HubSpot integrated with Google Sheets to host timeline reports on a HubSpot dashboard, to easily track and manage resourcing
HubSpot's reporting tools do not - currently! - have the functionality to build out timeline reports or gantt charts. To achieve this, follow the below steps to integrate your CRM with Google Sheets and produce a timeline report to embed on a HubSpot dashboard.
Step 1: Create a Google Sheet
In the desired space on your GDrive create a Google Sheet to be used for the resource report. Add a column to the first tab of the spreadsheet for every data point you'll need to build out a timeline report, which will be synced to the Google Sheet from HubSpot.
Step 2: Integrate HubSpot with the Google Sheet
Use the native integration with Google Sheets from within the workflows tool to add HubSpot data to the Google Sheet.
Instructions linked here: Add data to Google Sheets with workflows
- Create a workflow based off the object in your HubSpot portal where the resource data will be pulled from.
- Set the workflow trigger to the relevant criteria that aligns with your resourcing process.
For this example, I'm going to use the trigger that the deal is at 60% probability to close, by being in the 'Presentation Scheduled' deal stage, and ready to be considered in the resourcing report:
To ensure that any changes made to the deal are synced through to the timeline report, turn on re-enrolment in the workflow trigger and set it to re-enrol when changes are made to relevant properties. For this example, I'm going to set it to re-enrol whenever Deals in the pipeline 60%+ are modified, by using the 'Last modified date' property: - Sign in to the Google Sheets workflow extension and, add an action to the workflow to update data in the Google Sheet.
Use the HubSpot assigned record ID as the Lookup column header and turn on 'Create a new row if none match'
As this data will be used to create a timeline at a minimum it will need to contain:
- Start date
- End date
- Assignee
Plus any additional data you'd like to show on the timeline report.
As the date data will sync through to the Google Sheet as a Unix Timestamp, it will be required to create two columns to host the unformatted date date, as below.
❗️NOTE: Marketing, Sales, Service and/or Operations Hub Professional+ is required.
Step 3: Build a timeline report in the Google Sheet
- In the Google Sheet, create two columns for the date to be formulated from the Unix Timestamp synced over from HubSpot.
- Create a column to formulate the deal record URL, using the synced record ID. This will enable the end-user to easily navigate to the deal record should any amendments need to be made.
Use 'ArrayFormula' within the formula to ensure it is applied to each new row of data synced through from HubSpot. - Highlight all the columns of data to be included in the timeline report and then select 'Insert > Timeline'
Add the unformatted data columns to the end of the columns, so these can be excluded from the timeline dataset.
Navigate to the newly created timeline tab on your Google Sheet and customise the 'Settings' to align with your requirements:
Step 4: Embed timeline on a HubSpot dashboard
Copy and paste the timeline tab URL in of the Google Sheet - Not the the Google Sheet share link - into a HubSpot dashboard. Make sure everyone who will need to view the timeline report has access to the Google Sheet.
Instructions linked here: Embed external content on a dashboard
Once embedded, users will be able to view the timeline report from within HubSpot, clicking on the timeline cards to view further details and selecting the CRM record URL if any updates need to be made - such as assigning unassigned projects.