How to connect two Google Sheets with a two-way sync

Get a selection of expert articles

Google Sheets offers a simple way to manage data and collaborate. Still, it also has limited data syncing functionality and that makes it important to learn how to connect two Google Sheets. In this article we will go over how to do this using Sheetgo.

This means linking separate files and moving data between them in a dynamic, automated workflow. Additionally, you can build a code-free database by learning how to connect two Google Sheets two-way sync.

Make your data work for you

Ready to sync your Google Sheets?

How to connect Google Sheets and create a two way sync [AUTOMATED]

Why connect two Google Sheets?

The main reason to link two Google Sheets is to move data back and forth. Moreover, by creating a two-way connection, you can send data in both directions. When a change occurs in spreadsheet A, the data automatically transfers itself to spreadsheet B and vice versa.

Connected spreadsheets have several advantages:

  • They decrease manual work
  • They enable the creation of streamlined workflows that group data in separate “buckets”.
  • They provide more reliable data; having fewer hands on a spreadsheet means less errors.

It might seem practical to create one central spreadsheet that everyone can access. But this comes with problems: big spreadsheets are slow, unreliable and even a security risk if they contain sensitive information.

By connecting two Google Sheets you can control your data pipeline, allowing staff to share information while 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.

What does it mean to two-way sync Google Sheets?

A two-way sync is a bidirectional data transfer between two systems. This continuous transfer ensures that changes made in one are reflected in the other. It maintains consistent and up-to-date information across both platforms.

When you connect two Google Sheets, you can send data from a source to a destination tab in a spreadsheet of your choice. The selected data from spreadsheet A appears in spreadsheet B automatically, leaving spreadsheet A intact. 

A two-way sync is ideal for reporting, sharing information or powering data dashboards. It even allows you to share just one tab of a google spreadsheet, while keeping the rest private.

Sometimes you may want to create a connection in the opposite direction, pulling data from spreadsheet B back to spreadsheet A. This functions like a two-way sync: when data changes in one tab, it is updated in the other.

A two-way sync is useful for team coordination. For example, a project manager who needs to send tasks to team members while receiving status updates. Both the manager and the team members need information from one another.

Connect, share and collaborate

How to connect two Google Sheets?

In this example we have a project manager and five team members. The team members work separately, but they need to synchronize their tasks.

Using Sheetgo, we will create two-way connections between a project management master file and five individual files (one per team member).

Data from the “Project management” spreadsheet is sent to the “WBS” (work breakdown structure) tab in the spreadsheet belonging to each team member.

When the team (“Patrick”, “Ashley”, “Emanuel”, “Stephanie”, and “Joe”) update their progress, data flows back into the “Project management” spreadsheet.

Follow these steps to get set up — just add as many team members or spreadsheets as you need.

two-way sync

Step 1: Install Sheetgo

Install Sheetgo by clicking the blue button below. Log in to the web app to create a two-way sync between your Google Sheets.

Step 2: Create a split connection to distribute tasks

The first step is to create a split connection from your existing “Project management” master file. This connection sends project tasks to each team member.

Click +Create workflow > Connect.

two-way sync 1

Under Source data select your Project management master sheet. This is the file you want to export data from.

Under Send to > Destination, choose Multiple Google Sheets in a folder as your data destination.

Choose between creating a new folder (Sheetgo will do it for you) or using an existing folder in your Drive.

Now, select the source column to which spreadsheet the source data splits to. Each unique entry in the selected column will create and populate a different spreadsheet. The criterion here should be the column that identifies the task owner. In this example, that’s column H.

two-way sync 2

Click Finish and save to create the split connection.

Inside the Google Drive folder “Team projects”, you will now find five different spreadsheets. Share them with your team members using Google Sheets’ share function.

Sync your spreadsheets and enhance collaboration

Step 3: Have team members work on their spreadsheets

Each team member now has a to-do list in their personal spreadsheet.

Use a QUERY formula to copy the source data from the WBS tab into a new tab (here the new tab is called “Task progress”). Make sure that you create this extra tab in every team member’s spreadsheet so they can update their task progress.

two-way sync 3

Changing the color of the columns A-J to gray to remind users not to alter the data. Because this data is drawn from the QUERY formula and adding values to any of those cells would break it.

In this example we have added two columns to the right of the data (one for Progress and one for Notes). Here, Emanuel can update his progress on tasks and the data will be sent back to the Project management spreadsheet via another Sheetgo connection.

Step 4: Create a merge connection to get progress updates

To pull back data from the team members spreadsheets to the project manager’s master sheet, add a merge connection to your workflow. Merge pulls data from multiple files into one.

Open your workflow then click +Connect on the floating menu button.

Under Data source select Multiple Google Sheets in a folder.

The merge connection will pull data from the same tab in every spreadsheet in the folder. For this reason, all source tabs must have the same name (in our example: “Task progress”).

Under Destination, select your project management master sheet.

Click Finish and save. You will see the data from each team member’s spreadsheet flow back into the master file.

two-way sync 4

Automate your workflow

You created a workflow with two-way connections between your project management master file and your team members’ spreadsheets.

Filtered data is sent from the master sheet (Project management) to each team member. Data is then merged from your team spreadsheets back into the master sheet.

To transfer data in both directions, open the workflow and click Run on the floating workflow menu bar. Sheetgo runs the connections sequentially, ensuring everything is synchronized.

You can also automate the entire workflow by scheduling regular transfers. Just click Automate on the floating workflow menu bar. From once an hour to once a month, choose how frequently you need updates.

Link Google Sheets together, automate your work

That’s how to connect two Google Sheets using Sheetgo. This automation platform enables you to build your own custom workflows for any business process. It works with multiple file types stored on different cloud storage platforms.

Discover more ways to automate with spreadsheets on our website or check out our range of pre-built workflow templates.

If you want to learn how to track changes in Google Sheets, check out this article on how to record historical values.

Connect everything and automate entire business processes

Ready to streamline your spreadsheet data?

You may also like…