APIs & Integrations

jrz44
メンバー

Get Companies data into googlesheet dashboard

解決

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 いいね!
1件の承認済みベストアンサー
WendyGoh
解決策
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

解決

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 いいね!
4件の返信
WendyGoh
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

解決

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 いいね!
jrz44
メンバー

Get Companies data into googlesheet dashboard

解決

@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 いいね!
WendyGoh
解決策
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

解決

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 いいね!
jrz44
メンバー

Get Companies data into googlesheet dashboard

解決

 

@WendyGoh that worked!  Thank you so much!

 

0 いいね!