APIs & Integrations

jrz44
Membre

Get Companies data into googlesheet dashboard

Résolue

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 Votes
1 Solution acceptée
WendyGoh
Solution
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

Résolue

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!

Voir la solution dans l'envoi d'origine

0 Votes
4 Réponses
WendyGoh
HubSpot Employee
HubSpot Employee

Get Companies data into googlesheet dashboard

Résolue

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 Votes
jrz44
Membre

Get Companies data into googlesheet dashboard

Résolue

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

Get Companies data into googlesheet dashboard

Résolue

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 Votes
jrz44
Membre

Get Companies data into googlesheet dashboard

Résolue

 

@WendyGoh that worked!  Thank you so much!

 

0 Votes