Trying to connect Hubspot to Google Sheets

NLakmali — Mon, 07 Aug 2023 06:21:25 GMT

Hello,

My problem statement is simple:

I have a Hubspot account, where my sales team records all the details of client calls and other details collected on the call.
I wanted all this recorded information to a Google sheet. Once passed, I simply want to convert these into charts and graphs.
I also wanted this sheet to be updated whenever there is a new record is added or if an older record is updated.

I have currently created an app script using chatGPT and that request is timing out since there is a cut-off(??) for HubSpot to run an API call and the number of records is too many for it to handle.

I checked the error with a colleague, and this has happened since the API request has been timed out from the Hubspot end since it was taking/running for too long.

Please do let me know if there is something that I can do to fix this.

x// Replace 'YOUR_HUBSPOT_OAUTH_TOKEN' with your actual HubSpot OAuth token. const HUBSPOT_OAUTH_TOKEN = 'XXX-XXX-763a348b-20c2-4fae-bf9f-bdad8f507b6a'; // const CONTACTS_SHEET_NAME = 'Contacts'; const COMPANIES_SHEET_NAME = 'Companies'; // Add the additional columns you want to fetch from HubSpot const additionalColumns = [ 'communication_engine_service', 'friction', 'funding_stage', 'hubtags', 'industry_sub_segment', 'key_requirements_old', 'new_business_type', 'operating_type', 'post_meeting_notes', 'requirement_new', 'type_of_product' ]; function fetchContactsAndCompanies() { // const existingContacts = fetchExistingData(CONTACTS_SHEET_NAME); const existingCompanies = fetchExistingData(COMPANIES_SHEET_NAME); // fetchAndPopulateData(CONTACTS_SHEET_NAME, 'contacts', existingContacts); fetchAndPopulateData(COMPANIES_SHEET_NAME, 'companies', existingCompanies); } function fetchExistingData(sheetName) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); const lastRow = sheet.getLastRow(); if (lastRow === 0) return []; const lastColumn = sheet.getLastColumn(); const dataRange = sheet.getRange(2, 1, lastRow, lastColumn); const dataValues = dataRange.getValues(); return dataValues; } function fetchAndPopulateData(sheetName, dataType, existingData) { const apiUrl = `https://api.hubapi.com/crm/v3/objects/companies?properties=communication_engine_service&properties=friction&properties=funding_stage&properties=industry_sub_segment&properties=key_requirements__cloned_&properties=new_business_type&properties=business_type&properties=post_meeting_notes&properties=requirement___new&properties=type_of_product`; let after = ''; let data = []; do { const response = fetchHubSpotData(apiUrl, HUBSPOT_OAUTH_TOKEN, after); if (response.results && response.results.length > 0) { data = data.concat(response.results); after = response.paging ? response.paging.next.after : null; } else { after = null; } } while (after); if (data.length > 0) { Logger.log(data[0]) const headers = ['communication_engine_service', 'friction', 'funding_stage', 'industry_sub_segment', 'key_requirements__cloned_', 'new_business_type', 'business_type', 'post_meeting_notes', 'requirement___new', 'type_of_product']; const newData = data.map(obj => { const rowData = headers.map(key => { if (key in obj.properties) { return obj.properties[key]; } else { return ''; // Handle missing properties gracefully } }); return rowData; }); Logger.log(newData[0].length) Logger.log(newData.length) if (existingData.length > 0) { newData.unshift(...existingData); } const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); sheet.clearContents(); sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData); // Fixing the range dimensions. } } function fetchHubSpotData(apiUrl, token, after) { const params = { method: 'GET', headers: { Authorization: `Bearer ${token}`, }, }; let apiUrlWithParams = apiUrl; if (after) { apiUrlWithParams += `?after=${after}`; } const response = UrlFetchApp.fetch(apiUrlWithParams, params); return JSON.parse(response.getContentText()); } // Set up a trigger to update the data every hour. function setUpTrigger() { ScriptApp.newTrigger('fetchContactsAndCompanies') .timeBased() .everyHours(1) .create(); }

Re: Trying to connect Hubspot to Google Sheets

danmoyle — Mon, 07 Aug 2023 12:50:24 GMT

@NLakmali not a solution to your request, but a question: Why send the data to a Google Sheet for charts when HubSpot reporting shows a lot of data in reporting (charts, tables in Sales Analytics) in the standard portal? Is it because of the HubSpot Hub + Tier you have?

My guess for your question is that it has to do with the app you've created. I'd check out this HubSpot Academy course to better understand integrations and private apps. That may help.

Re: Trying to connect Hubspot to Google Sheets

NLakmali — Tue, 08 Aug 2023 06:06:24 GMT

Hi Dan,

Thank you for your help. I am trying to connect Hubspot to Google Sheets since I have custom columns that I would like to work with, which you correctly mentioned, is not a toggle-able feature in the current package that I am on. Is there any other way to fix this or something that I can use to fix the app script ?

Re: Trying to connect Hubspot to Google Sheets

danmoyle — Tue, 08 Aug 2023 17:40:40 GMT

@NLakmali in that case, I'd look at the Google Sheets integration here, and the add data to Google Sheets with workflows resource here for additional help. If those don't work, I'd suggest talking to someone familiar in the HubSpot developer world. I'm not a dev, but I work with some. Or the Developers Community here has folks, too.

Re: Trying to connect Hubspot to Google Sheets

rlote — Thu, 17 Oct 2024 07:40:10 GMT

Hey, you can do this with Superjoin (https://www.superjoin.ai/integrations/hubspot). It’s made specifically for reporting on Gsheets.

Superjoin helps you import your data from Hubspot to Google sheets and let's you control what data points you want to import, with scheduled auto refreshes which pulls in new data automatically.

We can also help you making customized live dashboard which helps you view data for your reporting requirements, which gets updated every time new data is added. It's a game-changer! Do try it!

topic Re: Trying to connect Hubspot to Google Sheets in Dashboards & Reporting

Trying to connect Hubspot to Google Sheets

Re: Trying to connect Hubspot to Google Sheets

Re: Trying to connect Hubspot to Google Sheets

Re: Trying to connect Hubspot to Google Sheets

Re: Trying to connect Hubspot to Google Sheets