I have a CRM system that pushes information to my Google Sheets every day through their API. The data comes in with some columns filled and others empty. I wrote a script to process this incoming data and populate the missing fields, but it only works when I manually edit cells.
Current situation:
API fills column A with tracking IDs
API leaves columns B, D, and E blank
API puts timestamp values in column C
My script should fill the empty columns and convert the timestamps
What I need:
Column B gets specific text
Column C converts timestamp to readable date
Column D gets a number
Column E gets currency code
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++) {
// Fill column B with status text
activeSheet.getRange(currentRow, 2).setValue('Final - ADS - Offline Conversions - All');
// Convert timestamp in column C
var timestampCell = activeSheet.getRange(currentRow, 3);
var timestampValue = timestampCell.getValue();
if (!isNaN(timestampValue) && timestampCell.getFormula() === '') {
timestampCell.setFormula('=EPOCHTODATE(' + timestampValue + ', 2)');
Utilities.sleep(300);
var convertedDate = timestampCell.getValue();
timestampCell.setValue(convertedDate);
}
// Set numeric value in column D
activeSheet.getRange(currentRow, 4).setValue(1500);
// Add currency code to column E
activeSheet.getRange(currentRow, 5).setValue('USD');
}
}
The script works perfectly when I manually add data, but when the CRM sends data via API, nothing happens. I tried onChange and time-based triggers, but time-based triggers affect all rows instead of just the new ones. How can I make this work with API data insertions?
I hit this constantly with third-party API integrations. The problem is programmatic updates completely bypass Google’s edit triggers. Here’s what worked for me: I set up a flag-based system using a dedicated status column. Instead of relying on triggers, I built a simple polling mechanism that checks for unprocessed rows every 2-3 minutes. I added a ‘processed’ flag column that starts empty when the CRM writes data. My script looks for empty flags, processes those rows with your logic, then marks them complete. This handles irregular API timing perfectly - some CRM systems batch updates or have delays. The processing overhead is tiny since you’re only scanning one column for empty values instead of comparing timestamps or row counts. I’ve been running this setup for 8 months across multiple CRM feeds with zero missed records or duplicate processing.
Had this exact problem last year building something similar. onEdit won’t fire for API writes - only manual edits. Switch to a time-based trigger that checks for new rows against a stored reference point. I created a properties service entry tracking the last processed row number. My time-based function runs every 5-10 minutes, checks for new rows beyond that point, and only processes those. Use PropertiesService.getScriptProperties().getProperty(‘lastProcessedRow’) to get the stored value, then update it after processing. Instead of processing everything each time, you’re only hitting the new stuff. Avoids those performance issues you mentioned. Mine runs every 10 minutes and handles 200-300 new records daily with zero problems.
Yeah, the onEdit trigger with API data is a classic pain point. Skip the time-based triggers that churn through everything - use a hybrid approach with installable triggers instead. Set up an onChange trigger that fires when your spreadsheet structure changes (like when your CRM adds new rows). This catches API insertions without needing manual edits. In your onChange function, just check if column B is empty - that’s how you know which rows need processing. Handle those rows and you’re done. Way more efficient than polling every few minutes, and it only runs when your CRM actually pushes data. I’ve used this setup with Salesforce and HubSpot integrations and it’s been rock solid.
API calls don’t trigger onEdit - that’s your problem right there. Here’s what works: add a helper column to track which rows you’ve processed. When your CRM pushes new data, that column stays empty. Set up a time trigger to run every few minutes, find the blank helper cells, process those rows, then mark them ‘done’. Way cleaner than tracking row numbers and handles data gaps perfectly.
onEdit won’t work with API data - Google handles programmatic updates differently. Try a timestamp approach instead: add a column tracking when each row was last processed. Set a time trigger to run every few minutes and look for rows where the process timestamp is older than the data timestamp (or empty). This catches new API data without killing performance since you’re just comparing dates, not scanning entire rows.