How to import Hubspot Analytics API data into Google Sheets using Apps Script?

Other Questions HubSpot
1

Hey everyone,

I’m working on a project where I need to pull analytics data from Hubspot into Google Sheets. The goal is to do some calculations and then use it in a Google Data Studio dashboard.

I’ve managed to get the data from Hubspot’s Analytics API, which comes as JSON. Here’s what I’ve got so far:

function fetchHubspotData() {
  var auth = setupAuth();
  var headers = {headers: {'Authorization': 'Bearer ' + auth.getToken()}};
  var data = [];

  var endpoint = API_ENDPOINT + '/reports/traffic/monthly?start=20230101&end=20231001';
  var response = UrlFetchApp.fetch(endpoint, headers);
  var result = JSON.parse(response.getContentText());
  console.log(result);
}

This seems to work fine, and I’m receiving the data. Now, I’m having trouble figuring out how to write this data into a Google Sheet. Can anyone provide some guidance on how to achieve that?

Thanks in advance for your help!

2

hey nateharris, i’ve done similar stuff before. after you get the data, use SpreadsheetApp to write it to sheets. something like:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

just make sure to format ur data as a 2D array first. hope this helps!