Question

Sorting Google Sheets data breaks my zap. Best practices?

  • 30 March 2023
  • 17 replies
  • 378 views

Userlevel 1

My zap is pretty simple. I input customer booking data like date, time, and email addresses into a Google Sheet, and it automates creating a Google Calendar event based on this data. The trigger is a new row in the Google Sheet. 

Recently I saw that the zap had stopped working, and figured out that its probably because I had sorted the data without turning the zap off. 

Customers don’t book in a neat order, so the rows on the sheet can be in an order like May, August, April. I want to sort the data in the sheet by date, so I can see the bookings chronologically as April, May, August. 

I just want to make sure I don’t break it again if I do the below

  1. turn off zap
  2. sort the Google sheet data by date (April, May, August)
  3. turn zap back on 

Or do I always have to revert back to the way the google sheet was prior to sort? 

  1. turn off zap
  2. sort the Google sheet data by date (April, May, August)
  3. undo sort by date (May, August, April)
  4. turn zap back on

thanks in advance for any help!


17 replies

Userlevel 7
Badge +14

Hi @Kmedia 

Good question.

I’d recommend using Airtable instead of GSheets.

Airtable has Views.

View can be sorted.

Userlevel 1

Hi @Troy Tessalone 

Thanks for the Airtable suggestion. Just watched a couple videos on it and now have a very rough idea of why it may be better than Google Sheets for my use case. 

It seems like I can just put the data into a spreadsheet just like Google Sheets, but would be able to sort the data in different contexts, called Views (like chronological order) without breaking the zap to Google Calendar? 

Am I understanding that correct? Thanks in advance! 

 

 

 

Userlevel 7
Badge +14

@Kmedia 

Yes, Airtable has certain advantages for use in integrations/automations.

Airtable also has Automations.

Airtable has integrations with GCal.

 

https://zapier.com/apps/airtable/integrations#triggers-and-actions

 

 

Userlevel 1

@Troy Tessalone 

So  if I use airtable then no need for zapier?   I’m def going to look into it. 

For now though, I have several workflows tied together via google sheets/forms/calendar so I need to fix my zap. 

Do you happen to have any insight on my original question regarding how to sort a google sheet without breaking the zap? 

Userlevel 7
Badge +14

@Kmedia

The Google Sheets trigger on my Zap stopped working

If your Google Sheets trigger stops working suddenly, first check if you’ve accidentally inserted a blank row anywhere in the spreadsheet. Zapier interprets a blank row as the end of the spreadsheet and may have trouble finding any new rows below it.

In addition to this, making the following changes to your spreadsheet while the Zap is turned on can cause errors:

  • Deleting existing rows or columns.
  • Adding rows anywhere other than to the bottom of your sheet.
  • Resorting the sheet.
  • Renaming/adding/rearranging columns.
  • Renaming the sheet.
  • Filtering the sheet.

If you need to make any of those changes to your Google Sheet, turn your Zap off while you work on the spreadsheet, and then turn it back on again.

 

Userlevel 1

@Troy Tessalone thanks for that. I had seen this tutorial before, and was referencing this in my initial question. What I’m understanding now is that zapier gets confused when you sort or filter, since it’s looking for a new row at the bottom of the spreadsheet. 

I did find out about the “filter views" iin Google Sheets and was wondering if this would let me sort and filter data without it impacting my zap. It blacks out the borders for a visual cue, and lets users sort and filter data without altering the original order of the spreadsheet.

The use case would be working with a colleague on a single spreadsheet, filtering the data, but not affecting the original so your colleague doesn’t open up a filtered data set. 

Do you happen to know if filter views can be applied to google sheets so that users can sort data without breaking their zap? 

Userlevel 7
Badge +9

Hey there, @Kmedia! Appreciate you reaching out!

I know it sounds tedious but as Troy mentioned the best advice we have for when you need to filter views is turning off the zap while doing this and then turning it back on when finished. 😔

Also since you expressed some interest in Airtable so I also wanted to share this article my teammate Chanelle wrote up on Airtable vs Google Sheets.

I hope some of this helps! 🧡

Userlevel 1

@christina.d all this helps very much thanks! I fully understand turning off the zap before doing any filtering activity is the best option.

My original question was  - do I have to return the sheet to the state it was prior to turning off the zap to not break it? 

If I have rows  in the order of A B C D , turn off the zap, sort the data so now the rows are in order D B C A, then turn the zap back on will this be a problem? 

If it would be a problem I was contemplating using the filter views function in addition to pausing the zap. Original order of the spreadsheet isn't changed so I figure it would not be a problem. 

 

Userlevel 7
Badge +14

@Kmedia

You can leave the data sorted.

But if you have other Zaps dependent on the sort order of the data, then those Zaps may be impacted.

