Displaying Labels instead of Internal Values (Google Apps Script/Google Sheets/HubSpot)

Question

You could say I'm a real newbie to coding. Right now I'm trying to connect the HubSpot Account to Google Data Studio via Google Apps Script and Google Sheets. I'm starting with simple stuff like in this example:

https://medium.com/@alexisbedoret/create-a-hubspot-custom-dashboard-with-google-spreadsheet-and-data-studio-27f9c08ade8d

I tried to build my code like the one in the example above. The problem is, that I'm getting the internal values of the "Deal Stage" and the "Pipeline" (the status if you will) the deals are in instead of the respective labels or names that are "attached" to the internal values (see photo)

Other stuff, like the Deal Name (or "Bewerbermanagementsystem" in the picture) is working properly.

Don't know if this is enough information for you guys, but it would be great if someone could help me, because I really do lack of knowledge in coding :D

Here's the section of the code:

   function getDeals() {
   var service = getService();
   var headers = {headers: {'Authorization': 'Bearer '+ service.getAccessToken()}};
   var keep_going = true;
   var offset = 0;
   var deals = Array();
   while(keep_going) {
      var url = API_URL + "/deals/v1/deal/paged?properties=dealstage&properties=pipeline&properties=bewerbermanagementsystem&properties=amount&properties=dealname&properties=dealtype&limit=250&offset="+offset;
      var response = UrlFetchApp.fetch(url, headers);
      var result = JSON.parse(response.getContentText());
      keep_going = result.hasMore;
      offset = result.offset;
      result.deals.forEach(function(deal) {
         var dealstage = (deal.properties.hasOwnProperty("dealstage")) ? deal.properties.dealstage.value : 0;
         var pipeline = (deal.properties.hasOwnProperty("pipeline")) ? deal.properties.pipeline.value : 0;
         var bewerbermanagementsystem = (deal.properties.hasOwnProperty("bewerbermanagementsystem")) ? deal.properties.bewerbermanagementsystem.value : "unknown";
         var amount = (deal.properties.hasOwnProperty("amount")) ? deal.properties.amount.value : 0;
         var dealname = (deal.properties.hasOwnProperty("dealname")) ? deal.properties.dealname.value : 0;
         var dealtype = (deal.properties.hasOwnProperty("dealtype")) ? deal.properties.dealtype.value : 0;
         deals.push([stageId,pipeline,bewerbermanagementsystem,amount,dealname,dealtype]);
      });
   }
   return deals;
}

Try running your code in debugger. You'll get a lot of feedback from it. — Cooper, Nov 07 '18 at 18:54
@Lars How are you going with your Google scripts problem? Worked it out, or still needing help? — Tedinoz, Nov 23 '18 at 21:50

bchatham · Answer 1 · 2023-01-16T22:58:16.473

The integration module provide by HubSpot may eliminate the need to write custom code to extract HubSpot data to a Google Sheet. Using the Update data in a Google Sheet workflow action with the Create a new row if none match setting works quite well to maintain a current list of objects (Contacts, Deals, etc.) and almost immediately mirrors in Sheets the data in HubSpot (note: I don't think it tracks record deletions).

That said, the integration returns the "internal values" of properties, and for Deal Stages for example, these may not be at all intuitive or relevant.

I worked around this my creating a mapping table in Sheets that translates the raw data values imported from HubSpot into another column in the sheet with sensible values:

Using an XLOOKUP array function in Google Sheets, you can translate an entire column of raw HubSpot values into meaningful relabelled ones. The function is:

={"Stage";arrayformula(if($A$2:$A="", "", XLOOKUP($C$2:$C, KeyValues!$A$2:$A, KeyValues!$B$2:$B, na(), 0, 1)))}

This array function translates the entire Raw Stage column in one shot into the Stage column. It should be placed in the header row of your data integration table. I find the array formula is easier to maintain than using individual relative row-level formulas. You can then feed the translated column to pivot tables or other calcs. I added the Index value associated with the mapped Label field to facilitate ordering categories in pivot tables.

The array function to lookup the Index value is nearly identical to the one used to look up the mapped data values:

={"Index";arrayformula(if($A$2:$A="", "", XLOOKUP($C$2:$C, KeyValues!$A$2:$A, KeyValues!$C$2:$C, na(), 0, 1)))}

I found that HubSpot will occasionally get confused and add duplicate rows, so I created the Dupe column that flags duplicate values based on a unique ID like Deal Name or the HubSpot recordID:

={"Dupe";arrayformula(if($A$2:$A="", "", COUNTIF($A$2:$A, $A$2:$A )-1))}

If you do see duplicates, it's important to know which row in the Google Sheet is actually the current record in HubSpot; you should always export the recordID in your data and you can then construct a URL to see what the Sheets row refers to using a formula like this:

={"URL";arrayformula(if($A$2:$A="", "", "https://app.hubspot.com/contacts/[Your HubSpot ID goes Here]/deal/"&$F2:F))}

The unnecessary (duplicate) row in your sheet will result in a 404 error in HubSpot, so that's the one you can delete.

Displaying Labels instead of Internal Values (Google Apps Script/Google Sheets/HubSpot)

1 Answers1