Google Sheets script not triggering when CRM API adds new rows

Other Questions HubSpot
1

I have a CRM system that pushes data to my Google Sheet every day through an API connection. The CRM fills these columns:

  • Column A gets a tracking ID
  • Column B stays empty
  • Column C gets a timestamp in UNIX format
  • Column D stays empty
  • Column E stays empty

I wrote a Google Apps Script to process this data and fill the empty columns:

  • Column A stays the same
  • Column B gets a description text
  • Column C converts the UNIX timestamp to readable date
  • Column D gets a number value
  • Column E gets a currency code

My script works when I manually add data to test it, but it doesn’t run when the CRM API adds new rows automatically. I need the script to trigger whenever new data comes from the CRM.

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var editedRange = e.range;
  var firstRow = editedRange.getRow();
  var lastRow = firstRow + editedRange.getNumRows() - 1;

  for (var currentRow = firstRow; currentRow <= lastRow; currentRow++) {
    // Set description in column B
    activeSheet.getRange(currentRow, 2).setValue('Final - Google Ads - Offline Conversions - All Match');

    // Convert UNIX timestamp in column C
    var timestampCell = activeSheet.getRange(currentRow, 3);
    var unixValue = timestampCell.getValue();
    if (!isNaN(unixValue) && timestampCell.getFormula() === '') {
      timestampCell.setFormula('=EPOCHTODATE(' + unixValue + ', 2)');
      Utilities.sleep(300);
      var convertedDate = timestampCell.getValue();
      timestampCell.setValue(convertedDate);
    }

    // Set value in column D
    activeSheet.getRange(currentRow, 4).setValue(1500);

    // Set currency in column E
    activeSheet.getRange(currentRow, 5).setValue('USD');
  }
}

I tried using onChange and onEdit triggers but neither works with API updates. I also tried time-based triggers but they process all rows instead of just the new ones. How can I make this work only for newly added rows from the API?

2

I encountered a similar issue while working with another CRM integration. The key limitation is that direct API updates do not trigger the onEdit or onChange events in Google Sheets, as those are reserved for manual edits. To resolve this, I utilized a time-driven trigger that executes every few minutes. The script identifies newly added rows by checking for non-empty cells in a specific column while leaving a designated tracking column empty. This way, it efficiently processes new data without duplicating efforts. I also used the Utilities.sleep function cautiously to manage API rate limits. This approach has effectively ensured that every new entry is captured and processed correctly.

3

The problem you’re facing is quite common with API integrations. Since external APIs bypass Google Sheets’ built-in triggers, you’ll need to implement a hybrid approach using time-based triggers with smart row detection. I solved this by adding a timestamp column that tracks when my script last processed data, then comparing it against new entries. Set up a time-driven trigger to run every 5-10 minutes, but instead of processing all rows, check for rows where column A has data but column B is still empty - this indicates unprocessed API additions. Store the last processed row number in PropertiesService to avoid reprocessing. This method has worked reliably for my Salesforce integration for over a year without missing any new records or creating duplicates.

4

api calls dont fire sheet triggers unfortunately. ive been using a workaround where i set a time trigger every 15mins and check if theres new data by comparing row count vs a stored value in script properties. works pretty well for my hubspot setup tho sometimes theres a delay