When in doubt, test it out.

 

The Google Sheets trigger on my Zap stopped working

If your Google Sheets trigger stops working suddenly, first check if you’ve accidentally inserted a blank row anywhere in the spreadsheet. Zapier interprets a blank row as the end of the spreadsheet and may have trouble finding any new rows below it.

In addition to this, making the following changes to your spreadsheet while the Zap is turned on can cause errors:

  • Deleting existing rows or columns.
  • Adding rows anywhere other than to the bottom of your sheet.
  • Resorting the sheet.
  • Renaming/adding/rearranging columns.
  • Renaming the sheet.
  • Filtering the sheet.

If you need to make any of those changes to your Google Sheet, turn your Zap off while you work on the spreadsheet, and then turn it back on again.

Userlevel 1

@Troy Tessalone Noted. I think I’m just going to take the added step of just duplicating my zap sheet to another new one to sort as I wish with no worries. Thanks for all the help. 

Userlevel 7
Badge +9

Woof - sorry for missing that, @Kmedia! Glad Troy was able to get ya back on track. Appreciate you sharing your end solution here as well. 🙂

Userlevel 1

Hi,

I had same issue and support said for me to turn off and then back on. Never said if when I turn back on zaps that didnt happen when off, would happen when I turn on again. In addition, airtable is not for me, also I would need to learn another tool, plus it has a paid plan, so I am not sure when would I need paying for it. 

 

I dont understand why people dont give the solution I will give here.

 

This is the solution I did for myself. I created a new tab and all data on the tab that is being sync with Zapier stays untouched, while I copy to a new tab using formulas everything from my main tab. 

 

Tab A = receive data integration from Zapier 

Tab B = copy of Tab A and I can do everything I want. Filter, delete etc.. 

 

Or, If you use a form to collect data to send to sheets (booking form). You can have the form send to two different google sheets. Use one sheet for integration only and the second one to sort and do as you please. 


Hope it helps! 

Userlevel 7
Badge +12

Hi @dannyg!

Thanks for sharing how you workaround the issues with re-sorting/making changes to a Google Sheet while you’re working with Zaps. 

Everyone’s use case is a little different, so a solution that works for one person won't be good for someone else. It’s great to have options, so thanks for sharing yours!

Userlevel 1

Thanks Danvers, but my solution is exactly what he asked. He wants to sort and play with his sheet without creating problems with his Zap. I complained, because I also faced this issue and like many other users. Zapier keeps suggesting airtable and if you look at all the replies generated from a clear question made is enormous. I also been in his place where support replied 5 times and never once, said to sync sheet with another empty sheet, or duplicate sheet so you can leave one alone and use other. I am starting to think that Zapier has a deal with Airtable or something. Sorry for all this, but is true. Why all these replies for a normal and obvious question. Also, how come Zapier only directs people to Airtable where you can have the solution inside Google Sheets? 

 

Userlevel 7
Badge +12

@dannyg It must be frustrating to have people keep offering another app when you want to use Google Sheets. I can say that Zapier has no connections to Airtable. It’s a solution we commonly recommend because it’s more robust when it comes to creating more of a database-type sheet that you want to sort, filter etc. From a completely personal perspective (my opinion does not reflect Zapier’s position), I only use Google Sheets for storing information if I need something quick or will need lots of formulas if I’m creating a list of items that need to be used/sorted, etc. I will use Airtable. 

 

That said, I do understand your frustration. I personally don’t tend to recommend adding a whole new sheet because it’s something that needs to be kept updated to match the existing sheet, which brings its own challenges. Thanks for sharing your thoughts on this and I’ll definitely bear in mind that for some users, a synced sheet could be a better option. 

 

Thanks!

Userlevel 1

Thanks, you will notice that also for Kmedia his solution is duplicating sheets. The sheet is always synced with the original, because you can use importrange function and add the URL to whatever sheet you like. This will sync data between sheets. I understand your point as well, but I believe Airtable is a second solution. 

Userlevel 7
Badge +11

Thanks for following up here, @dannyg

I agree, it’s not always necessary to switch to a different app. Glad to hear you’re using Google Sheet’s IMPORTRANGE function here, I’m a big fan of it! For cases like this I’ll often suggest using that IMPORTRANGE function. As it’s great for pulling in all the information from the worksheet that’s connected to the Zap into a different worksheet where you can do things like filter, sort, group etc. without needed to turn the connected Zap off/on every time you want to make a change. And often quicker than having to move everything over to (and learn how to use) a new app. 

Anyway, it sounds like you and @Kmedia are all set here for now. 😁

Please do reach out in Community again if we can assist with anything else in the future. In the meantime, happy Zapping! ⚡

Reply