Best answer

New or Updated Spreadsheet Row in Google Sheets only works when a new row is added

  • 4 January 2021
  • 4 replies
  • 648 views

Userlevel 1

I basically want to repost this issue, as it has been closed / marked solved, but the issue is persisting for me. The trigger triggers when I insert a new row, but not when I update an existing row (with content that’s new and hasn’t been seen before). I don’t have any empty rows above the row I want to process.

 

In general, this stuff is pretty buggy (e.g., it suddenly stops detecting column headers and only works again if I create a new spreadsheet with the exact same contents). Would prefer paying 10x the monthly price to this frustrating experience.

icon

Best answer by nicksimard 5 January 2021, 02:34

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Userlevel 7
Badge +14

Have you checked the helped articles? https://zapier.com/apps/google-sheets/help

 

Perhaps you can provide a screenshot of how your Zap trigger is configured.

 

FYI: The GSheet New/Updated Row trigger, while marked as Instant, is not technically instant.

The Google Sheets trigger is marked "instant" but it still takes a few minutes to trigger.

The new and updated spreadsheet row triggers for Google Sheets are unique in that when there is a trigger event in the spreadsheet, Zapier gets a notification webhook from the Google about this following that notification, Zapier sends Google Sheet a request asking for new data. After this, the trigger works using the normal polling mechanism and these new or updated rows returned will trigger the Zap. This process takes about 3 minutes overall so although the Google Sheets trigger is marked "instant" it really is a hybrid of both webhook and polling techniques. While being slower than any other "instant" trigger, it's still faster than all polling triggers which would take 5 or 15 minutes.

Userlevel 7
Badge +14

If you’re looking for an alternative to GSheets, then I suggest checking out Airtable: https://zapier.com/apps/airtable/integrations#triggers-and-actions

Userlevel 7
Badge +11

Hi @dogoodthings!

It sounds like you may be running into a bug that we reported 6 hours ago, where "New Row" and "New or Updated Row" triggers are delayed, and possibly not receiving hooks. I’ve added you as an affected user, so when it’s resolved you’ll be notified via email.

If this is not super recent for you then I offer you the following as well…

Generally speaking, it can be tricky to get Google Sheets working 100% how you want/expect it to with Zapier. A few things to consider:

  • When we say “seeing that same value in the row before” it’s based on the row number (like where it is in the worksheet) and not the exact row itself.
  • That means that if you had 5 rows and we’d seen YES in the Is Subscribed column of the 3rd row, if you were to insert 3 rows between the current row 2 and 3 that means that the 1st of your inserted rows is now the 3rd row. So having YES in the Is Subscribed column would technically have been seen there before (even though in the NEW version of the row, it had not existed prior.
  • Google Sheets doesn’t have a “row ID” that follows the row, unlike Airtable (that Troy mentioned) that does have a Record ID that uniquely identifies the record.

All of this means that if you insert rows into the middle of a worksheet, delete rows or move them around that’s going to affect things. So even though you know that in the row for Nick Simard you definitely never had YES in the Is Subscribed column, if a row in that position ever did, then it’s not considered updated.

Whew! Confusing, I know. Hopefully some of this helps!

Userlevel 1

Thank you for the replies.

Yes, I had checked that help page (plus two more related to Sheets issues) before posting.

I got the live version to trigger in some cases, but not all of the cases where I wanted to, and it took much more than “a few minutes”. It’s hard to say when exactly because I tried multiple times, but it could be anywhere between 15 minutes and 3 hours. Basically the next day I noticed that some of the zaps had finally gone through.

When testing, I tried to isolate the issue as much as possible, and I put values into the trigger column that I hadn’t used anywhere else in the spreadsheet before (basically an integer that I kept increasing every time I made a change).

----

I now have a working setup that avoids the “New or updated spreadsheet row” trigger and uses the following setup instead: Webhook with a “first_row” parameter → Google Sheets: Get many spreadsheet rows (get 20 rows, starting at “first_row”)  → JavaScript converts the object into multiple rows → previous workflow. This requires manual triggering, but that’s fine with me for this use case.

Here’s the JavaScript:

// this is wrapped in an `async` function
// you can use await throughout the function

output = [];

for (const row of JSON.parse(inputData.rows)) {
const formattedRow = row
.map((val, idx) => ([convertToNumberingScheme(idx + 1), val]))
.reduce((prev, curr) => {
const [key, val] = curr
prev[key] = val
return prev
}, {})
output.push(formattedRow)
}





/////////////////////////////////////////////////

function convertToNumberingScheme(number) {
var baseChar = ("A").charCodeAt(0),
letters = "";

do {
number -= 1;
letters = String.fromCharCode(baseChar + (number % 26)) + letters;
number = (number / 26) >> 0; // quick `floor`
} while(number > 0);

return letters;
}