Help with google script to write email addresses

contacts

#1

Hello, I am trying to make it so google sheets populates the email addresses of contacts in Hubspot, but I can't seem to get it right. Any help would be really appeciated.

The error I am currently getting is "TypeError: Cannot call method "forEach" of undefined. (line 135, file "promocode")". Line 135 is " result.emails.forEach(function(email) {"

var sheetNamepromoCodes = "promoCodes";

  • ###########################################################################
  • -----------------------------------------------------------------------

  • ------------------------------- GET DATA ------------------------------

  • -----------------------------------------------------------------------

  • ###########################################################################
    */

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

// We'll take three properties from the deals: the source, the stage & the amount of the deal
var url = API_URL + "/contacts/v1/lists/all/contacts/all?properties=email"
var response = UrlFetchApp.fetch(url, headers);
var result = JSON.parse(response.getContentText());

// For each deal, we take the stageId, source & amount
  var emails = Array();

result.emails.forEach(function(email) {
emails.push([email.value]);
});

return emails;
}

/**

  • ###########################################################################
  • -----------------------------------------------------------------------

  • -------------------------- WRITE TO SPREADSHEET -----------------------

  • -----------------------------------------------------------------------

  • ###########################################################################
    */

/**

  • Print the different deals that are in your pipeline to the spreadsheet
    */
    function writeEmails(emails) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetNamepromoCodes);

// Let's put some headers and add the deals to our table
var matrix = Array(["emailAddress"]);
matrix = matrix.concat(emails);

// Writing the table to the spreadsheet
var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
range.setValues(matrix);
}

/**

  • ###########################################################################
  • -----------------------------------------------------------------------

  • -------------------------------- ROUTINE ------------------------------

  • -----------------------------------------------------------------------

  • ###########################################################################
    */

/**

  • This function will update the spreadsheet. This function should be called
  • every hour or so with the Project Triggers.
    */
    function refresh() {
    var service = getService();

if (service.hasAccess()) {
var emails = getEmails();
writeEmails();

} else {
var authorizationUrl = service.getAuthorizationUrl();
Logger.log('Open the following URL and re-run the script: %s',
authorizationUrl);
}
}


#2

Hi @kendrieling,

You're setting the result variable to the parsed JSON response from HubSpot, but you're not checking to make sure the variable has a value first. It appears that in some situations, JSON.parse(response.getContentText()); is returning undefined, which makes result undefined, which causes an error when you try to call .forEach() on it's email property.

I would start by investigating why the result of JSON.parse(response.getContentText()); is evaluating to undefined; it seems likely that getContentText() isn't returning a value.