Get Companies data into googlesheet dashboard

SOLVE
jrz44
Member

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 Upvotes
1 Accepted solution

Accepted Solutions
WendyGoh
Solution
HubSpot Employee

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!

View solution in original post

0 Upvotes
4 Replies 4
WendyGoh
HubSpot Employee

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 Upvotes
jrz44
Member

@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 Upvotes
WendyGoh
Solution
HubSpot Employee

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!

View solution in original post

0 Upvotes
jrz44
Member

 

@WendyGoh that worked!  Thank you so much!

 

0 Upvotes