How to connect two Google Sheets two-way sync

by Jun 3, 2020

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 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 and connect two Google Sheets? This means linking separate files and moving data between them in a dynamic, automated workflow. By connecting spreadsheets, you can build a database-type system without coding.

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. By creating two connections, you can send in both directions, creating an automated two-way sync. When a change occurs in spreadsheet A, the data is updated in 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 where data is segregated 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.

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 wants 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 are working 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. Log in to the web app to start creating the two-way sync between your Google Sheets.

Step 2: Distribute the project tasks

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

Click +Create workflow > Connect.

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. 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 is distributed 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.

Click Finish and save to create the distribute 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.

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.

As you can see, columns A-J are gray because this data comes from the QUERY formula. Changing the color of the columns reminds users not to alter the data in that part of the spreadsheet.

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

To pull back data from the team members spreadsheets to the project manager’s master sheet, add a consolidate connection to your workflow. Consolidate 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 consolidate 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 distributed from the master sheet (Project management) out to each team member. Data is then consolidated 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 updates in the master sheet.

To update 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 updates. 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 updated.

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.

Share This