Update data send by Hubspot to a Spreadsheet

Question

I'm using hubspot CRM to send data daily to my spreadsheet: In column A it fills with a GCLID In Column B it fills with nothing In Column C it fills if a UNIX date value In Column D it fills with nothing In Column E it fills with nothing.

So i built a Appscript to edit these fields like: In Column A nothing Happens In Column B it fills with a text In Column C it converts the unix date value to regular date format with epochtodate In Column D it fills with a numeric value In column E it fills with a text

I can't say more than this because it's some private data.

The appscript works just fine, when i edit something, like i add data on the row below, it edits and fills perfectly. But when hubspot does via API nothing happens.

I'd like to make this appscript works when hubspot sends data to the spreadsheet too.

Here's the script i'm using.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var startRow = range.getRow();
  var endRow = startRow + range.getNumRows() - 1;


  for (var row = startRow; row <= endRow; row++) {
    // Coluna B
    sheet.getRange(row, 2).setValue('Definitivo - GADS - Conversões Offline - Match all');


    // Coluna C (Converter valor Unix para data usando a fórmula EPOCHTODATE)
    var cell = sheet.getRange(row, 3);
    var cellValue = cell.getValue();
    if (!isNaN(cellValue) && cell.getFormula() === '') {
      // Adicionar a fórmula temporariamente
      cell.setFormula('=EPOCHTODATE(' + cellValue + ', 2)');
     
      // Aguardar um momento para a fórmula calcular o valor
      Utilities.sleep(500);
     
      // Converter o valor calculado para um valor estático
      var calculatedValue = cell.getValue();
      cell.setValue(calculatedValue);
    }


    // Coluna D
    sheet.getRange(row, 4).setValue(2100);


    // Coluna E
    sheet.getRange(row, 5).setValue('BRL');
  }
}

Already tried using on change and on edit triggers. Already tried using time as a trigger but it changes all rows, i need it to trigger only on new filled rows, like new data sent from hubspot.

Script executions and API requests do not cause triggers to run.. A time-based trigger would be my choice. You need to identify some aspect of the data that indicates 1) whether a data row is submitted by HubSpot (vs a manual edit) and 2) whether the HubSpot data row has been "converted". You have not given an example of the data as submitted by HubSpot, and the data as converted by the script, but I'd say that Column C (UNIX => regular date) would be a possible indicator. — Tedinoz
– Tedinoz, Commented Jul 28, 2024 at 2:48

Tedinoz · Accepted Answer · 2024-08-10 05:19:34Z

0

You want to update Hubspot data. You need to use a time-driven trigger.

Consider this answer:

dataRange - the entire data range is evaluated
getDisplayValues - returns Epoch timestamp as a string; avoids inadvertent conversion to a date.
if(rowData[1].length !== 0) - through a loop, each value in Column B is evaluated for a blank (no length)
- if no:
  - the script jumps to the next row
- if yes:
  - updates for each Column are made
  - the data for that row is updated.
sheet.getRange(i+1,3).setNumberFormat("mmmm dd, yyy hh:mm:ss") - formats the date as required.

function updateHubspot() {
   var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Sheet2")
  
  // establish key variables
  var lastRow = sheet.getLastRow()

  // get the data
  var dataRange = sheet.getDataRange()
  //Logger.log("DEBUG: the data range = "+dataRange.getA1Notation())
  // note use of "getDisplayValues"
  // returns the Epoch timestamp as a string
  // avoids any inadvertent/unintended conversion to a date
  var data = dataRange.getDisplayValues()

  // Loop through the rows to find blank cells in Column B
  for (var i=1;i<lastRow;i++){ // row 0=header
    var rowData = data[i]
    // Logger.log(rowData) // DEBUG

    // test if Column B is blank
    if(rowData[1].length !== 0){
      // this row  converted, 
      // Logger.log("DEBUG: i:"+i+", Column B length:"+rowData[1].length+", value = "+rowData[1]+", row  converted")
      continue
    }

    Logger.log("DEBUG: i:"+i+", row:"+(+i+1)+", Column B length:"+rowData[1].length+", value:"+rowData[1]+", EPOCH value:"+rowData[2]+" - convert the row")
    // update Column A
    // do nothing
    
    // update Column B
    rowData[1] = 'Definitivo - GADS - Conversões Offline - Match all'
    
    // update Column C (Epock Timestamp) to a "conventional date"
    // multiply converts string to number
    // multiply by 1000 to get seconds. 
    var dateObj = new Date(rowData[2]*1000);
    rowData[2] = dateObj
    
    // update Column D
    rowData[3] = 2100
    
    // Update Column E
    rowData[4] = 'BRL'
    // Logger.log("DEBUG: i:"+i+", length = "+rowData.length+", rowData = "+rowData)

    // set the update range and update chnages for this row
    var targetRange = sheet.getRange(i+1,1,1,5)
    // Logger.log("DEBUG: the target range = "+targetRange.getA1Notation())
    targetRange.setValues([rowData])
    // format the date
    // Logger.log("DEBUG: the date range = "+sheet.getRange(i+1,3).getA1Notation())
    sheet.getRange(i+1,3).setNumberFormat("mmmm dd, yyy hh:mm:ss");

  }

}

SAMPLE - BEFORE

SAMPLE - AFTER

edited Aug 10, 2024 at 5:19

answered Jul 28, 2024 at 4:04

Tedinoz

8,3943 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rafael Guimares de Assis Motta Over a year ago

Hello! I gonna test it out and see if it's gonna work. As soon as i can i gonna come back here and tell if it worked. Thank you!

Rafael Guimares de Assis Motta Over a year ago

Hey! I just tested it using a time-driven trigger. The trigger executed the script, although it didn't change anything on my sheet. The data is: a40 TESTE RAFAEL C40: 1722279423 It was supposed to convert 1722279423 and add the texts on b, d, e, right?

Tedinoz Over a year ago

It was supposed to convert 1722279423 and add the texts on b, d, e, right? Correct, and there was a basic flow in the code. I've updated the script; please try it again.

Collectives™ on Stack Overflow

Update data send by Hubspot to a Spreadsheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related