-1

I need your help.

I'm trying to get analytics data from my Hubspot account into a Google Spreadsheet, so I can make some calculations with it and feed it into a Google Data Studio Dashboard.

I'm making requests to Hubspot's Analytics API, which returns data as a JSON.

function getAnalytics() {
// Prepare authentication to Hubspot
   var service = getService();
   var headers = {headers: {'Authorization': 'Bearer '+service.getAccessToken()}};
   var analytics = Array();

// API request

   var url = API_URL + "/analytics/v2/reports/sessions/monthly&start=20180101&end=20181001";
   var response = UrlFetchApp.fetch(url, headers);
   var result = JSON.parse(response.getContentText());  
   Logger.log(result)

}

up to this point it seems the code does the job.

Logger.log (result)

returns:

[18-10-20 19:40:40:112 PDT] {2018-09-01=[{directTraffic=796, desktop=1347, emailMarketing=194, referrals=24, breakdown=sessions, mobile=1372, socialMedia=396, organicSearch=500, paidSearch=814, others=5}, {directTraffic=654, desktop=1204, emailMarketing=165, referrals=22, breakdown=new-visitors, mobile=1279, socialMedia=379, organicSearch=481, paidSearch=782}, {directTraffic=8417, desktop=8366, emailMarketing=6649, referrals=8750, breakdown=bounce-percent-dec, mobile=9037, socialMedia=7398, organicSearch=9340, paidSearch=9717, others=8000}, {directTraffic=125, desktop=117, emailMarketing=108, referrals=100, breakdown=views-per-session-dec, mobile=106, socialMedia=103, organicSearch=112, paidSearch=103, others=0}, {directTraffic=134, desktop=130, emailMarketing=142, referrals=112, breakdown=interactions-per-session-dec, mobile=113, socialMedia=130, organicSearch=115, paidSearch=104, others=120}, {directTraffic=75, desktop=65, emailMarketing=72, referrals=15, breakdown=time-per-session, mobile=22, socialMedia=47, organicSearch=29, paidSearch=15, others=48}], 2018-10-01=[{directTraffic=27, desktop=47, emailMarketing=11, breakdown=sessions, mobile=33, socialMedia=9, organicSearch=10, paidSearch=23}, {directTraffic=23, desktop=39, emailMarketing=10, breakdown=new-visitors, mobile=32, socialMedia=7, organicSearch=9, paidSearch=22}, {directTraffic=8518, desktop=9148, emailMarketing=7272, breakdown=bounce-percent-dec, mobile=9090, socialMedia=10000, organicSearch=10000, paidSearch=10000}, {directTraffic=107, desktop=100, emailMarketing=100, breakdown=views-per-session-dec, mobile=103, socialMedia=88, organicSearch=100, paidSearch=100}, {directTraffic=114, desktop=108, emailMarketing=127, breakdown=interactions-per-session-dec, mobile=109, socialMedia=100, organicSearch=100, paidSearch=100}, {directTraffic=7, desktop=5, emailMarketing=58, breakdown=time-per-session, mobile=18, socialMedia=0, organicSearch=0, paidSearch=0}], 2018-08-01=[{directTraffic=908, desktop=1184, emailMarketing=57, referrals=63, breakdown=sessions, mobile=1308, socialMedia=176, organicSearch=362, paidSearch=935, others=9}, {directTraffic=774, desktop=1055, emailMarketing=46, referrals=54, breakdown=new-visitors, mobile=1249, socialMedia=161, organicSearch=349, paidSearch=922, others=2}, {directTraffic=8865, desktop=8952, emailMarketing=7719, referrals=8412, breakdown=bounce-percent-dec, mobile=9686, socialMedia=9715, organicSearch=9419, paidSearch=9839, others=7777}, {directTraffic=124, desktop=124, emailMarketing=129, referrals=119, breakdown=views-per-session-dec, mobile=104, socialMedia=111, organicSearch=113, paidSearch=102, others=55}, {directTraffic=134, desktop=133, emailMarketing=157, referrals=123, breakdown=interactions-per-session-dec, mobile=105, socialMedia=113, organicSearch=116, paidSearch=102, others=133}, {directTraffic=59, desktop=64, emailMarketing=98, referrals=96, breakdown=time-per-session, mobile=8, socialMedia=16, organicSearch=39, paidSearch=4, others=52}],

What I need to do now is to right this data into a Spreadsheet. And I'm having trouble doing it.

Can you guys help on this end?

Thank you so much.

best

Lucas Farias de Moraes Sarment
  • 55
  • 6
  • SO is not a free script writing service. Anyway, use something like Zapier instead: https://zapier.com/apps/google-sheets/integrations/hubspot – wp78de Oct 21 '18 at 04:16

1 Answers1

0

my google apps script works to parse the JSON response on a hubspot contact query. I'm sure your variables will be different, but this is how it's working for me.

        // API request
           var url = API_URL + "/analytics/v2/reports/sessions/monthly&start=20180101&end=20181001";
       //  Your code above       
           var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
       //  Make request to API and get response before this point.
           var json = response.getContentText();
           var data = JSON.parse(json);
           //Logger.log(data.contacts[0]);
           var id = data['contacts'][0]['identity-profiles'][0].vid; 
           var who = data['contacts'][0]['identity-profiles'][0]['identities'][0]['value']; 
           var rep_id = data['contacts'][0].properties.relationship_manager['value']; 
       //  Logging the values collected
           Logger.log(id);
           Logger.log(who);
           Logger.log(rep_id);
    }

Depends on how deep the data is nested on which format you will need.

Hope this helps.

Joy Hanawa
  • 101
  • 8