Google Apps Script onEdit trigger not functioning with external API updates (Zapier integration)

[sophialee92]

I’m facing an issue where my Apps Script function works fine with manual edits, but it doesn’t respond when data is updated via API calls from Zapier.

Here’s how my setup works: I use Zapier to fetch data from Stripe and populate one sheet, while my script is intended to process that data and make changes to another sheet. The script triggers perfectly with manual inputs, but it stays dormant when new data arrives via the API.

I’ve tried implementing time-based triggers, but I couldn’t get them to work as needed. Has anyone experienced this issue before? It seems like changes from the API aren’t activating the onEdit trigger, and I could use guidance on the best solutions available.

function processData(e) {
  var mainTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MainData");
  var apiTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("APIData");
  var mainData = mainTab.getDataRange().getValues();
  var firstCol = 1;
  var changedRow = e.range.getRow();
  var changedColumn = e.range.getColumn();
  var newValue = apiTab.getRange(changedRow, firstCol).getValue();
  var timestamp = apiTab.getRange(changedRow, firstCol + 1).getValue();
  var foundMatch = false;
  var insertRow;
  
  if (changedColumn == firstCol) {
    for (var j = 0; j < mainData.length; j++) {
      if (mainData[j][firstCol - 1] == newValue) {
        foundMatch = true;
        var currentCount = mainTab.getRange(j + 1, firstCol + 1).getValue() || 0;
        mainTab.getRange(j + 1, firstCol + 1).setValue(currentCount + 1);
        break;
      }
    }
    
    if (!foundMatch) {
      insertRow = mainData.length + 1;
      mainTab.getRange(insertRow, firstCol).setValue(newValue);
      if (timestamp >= new Date("2024-01-01T00:00:00.000Z") && timestamp <= new Date("2024-01-07T18:00:00.000Z")) {
        mainTab.getRange(insertRow, firstCol + 1).setValue(1);
      } else if (timestamp > new Date("2024-01-07T18:00:01.000Z") && timestamp <= new Date("2024-01-14T18:00:00.000Z")) {
        mainTab.getRange(insertRow, firstCol + 1).setValue(2);
      }
    }
  }
}

function onEdit(e) {
  var currentSheet = e.source.getActiveSheet();
  if (currentSheet.getName() === "APIData") {
    processData(e);
  }
}

[Tom42Gamer]

Had this exact issue with my CRM integration last year. The problem is onEdit triggers only work when users manually edit cells - they don’t catch programmatic updates. Here’s what fixed it for me: I used installable onChange triggers plus a simple polling system. The onChange trigger watches for any spreadsheet changes, and I throw in SpreadsheetApp.flush() to make sure everything gets saved. But since API updates can still slip past onChange, I added a time-based trigger that runs every 2-3 minutes. It just checks row counts or timestamps to catch anything that got missed. Works great - catches everything without hammering the API.

[Alex_Thunder]

you could also set up time-driven triggers to check for new data every minute. i ran into the same problem - external api updates don’t trigger onEdit. just store the last processed row number somewhere and compare it each time to see if there’s new data to handle.

[sophiac]

Yes, this issue arises because the onEdit function only activates from manual changes, not from API updates. I’ve faced this challenge too with my integrations. A straightforward solution is to switch to the onChange trigger; it responds to structural changes in the sheet and is more in sync with how Zapier updates data. Additionally, consider having Zapier trigger a web app endpoint in your Apps Script, allowing for real-time processing without relying on triggers. This method provides direct interaction and is often more reliable.