How to connect two Google Sheets two-way sync

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. In addition, email attachments and outdated file versions are finally becoming a thing of the past. Just share the spreadsheet with your colleague or client and they can access the data they need, whenever they need it. But did you know you can take cloud collaboration to another level if you learn how to connect two Google Sheets?

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

Why connect two Google Sheets?

If you connect two Google Sheets, you can move data back and forth between them without copy-pasting, coding, or Importrange. Moreover, by creating two connections, you can send in both directions, creating automated two-way sync. When a change occurs in spreadsheet A, the data automatically transfers itself to spreadsheet B and vice versa.

Connected spreadsheets have several advantages. As well as eliminating the need for manual work, they enable you to create streamlined workflows that segregate data in separate “buckets”. This means smaller, lighter spreadsheets, which are quicker to process.

A workflow of interconnected sheets also gives you more reliable data: with fewer hands touching the same spreadsheet, there’s a lower chance of error. While it might seem practical to create one central spreadsheet that everyone can access, monster spreadsheets are slow, unreliable and even a security risk if your team is handling sensitive information.

By connecting sheets 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. Click below to start connecting files and creating your own workflows.

What is a two-way sync?

When you connect two Google Sheets with Sheetgo, you can send data from a tab in the source spreadsheet to a tab in the destination spreadsheet. The data (or a filtered selection of the data) from spreadsheet A appears in spreadsheet B automatically, leaving spreadsheet A intact. This is ideal for reporting, sharing information or powering data dashboards.

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. A typical example is the project manager who needs to send tasks to team members whilst receiving status updates. Both the manager and the team members need information from the other. Here’s how it works:

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.

We will create two-way connections between a project management master file and five separate, individual files: one for each team member.

Data from the “Project management” spreadsheet is sent to the work breakdown structure “WBS” 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.

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: Split the project tasks

First, create the split connection from your Project management master file. This connection sends project tasks to each team member.

Click +Create workflow > Connect.

Under Send to > Destination, choose Multiple Google Sheets in a folder as your data destination. This is where you want to send data to.

Choose between a new folder (Sheetgo will create it for you automatically) or an existing folder in your Drive. Give the folder a name (in this example our folder is called “Team projects”).

Now, select the source column. The name or value in this column defines 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.

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.

Step 3: Team members fill out progress

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.

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: Merge task progress data from team members

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.

Automate your workflow

You have now created a workflow with two-way connections between your project management master file and your team members’ spreadsheets. It works in a loop.

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

When the manager makes changes to the project tasks, the data also changes in the WBS tab in the team spreadsheets. Likewise, when team members make changes to their spreadsheet, the data also transfers 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 the data to be transferred.

⇒ Watch the video below to learn how to connect Google Sheets and create a two way sync.

Automate your work in spreadsheets

That’s how to connect two Google Sheets with a two-way sync. Sheetgo enables you to build your own custom workflow for any business process, including 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, including Project Management and Cash Flow.

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