Google Sheets is an AWESOME app, that I use loads. However those of you who use it a lot may have experienced your zaps breaking after someone (maybe a team member or customer) edits the sheet in certain ways.
Here is what I do when I set up Google Sheets for customers:
I always create a Worksheet at the very beginning titled "Instructions":
This is the standard text I use:
This may seem a bit draconian, but unfortunately I have run into issues with these with so many different permutations that it's difficult to narrow them down. One of the permutations, for example, related to whether the connected Google account was a standard account or part of G Suite. The devs in charge of the G Sheets zap are clearly trying to limit these issues. I have noticed a shift lately from "named" sheets to ID'd sheets, which I'm sure is part of the process. However, when I am working with clients - prevention is better than cure.
I make sure to tell clients that it is totally possible to make all of the above changes but they must just let me know first. All that is usually required is to switch the Zap Off, and remap the fields (don't forget to "Refresh Fields" after pulling in new data) and you're good to go.
Tip: I often set up extra columns in between the required columns (title them "Spare Column 1, Spare Column 2, etc) and then hide them. They can come in handy later when you need to add new fields that are better placed in-between than at the very right. Hiding Rows, like dummy info for tests is sometimes handy too.
Also be sure to use Conditional Formatting and permissions on your worksheets. Here is the message that pops up for one of the settings:
Despite these niggles Google Sheets continues to top Zapier's most popular apps list, and probably has the most powerful and flexible set of integrations of any app. I often use Google Sheets as an intermediate step when I am setting up a complex zap, as the data is so easy to visualise, and is great for testing.
@ChrisP - a fantastic guide and a great, simple way to educate clients - thanks! 😀
I've never understood why re-ordering or renaming worksheets causes an issue. Unlike columns, the worksheets are referenced by ID, so it shouldn't be an issue.
@ChrisP! I will admit to being more inclined to just lock things down for clients rather than providing them instructions 🤣, but I really like this idea. Thanks for sharing!
I locked permissions on other columns, but even with this single column editable, a lot of accidental damage can be done.
I typically use a filter step whenever doing anything triggered off of, or even just searching a Google Sheet. The filter step will check that the input was intended, or is not a duplicate.
To do this, each pass of the automation will update the row with a status/stage, sometimes in the same column, sometime in unique columns. So I may have a column that is 'Run', and the first filter is 'Run' does not contain 'Yes'. Once it runs, it updates that column to 'Yes'. Next, it needs to update it or something of the like, so it filters on a column that says 'Update' which is blank, proves that it's blank, and once the thing is updated, updates that column to 'Yes'.
I'll do the same thing if I have a trigger of New or Updated Row, where it's triggered only off of a specific column, usually called 'Run', that HAS to contain the word 'YES', or something like that.
Also, for anything that I do, whether it be in Google Sheets, or in another service wherever possible, I will use the 'Find (or create if not found)' rather than the 'Create' option. This way, if it finds an existing record, it stops, but if it doesn't, it creates a new instance as previously intended. This ensures that duplicates can never happen.
Tying these two things together, filters and always using 'Find (and create if none found)', ensures that Zaps only ever run when you really really want them to, and can never ever create duplicates.
Plus when working with clients, I always hide and lock the logic/trigger sheets, so that they can input/edit/view data, but never have to worry about breaking things.
You may already know this, but another cool thing about Find (or Create) steps is that in the output for the step they have a 'Zap data was found' field, which can be True (if an item was was found) or False (if it was created). I'll be posting a tip about how to use this with Google Sheets to create your own deduplication record soon!