How do you get started with automation? How do you build your first automation?
I believe automations can help you accomplish more of what you want to do and avoid what you don’t want to do, but getting started can be daunting.
There are so many new tools to learn: Zapier, Make, Airtable, and so many more.
But even more than that, creating automations takes a different way of thinking that doesn’t always come naturally. So, to help ease you into it, I’m creating this new series called “Your First Automation” to help you learn the basics of automating everything. These guides should help ease you into automations, and also help show you a few tricks that you can use to create your own automations later.
The first automation we’re going to tackle in this series is one of the most basic, but also weirdly useful tricks: creating a duplicate of a spreadsheet.
Why You Might Want to Duplicate This Spreadsheet with an Automation
Creating a duplicate is easy enough that you don’t really need an automation. Just copy and paste! Or if you have to make it complicated, right click the sheet and then click “Copy to a new sheet.” Easy!
But there are times when you might want to duplicate a spreadsheet using an automation, for example:
- To avoid copying duplicates
- To copy contacts into something that doesn’t allow imports, like a Zoom meeting
- To copy only a certain type of item
- To copy certain rows only at a certain time or under certain conditions
But even more than that, most automations are really just about moving data and combining it in different places at right time. Automations can seem complicated, but most of them are just fancy copy/paste.
Automations can seem complicated, but most of them are just fancy copy/paste.Click to shareExperienced with automations? Give me some more examples of situations where you copy a spreadsheet in the comments!
So, let’s figure out the basics today by learning to duplicate a spreadsheet!
3 Steps to Duplicate a Spreadsheet for Your First Automation
This is a pretty simple process, but you might need to create a free Google account to use Google Sheets and a free Zapier account, if you don’t have them already. Once you’ve done that, you can move on to step 1.
Step 1: Create the Google Sheet
Start by creating a Google sheet with a few columns and fill it out with some sample data. You can also use the template we’ve made here:
Template for Your First Automation: Duplicate Spreadsheet Contents to Another Sheet »
Just click “File > Make a copy” to create your own version of this template.
Why Google Sheets? We tend to prefer Google sheets for simple automations just because it connects to everything easily, although there are other spreadsheet software that work for automations as well (check our full best tools for automations list here).
As you can see from our template, we’ve got a very simple spreadsheet with just 6 columns. The actual columns aren’t that important, except you SHOULD use a header for each.
The only thing that is important is to have a “Copy?” column, which will act as our trigger column for the automation. Just leave it blank for now!
Step 2: Create Your Zap on Zapier
Next, we need to create our automation that will do the actually duplicating. We’ll be using Zapier for this, so head over there and create a new zap. You can also use our template here (although, it’s not the worst idea to set this up just so you can get familiar with it):
Zap Template for Your First Automation: Duplicate One Spreadsheet to Another »
Why Zapier? Zapier is the main tool we use for automations (although Make.com is cool too!). It’s amazing at integrating thousands of tools together, or just one, in this case Google Sheets!
Here’s what it looks like when you first create your zap.
Easy enough, right? Now, on to the next step!
Step 3: Create Your Trigger Event
All zaps have a trigger, the thing that starts the Zap, and one or more actions, the action that you’re doing, such as making a duplicate of a spreadsheet!
For our purposes, here’s what the trigger is going to be:
Trigger app: Google Sheets
Event: New or Updated Spreadsheet Row
Account: Whatever Google account your spreadsheet is on
Spreadsheet: The spreadsheet you created in step 1
Worksheet: Sheet1
Trigger Column: “Copy?” column
Take special note of the trigger column value at the end. We want the zap to watch that column ONLY, because when it updates, that’s when it will trigger the next step to duplicate the value in the row.
Once you’ve created the trigger step, test it. You should get the last row in your spreadsheet, which for us is row 11.
Step 4: Create Your Action Event
Now that we’ve created a trigger, we’re nearly done! Next, it’s time to create the action step.
Here’s our action step configuration:
Action step app: Google Sheets
Event: Create Spreadsheet Row
Account: Whatever Google account your spreadsheet is on
Spreadsheet: The same spreadsheet you created in step 1
Worksheet: Sheet2 (make sure a second worksheet exists on the spreadsheet you’re copying)
Once you select the worksheet, a new set of fields will open up in the zap that mirror the header columns you already have set up in your spreadsheet.
This is how you map the content you’re copying from one spreadsheet to the other. For each of these fields, you can select any data that’s been used previously in the Zap.
Here’s how we’re going to configure it:
For the “Date Copied” field, we’re going to use a zapier specific variable, {{zap_meta_human_now}}, which will display the timestamp when the Zap ran. (P.S. I always forget this variable, and Google it pretty much every time, so don’t worry if you forget it!) By displaying the date the Zap ran, we’ll know that it ran successfully.
Once you’ve done that, go ahead and test the action. You should see your brand new row in Sheet2!
Optional Step: Log the Duplication in Sheet1
Because I’ve been doing this for a long time, I like to know when Zapier has completed the thing I told it to do (you’d be surprised at how often it doesn’t, almost always because I’ve messed something up!).
So I like to add one final step to update the original record I duplicated with a timestamp of when it was completed. Here’s what it’ll look like.
Important: for the Row make sure you use the Row ID from step 1!
Then just pop our timestamp variable, {{zap_meta_human_now}}, in the Date Copied field to log when it was completed.
Once you hit test, you should see the current date and time pop up in your row on Sheet1.
And after that, your Zap is complete and you can publish it!
Step 5: Run the Zap by Updating the Trigger Column
Now it’s time to run our automation!
To do this, just put any character in the trigger column (personally, I like to use either a Y for “Yes” or a √ with the shortcut option + V on Mac). Give it a minute or so for Zapier to notice, and it will trigger the zap and run the automation.
Don’t forget to check Sheet2 to see your updated results!
You did it! You created your first automation and duplicated a spreadsheet.
Keep in mind, you could have moved this data anywhere, not just to a second worksheet on the same spreadsheet, so while it might not seem like you’ve done much, this is actually kind of a big deal!
Now, what if we took it just a step further. What if we only wanted to duplicate some of the rows, say only the rows of a certain type? Let’s do one tweak to our automation and take it to the next level.
Next Steps: Duplicate a Spreadsheet Row Based on a Condition
To take this automation to the next level, let’s only duplicate the rows of a certain type, namely the even rows.
Here’s a template so you can see how this works, but again it might be good to do this manually so you can see the process:
Zap Template for Your First Automation: Duplicate Spreadsheet Conditionally »
To duplicate conditionally, we just need to add a filter step. Here’s how:
- Edit your Zap
- Click the + sign between Step 1 and Step 2
- Select the “Filter” action
- Here’s the configuration we’re going to use ({{Type}} exactly matches “even”):
That’s it! Now it will only duplicate the rows labelled “even.”
Let’s just change one more thing, changing our next step, which duplicates the row from Sheet2 to Sheet3 so we can see the difference.
You’ll also want to go back to Google Sheets and clear the “Copy?” trigger columns so we have a fresh start.
Now just turn on your Zap and try it out by filling in the trigger column with any character. Give Zapier a couple of minutes to update, and it should have copied over your results to Sheet3! (But only the even ones!)
Pretty cool, right?!
You Can Create Your First Automation!
You can make your life easier with automations! You might just need a little practice.
I hope that this automation is simple and straightforward enough that anyone can do it, so give it a try and let me know how it goes.
And if you run into trouble (because there are a million ways to run into trouble with automations), let us know in the comments and we’ll try to help. This is a community, after all.
How did it go with this automation? Was it your first? Let us know in the comments!