How to connect two Google Sheets two-way sync

Written by
Laura Tennyson
Google Sheets offers a simple and effective way to manage data and collaborate with colleagues, enabling you to share and access information in real-time. Email attachments and sharing outdated files is a thing of the past thanks to Google’s cloud-based spreadsheet tool – Google Sheets. Now, it’s easy to share your spreadsheets directly with collaborators, where everyone has the latest data in one place. But, can we take this to another level? Can we learn how to connect two Google Sheets within the tool itself?

By connecting two Google Sheets together, you can sync data from one spreadsheet to another automatically, creating a database-type system with no coding required. Let’s explore in more detail the benefits of a two-way sync system in Google Sheets, and how you can create your own using the Sheetgo add-on.

What is a two-way sync?

A two-way sync allows you to transfer data freely between two spreadsheets. You can send data (or a selected amount of data) from spreadsheet A into spreadsheet B, and vice versa. The data still remains in its original source file too.

What’s more, when you add new data or update existing data in one of your spreadsheets, the two-way sync will automatically update the same data in the second spreadsheet. This means all of your files will remain up to date.

A two-way sync between Google Sheets is perfect for collaborative projects, whether that be for reporting, powering dashboards, or sharing insights. Let’s say I’m a project manager who needs to send tasks to team members, whilst also receiving status updates and links to the final projects. I can use a two-way sync system in Google Sheets to share data between my master spreadsheet and their individual project sheets. Whenever one spreadsheet is updated, the connected spreadsheet will also be updated.

Why connect two Google Sheets with Sheetgo?

By connecting Google Sheets with Sheetgo, you can automatically create a two-way syncing of spreadsheets and avoid the following complexities:

  • No copy and pasting: Avoid manual copy and pasting and the risk of human error. Sheetgo creates an automated workflow that connects your spreadsheets together and updates them for you.
  • No coding: Achieve a database-style system without the difficult coding. The only low coding involved is if you choose to add formulas to your spreadsheets!
  • No IMPORTRANGE: IMPORTRANGE has been used to connect Google Sheets in the past, but this is a complex, often unreliable formula that doesn’t offer a scalable solution.

By connecting sheets with Sheetgo, you can control your data pipeline, allowing staff to share information whilst simultaneously ensuring data protection. If you share data with external stakeholders, access control is crucial. A workflow of connected sheets allows you to share specific data from your live spreadsheets with clients or customers, without giving them direct access to internal files.

Now that we understand the benefits of connecting two Google Sheets with Sheetgo, let’s explore exactly how to do this using the Sheetgo add-on.

How to connect two Google Sheets

Let’s use the example above to demonstrate how to connect Google Sheets in a two-way sync using the Sheetgo add on.

I’m a project manager who needs to send tasks to individual members of my team, whilst receiving progress updates from them at the same time.

Here is my current spreadsheet containing all of the project tasks.

How to connect two Google Sheets two way sync 0

Let’s start with my team member Mark. I want to send all of his tasks to a separate spreadsheet dedicated to his work, that only he can access. Once I have successfully transferred his data, I want to add two more columns where he can track his progress and write notes, which I can then connect back to my master spreadsheet.

As a result, we will both be able to view each other’s data from our own spreadsheets in real-time.

1. Install and open the Sheetgo add-on

Start by installing the Sheetgo add-on into your Google Sheets by clicking on the button below.

Sheetgo add-on

Alternatively, you can install the add-on by opening your Google Sheets and heading to Extensions > Add-ons > Get add-ons. In the pop-up, search for Sheetgo, click on the result and press the Install button.

Make sure you have the Google Sheet containing your data open. To access the Sheetgo add-on, head to Extensions > Sheetgo > Start. The add-on will appear on the right-hand side of your screen.

How to connect two Google Sheets two way sync  1

2. Choose file to export

In the add-on, under the Export data section, click Select data.

How to connect two Google Sheets two way sync  2

As you’re already in the file you want to export, simply choose the correct tab that contains the data you wish to export.

Once finished, click Continue.

How to connect two Google Sheets two way sync 3

3. Filter your data

Now it’s time to choose the exact data you want to export. In my example, I want to export the task data that belongs to Mark.

Under the Filter data section, select Filter by condition.

How to connect two Google Sheets two way sync 4

As I only want to split my data based on one factor (task owner name), change the condition type to Data matches one condition (OR).

Now it’s time to choose my condition. As I want to select data based on the task owner, I need to select Column G. Then, in order to export only Mark’s tasks, I need to change the Criteria to Text is exactly and write the Value as ‘Mark’.

Once finished, click Continue.

How to connect two Google Sheets two way sync 5

4. Choose destination file

Now it’s time to choose where this selected data will go. First, choose whether you want to export your data to a new or existing file. In this example, I want to create a new file for Mark. Next, you can change the name of your new file and tab to suit your objectives.

In order to allow Mark to edit his new spreadsheet with his progress updates. I must alter the spreadsheet settings. In order to do this, expand the settings section directly underneath the previous fields.

How to connect two Google Sheets two way sync 6

Now deselect the button under the Lock destination tab title. This will allow the user to alter the spreadsheet without Sheetgo overwriting your edits in the next update.

Once finished, click Finish and save.

How to connect two Google Sheets two way sync 7

5. Open and edit new Google Sheets file

Now open your new file. It should only contain the filtered data you selected from your source file. You can make edits by adding new columns that you wish to be filled out within this new file.

As I want Mark to provide updates on his progress with the tasks, I’m going to add two more columns – ‘Progress (hours)’ and ‘Notes’. Here, Mark can keep track of each task’s progress, and add any important notes he has along the way.

How to connect two Google Sheets two way sync 8

6. Connect new file back to source file

In order for me to receive these new updates in the new columns, I need to import the new spreadsheet back into my master sheet.

Head back to your master sheet. In the add-on, click Add connection.

How to connect two Google Sheets two way sync 9

Under the Import data section click Select data.

How to connect two Google Sheets two way sync 10

Search for the new file you created. For this example, I need to select Mark’s Google Sheets file and select the tab where the data is stored.

How to connect two Google Sheets two way sync 11

Now, under the Destination section, you can name the new tab where this data will be imported to. I will name my tab ‘Mark progress’ so that I know that this tab will only include Mark’s task progress.

Once completed, click Finish and save.

How to connect two Google Sheets two way sync 12

7. Schedule automatic updates

In order to make sure that you receive the latest updates from your two-way sync system, you can schedule automatic updates.

In the add-on, click Automate.

How to connect two Google Sheets two way sync 13

You can edit the automation schedule by days and hours. Once you’re happy with your schedule, click Save.

How to connect two Google Sheets two way sync 14

8. Share new files with your team members

Now that your Google Sheets are connected, you can share the individual team member files with its corresponding users.

For example, now I can share Mark’s personal spreadsheet with him. Now, whenever he adds new data, it will appear in my master sheet.

How to connect two Google Sheets two way sync 15

Can you sync two Google Sheets?

And there you have it! With Sheetgo, not only can you connect two Google Sheets together, but you can create an effective two-way sync system without the need for complex coding or IMPORTRANGE.

If you would like to find out more on how to use the Sheetgo add-on to successfully split data, check out our blog post on How to split Google Sheets into multiple sheets.

Alternatively, take a look at some related blog posts below!

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

You may also like…