Update data send by Hubspot to a Spreadsheet

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.

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