Question

My Zap randomly duplicated data on slave sheets after deleting a row.

  • 11 August 2023
  • 1 reply
  • 26 views

Userlevel 1

I have a Zap that is rather complicated but is working with a random glitch that I cannot track down.

  1. Gravity Forms entry to sheet entry. This is the master sheet for the next steps. Never have issues here.
  2. When a row of data is approved (via timestamp) in the master sheet it triggers a zap that looks up the row that was approved. This value is then used to update the corresponding rows on two other (slave) sheets.
  3. Problem I am randomly having is that it will delete the appropriate row on the slave sheets but then will occasionally duplicate the data immediately below. Thus, the updated data becomes totally missing from the slave sheets as it is never written to them. Sometimes a row will just disappear. Throws the sheet into disarray on succeeding approvals if duplication isn’t immediately caught and rectified. IOW, this problem can become compounding since everything relies on the same number of rows AND sort order.

Some more info.

  1. All three sheets have the same number of rows and the rows are in the same sort order on all of them.
  2. Why delete then update the row? Unless it has changed, the Update Row method will not blank a cell on update. Thus, if a value is removed at the master sheet it will not be removed on the slave sheets without first deleting the row and then updating it with the new data. Insert Row will not work here AFAIK since it will just add the row to the bottom of the sheet, throwing the sort order off.

So these are my steps:

  1. Google Sheets logo New or Updated Spreadsheet Row in Google Sheets (TRIGGER: Newly approved row in Master Sheet)
  2. Filter by Zapier logoOnly continue if… (Approved cell exists - this cell gets reset anytime an updated Gravity Forms entry is submitted)
  3. Google Sheets logo Lookup Row to Delete and Update on Slave Sheets: Looks up which row was updated in the Master Sheet
  4. Google Sheets logo Delete Outdated Row From Slave Sheet 1: Uses Lookup Row number from step 3
  5. Google Sheets logo Delete Outdated Row from Slave Sheet 2: Uses Lookup Row number from step 3
  6. Delay by Zapier logo Delay After Queue in Delay by Zapier: 1 minute delay: Added in an attempt to prevent issue above.
  7. Google Sheets logoUpdate Deleted Row on Slave Sheet 1: Uses Lookup Row number from step 3
  8. Google Sheets logoUpdate Deleted Row on Slave Sheet 2: Uses Lookup Row number from step 3

Any insight would be appreciated.


1 reply

Userlevel 7
Badge +11

Hey there @Joshua J! 👋

It sounds like you’d ideally like the Zap to overwrite an existing value in a cell to be blank when the value for it supplied by the trigger is blank. Am I understanding that correctly? 

If so, rather than using a Delete Spreadsheet Row action, there is a trick you could try that involves using an IF formula to set the value for the cell in a Update Spreadsheet Row action:  =IF(LEN("{{value}}"),"{{value}}"," ")

In that formula you’d replace the {{value}} tags with the field from the trigger step containing the value you’d like to update the cell with. For example:
7674d37c73fc6ec5c0950dcea8dbd411.png

That should ensure that the relevant cell in Sheet 1/Sheet 2 will appear as a blank cell when the field is empty. And if the selected trigger field isn’t empty, then it will update the cell to match the value in the selected field.

Hopefully that helps to get around the issue caused by using Delete Spreadsheet Row actions in the Zap. If I’ve misunderstood what you’re looking to do here or you run into any issues with that formula, just let me know! 

Reply