Is there a way to run Apps Script in Google Sheets when Zapier updates a row?

Other Questions Zapier
1

Hey everyone! I’m having trouble with my Google Sheets setup. I’ve got a function that’s supposed to run when a row is changed, but it only works when I edit the sheet myself. It doesn’t do anything when Zapier updates the sheet through the API.

Here’s what I’m trying to do:

  • Sheet2 gets info from Stripe via Zapier
  • Sheet1 should update based on changes in Sheet2

I’ve tried using time-based triggers, but no luck. Anyone know how to make the script run when the API updates the sheet? Here’s a simplified version of what I’m working with:

function updateCounter(event) {
  const mainSheet = SpreadsheetApp.getActive().getSheetByName('MainSheet');
  const dataSheet = SpreadsheetApp.getActive().getSheetByName('DataSheet');
  
  const changedRow = event.range.getRow();
  const changedCol = event.range.getColumn();
  
  if (changedCol === 1) {
    const newValue = dataSheet.getRange(changedRow, 1).getValue();
    const newDate = dataSheet.getRange(changedRow, 2).getValue();
    
    // Logic to update MainSheet based on DataSheet changes
    // ...
  }
}

function onEdit(e) {
  if (e.source.getActiveSheet().getName() === 'DataSheet') {
    updateCounter(e);
  }
}

Any ideas on how to get this working with API updates? Thanks!

2

I’ve faced a similar challenge with Google Sheets and Zapier. Unfortunately, the onEdit() trigger only fires for manual edits, not API updates. However, I found a workaround that might help you.

Instead of relying on onEdit(), you could set up a time-driven trigger to run your script every few minutes. This way, it’ll catch any changes made by Zapier, albeit with a slight delay.

Here’s how I modified my script:

function checkForChanges() {
  const dataSheet = SpreadsheetApp.getActive().getSheetByName('DataSheet');
  const lastRow = dataSheet.getLastRow();
  
  for (let i = 1; i <= lastRow; i++) {
    // Check if row has been updated (you might need to add a 'last updated' column)
    if (rowNeedsProcessing(i)) {
      updateCounter({range: dataSheet.getRange(i, 1)});
    }
  }
}
  1. Set up a time-driven trigger for this function in the Apps Script editor.

This approach has worked well for me, ensuring that Zapier updates are processed without manual intervention. Just remember to optimize your script if you’re dealing with large datasets to avoid hitting execution time limits.

3

hey there! i’ve run into this too. zapier updates don’t trigger onEdit(). one trick is to use Google Sheets’ IMPORTRANGE function. set it up in a separate sheet to pull data from your Zapier-updated sheet. then use onEdit() on this new sheet. it’s not perfect, but it works for me!

4

I’ve encountered this issue before, and it can be frustrating. Unfortunately, Apps Script doesn’t natively support triggering on API updates. However, there’s a workaround that’s worked well for me.

Consider using Google Cloud Functions in conjunction with Zapier. Instead of having Zapier update the sheet directly, set up a Cloud Function that Zapier can call. This function can then update the sheet and trigger your Apps Script code.

Here’s a high-level approach:

  1. Create a Google Cloud Function that updates your sheet.
  2. Modify your Zapier workflow to call this function instead of updating the sheet directly.
  3. In the Cloud Function, after updating the sheet, use the Apps Script API to trigger your script.

This method ensures your script runs whenever data is updated, regardless of the source. It’s more complex to set up initially, but provides a robust solution for automated updates.