APIs & Integrations

jrz44
Miembro

Get Companies data into googlesheet dashboard

resolver

Hi - I've been using these articles as guides for getting deals and contacts into googlesheets, but I can't find a good resource for getting all company properties into a googlesheet.  I'm using this to API_URL to get the data and these internal_names to pull the data

 

But it's not running thorugh all of the companies (it's stopping at 250 instead of 10,000 companies) and it's not retrieving any of the data

 

Any help would be most appreciated!  Code below:

 

function getCompanies() {
// Authentication to Hubspot
var service = getService();
var options = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
var numResults = 0;

// We are going to put all the date into that array, starting with the header of our sheet
var data = new Array();
data.push(["name", "website"]);

var keep_going = true;
var hasMore = false;
var offset = 0;

while (keep_going)
{
var url_query = API_URL + "/companies/v2/companies/paged?property=name&property=website&limit=250&offset="+offset;
var response = UrlFetchApp.fetch(url_query, options);
var result = JSON.parse(response.getContentText());

 

keep_going = result.hasMore;
offset = result.offset;

result.companies.forEach(function(item) {
var name = (item.properties.hasOwnProperty('name')) ? item.properties.name.value : "NA";
var website = (item.properties.hasOwnProperty('website')) ? item.properties.website.value : "NA";
data.push([name, website]);
numResults++;
});
}

Logger.log(numResults);

// This function will clear and fill in the sheet with the data
writeCompanies("Comps",data);
}

 

function writeCompanies(sheetName,results)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
sheet.clear();

var setRange = sheet.getRange(1,1,results.length,results[0].length);
setRange.setValues(results);
}

 

0 Me gusta
1 Soluciones aceptada
WendyGoh
Solución
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

resolver

Hi @jrz44,

 

That's strange. 

 

Could we debug this further by logging the var result under the console

 

var result = JSON.parse(response.getContentText());

to see if it's getting back any result?

 

If it is, can we see if both the keep_going and offset var is being set correctly by logging it in the console? 

Also, as the has-more key contain hypen, it looks like we must accessed it via [ ] rather than dot notation. Referencing this documentation - https://developers.google.com/apps-script/guides/triggers/events.

 

I believe it should be something like this:

keep_going = result[has-more];

instead of 

keep_going = result.has-more;

Let me know if this works!

Ver la solución en mensaje original publicado

0 Me gusta
4 Respuestas 4
WendyGoh
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

resolver

Hi @jrz44,

 

The code logic looks right to me however one thing that stood out was the way you get result.hasMore. 

 

The JSON response body for HubSpot Get all companies endpoint return key "has-more" and not "hasMore".

 

Could you try changing this line of code:

 

keep_going = result.hasMore;

to

 

keep_going = result.has-more;

and see if it works?

0 Me gusta
jrz44
Miembro

Get Companies data into googlesheet dashboard

resolver

@WendyGoh thanks for replying!  When I type in "has-more", the code won't run:

 

ReferenceError: "more" is not defined. (line 133, file "Code")

 

I think it sees the hyphen and thinks it's a separate variable that isn't prior defined.  Any other ideas?  I'm not even getting the data populating into the spreadsheet for the first 250 items.

0 Me gusta
WendyGoh
Solución
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

resolver

Hi @jrz44,

 

That's strange. 

 

Could we debug this further by logging the var result under the console

 

var result = JSON.parse(response.getContentText());

to see if it's getting back any result?

 

If it is, can we see if both the keep_going and offset var is being set correctly by logging it in the console? 

Also, as the has-more key contain hypen, it looks like we must accessed it via [ ] rather than dot notation. Referencing this documentation - https://developers.google.com/apps-script/guides/triggers/events.

 

I believe it should be something like this:

keep_going = result[has-more];

instead of 

keep_going = result.has-more;

Let me know if this works!

0 Me gusta
jrz44
Miembro

Get Companies data into googlesheet dashboard

resolver

 

@WendyGoh that worked!  Thank you so much!

 

0 Me gusta