Two-Way Sync with Sheet Automation

Google Sheets provides a versatile platform for data tracking and collaboration.

However, keeping data synchronized across sheets can be a challenge. Manually updating both sheets is tedious and error-prone. Using ImportRange or Query formulas will support one-way data sync. But editing on the other end is not allowed.

Here’s where two-way sync comes in!

In this blog post, we’ll explore how to set up and implement two-way sync in Google Sheets to track project information across multiple sheets.

The scenario

Let’s consider a scenario where there is a master sheet that tracks all project information, including tasks, due dates, assigned vendors, and more. Additionally, each vendor has a dedicated sheet containing the tasks assigned to them.

The goal is to ensure that any updates made in the master sheet are reflected in the vendor-specific sheets, and vice versa, to maintain consistency and transparency in project management.

The sheets

Below are the sample sheets we create for the scenario.

The master sheet contains all project information (project name, deadlines, etc.).

Each vendor has a dedicated sheet containing the tasks assigned to them.

The data will have an ID column to identify each record. This is important to corelate data during synchronization.

Also there is a vendor column that tracks vendor assignment information. For simplicity, the vednor column should be exactly the same as the tab name of corresponding vendor sheet.

The workflow

We will use the below workflow to demonstrate how two-way sync works:

1. Task will be added to master sheet initially.
2. A new task will be assigned to the vendor when vendor column is specified.
3. Changes made in the master sheet will automatically appear in the corresponding vendor sheet.
4. Tasks updated in any vendor sheet will automatically synced back to the master sheet.

Install Sheet Automation extension

If you haven’t installed Sheet Automation, it is an extension in Google Workspace Marketplace and click “Individual Install” to install the extension.

Once it is installed, go to sheet menu Extensions - Sheet Automation and open it. A sidebar should appear on the right side shortly.

Build assignment rule

We will start by creating a rule to assign tasks.

The rule will only monitor vendor column. This is to allow the owner to control when you want to copy tasks to vendor sheets. as they are not immediately avaialbe for vednor to work on.

When the vendor column is updated and the status is new, the action will be fired and the row is duplicated to the vendor sheet, ie, assigned to the vendor.

Build sync rule on master sheet

Then we create a second rule to synchronize task from master to vendor.

This rule monitors the tasks and will be triggered when any column(except ID and vendor) is changed. The condition will check if the task has a vendor, otherwise it will be ignored. The Lookup Row action will try to find the task using task ID in the vendor sheet.

If condition is satisfied and task row is found in vendor sheet, Update Row action will pull the data from master sheet and write it to vendor sheet.

Note the task ID column and vendor column are not supposed to be changed.

Build sync rule on vendor sheet

Let’s move on to the vendor sheet.

The rule is quite similar to the one we define on master sheet.

Note it only syncronize column D and E as these columns are supposed to be updated by vendors. If you want vendor to update more columns, you can change the trigger and Update Row action accordingly.

Test 2-way sync

Once the automation rules are defined, follow the below steps to test it:

1. Create a task row with new status and blank vendor in master sheet
2. Update vendor column to Vendor2 (you should see the row copied to Vendor2 sheet)
3. Update other columns in the task in master sheet (you should see the data is duplicated to the task in Vendor2 sheet)
4. Update the due date column of the task in Vendor2 sheet (you should see the due date is updated in master sheet)

Now you have a two-way editing sync! Edits in the master sheet will update vendor sheets, and changes made by vendors will flow back to the master sheet.

That’s pretty amazing that we have a functional two-way sync with just a few simple rules.

Conclusion

With this two-way sync, your project information stays up-to-date across teams, ensuring clear communication and streamlined collaboration.

Remember, this is a basic setup, and you can customize it to fit your specific needs.

Comments

Add a Comment