If you have two Google Sheets and you'd like to keep them in sync, as in any row added to the first Google Sheet gets automatically added to the second Google Sheet, this guide will take you through it step by step.
This is useful if you'd like to backup a Google Sheet to another one or to separate operations on the same data in two different sheets or for two different teams.
Copy rows from a Google Sheet to another one
There are several approaches to keep two Google Sheets in sync, our approach in this guide is to detect new rows in the first sheet and automatically add them to the new one. The sheets don't have to be in the same Google Drive account but you need to have access to both of them.
Prerequisites
In order to keep your Google Sheets in sync, you'll need the following tools:
- An Activepieces account. We'll build this automation job with Activepieces.
- 2 Google Sheets. One to read from and one to write to. They can be in separate accounts but you need to have permissions to both accounts.
Steps to keep 2 Google Sheets in sync
Follow these steps to automate the process of copying 2 Google Sheets:
1. Create an Activepieces account
We'll build this syncing job in our Activepieces account. If you don't have an account, go ahead and create one to be able to follow the next steps.
2. Create a new automation flow
In your Activepieces account, click Start building if this is your first automation, or New flow if you have other flows. Icalled this automation "Sync my sheets 🔁📊" but you can give it any name of your liking! This is my fresh automation:
3. Set up the Google Sheet trigger
Our first step is to set up our automation to listen to any new row from our Google Sheet and run the flow accordingly. This listener is called Trigger. So we'll click on Select Trigger, find the Google Sheets app and the New Row trigger.
Now connect your Google Drive account using the +New Connection button. Once connected, choose your sheet and sub-sheet from the dropdowns. I did it like this:
Before starting with this automation, my two sheets had the same data and they both had 3 pokemons data like this:
This is from my first sheet, but the second one looked identical in the initial state.
4. Load sample data from the first sheet
Now under Generate Sample Data in the sidebar, click on Load Data to bring some sample data from our sheet. This will help us use the data from the first sheet in the next step. When Iclicked on it, Isaw this:
5. Add a step to insert a new row in the second Google Sheet
Now click on the +right below the trigger step, find the Google Sheets app and select the Insert Row action. If the second sheet is in the same account as the first sheet, you can choose your connection from the dropdown, otherwise, click on +New Connection to connect the second Google Drive account.
Now choose your second sheet and its sub-sheet from the dropdown.
In the Values field, we will add as many values (columns)as we'd like to sync from the first sheet to the second one. In our case, we have two columns:Pokemon Name and Type, this means we'll need to values in this field.
The first value will be the Pokemon Name, we'll choose it from the dropdown when we click inside the input. Navigate to New Row → value →value 0 and click on it. Notice that the value field will have a variable tag added to it.
This is how Ichose the first value:
Add another value, and do the same thing but select value 1 instead of value 0 now. In case you have a sheet with 5 columns, you'll do this 5 times instead of 2.
6. Test the Google Sheets sync flow
You're done with the sync flow design, it's time to test! Click Test flow in the right corner of the page. You will have a row added in the second sheet, if you see it, the flow ran successfully. You can delete this row to keep your sheet clean.
7. Activate your Google Sheets data copying
Now click the main Publish button, you will see a toggle turned on. This means your flow is activated in the background. Let's add 5 more pokemons to our first sheet now and see if they sync.
I added these to my first sheet:
Note that the Google Sheet New Row trigger fires every 5 minutes and not instantly. That means it runs at multiples of 5 minutes, if your time is 13:41 you'll have to wait until 13:45 for the next run.
When the flow ran, my second sheet updated and Isaw these results in it:
Congrats 🎉 you now have a bot that copies data from a Google Sheet to another one while you're sleeping!
