How to Automate Google Sheets with Live 2-Way Updates
How to Automate Google Sheets with Live 2-Way Updates

How to Automate Google Sheets with Live 2-Way Updates

Unito’s Google Sheets integration allows you to quickly populate tables with real-time information from your other apps or tools automatically. This can save a significant amount of time by cutting out copy-pasting between apps entirely. Instead, automated data syncing becomes a breeze, supporting you through various tasks including business reporting, project management, customer or contact tracking, and more. The process is simple, and the principles apply to any of Unito’s Google Sheets integrations from project management apps to software development tools and more.

What do I need to know before automating Google Sheets with Unito?

Here are a few concepts to keep in mind as you sync your work with Unito.

How does Unito sync app data with Google Sheets rows?

When using Unito, it’s essential to keep in mind that the platform needs to understand how to categorize your spreadsheet data in order to sync it with your desired app or tool. This is where field mapping comes into play. In Google Sheets, the fields come from the table header – the text in the first row of each spreadsheet. This means that your spreadsheet rows will become cards and your columns will become fields in the app or tool you are connecting to.

Here’s an example from a Unito integration between Google Sheets and Wrike:

Wrike synced to Google Sheets with Unito 2-Way updates
In this example we’ve highlighted a few of the fields in Wrike that appear in our Google Sheet so you can get a better sense of what to expect from Unito. The fields (details) of each selected Wrike task populate the columns of our spreadsheet.

So in other words, your Google Sheets rows represent each work item (a task, ticket, issue, contact, etc.) while the columns represent the various fields (e.g., title, description, assignee, dates, etc.).

If you aren’t sure where to begin preparing your table, you can start with this Google Sheets template. But all you really need to do is give each column a name that matches with the fields in your other tool.

What data from my other tools can sync to Google Sheets with Unito?

Whichever tool you choose, you’ll need to think about the specific fields, or work item details, related to that tool. So if you’re connecting Trello or Favro cards to Google Sheets, which card details do you want to sync?The same considerations apply for connecting Google Sheets rows to issues in Bitbucket, Azure DevOps or GitLab; tasks in Mesitertask, Teamwork, Zoho Projects, or ClickUp; tickets in Zendesk; Outlook contacts, etc.

Add a table header to your spreadsheet

To ensure Unito is able to sync app fields to the rows of your Google Sheet, you must identify each column with a header that accurately reflects your work item data. The easiest way to do this? Name the columns after the fields you’re syncing from the other tool—think title, member, description, and more.

Here is an example of a header row. You’ll need to label the first and last columns of your sheet: “UnitoID” and “Last Modified”. The Unito extension does this automatically, or you can create the titles manually.

install the Unito add-on to your Google Workspace

Go to docs.google.com. From the top of your Google Sheet, click Extensions, then Add-ons, followed by Get add-ons. Search for “Unito” and select Unito for Google Sheets.

Unito Google Sheets Add-On
You can find more detailed steps on this process here.

Watch this Google Sheets integration in action:

Step 1: Connect Google Sheets and another tool to Unito

  1. Now you can either go to the Unito App and click +Create Flow.
  2. Click Start Here to connect your tools.
  3. Select +Add a tool to this flow in either column and select Google Sheets.
  4. +Choose account to specify the Google account you want to use.
  5. Pick the sheet you want to connect to Unito.
  6. Repeat the above steps to connect your other tool with Unito.
  7. Click Confirm.

Here’s a list of permissions required by various tools to connect with Unito. If you run into any issues at this stage, don’t hesitate to contact Unito support for troubleshooting.

Here’s what our connected demo flow looks like between Airtable and Google Sheets:

Connect Airtable and Google Sheets to Unito

When you’re ready, select Confirm.

Step 2: Set a flow direction to populate Google Sheets automatically

Flow direction determines where new data should be created by Unito. With most Google Sheets use cases, you’ll want to create a one-way flow to your table. That means specific work items in your other tools will send data to your spreadsheet, but not the other way around. Later, we’ll tell Unito which fields to keep updated in a live, 2-way sync between tools.

Set flow direction between Airtable and Google Sheets
In this case, we’ve told Unito that tasks in Airtable can create rows in Google Sheets, but not vice versa.

A flow from Google Sheets (either 1 or 2-way) would mean that specified rows in your table would create work items in your other tool. If you’re going to do this, we recommend setting rules at the next step requiring specific triggers for work item creation.

Click Confirm when you’ve chosen a flow direction. 

Step 3: Filter data to export from your other tool to Google Sheets

Here you can establish triggers to determine which actions in your other tool will generate new rows in Google Sheets (or vice versa if you’ve chosen a 2-way flow direction). Since we’re demonstrating a one-way flow, we only need to set rules from our other tool to Google Sheets. To get started, select “Add a new trigger.”

Here’s an example of the types of triggers you can choose from, depending on your setup:

Set rules to filter data between Airtable and Google Sheets
Every tools is different and not all fields can be used as a trigger for work item creation. We recommend single-select fields, priority, status, etc. The idea being, if a work item reaches a particular state, only then should it sync with Google Sheets.

You can learn more about setting up rules here.

Step 4. Choose fields to automatically update in real-time

After opening this screen, you’ll have two options to choose from. Since we’re working with spreadsheets (which don’t have pre-defined fields), we’ll likely have to go with Start from scratch. But, if you name your Google Sheets fields after the exact fields in your other tool, in many cases Auto-map will be able to identify most of them. From there, you can add any that are missing.

This image has an empty alt attribute; its file name is Asana-master-project-mappings-method-selection.png
Your fields will be automatically mapped for two-way updates, but you can adjust them as needed. With a one-way sync between fields, only the destination field will update automatically when you make changes in the source field.

The top row in your sheet needs to be a table header for Unito to sync properly with other apps. The field names you choose in Google Sheets will then appear at this stage so you can pair them with fields in your other tool. If you edit the names of your fields while the Unito app is open, it won’t update with the latest names until you close your tab and re-launch Unito.

See below for an example:

Here is an example of our demo’s custom field mappings. Anything you have labelled in either tool’s table header can be mapped here as a field.

Here’s another example of completed field mappings used by our data and development teams at Unito:

Click Confirm when you’re satisfied with your field mappings.

Step 5: Save, launch, and export your data to Google Sheets!

And that’s it! You’ve just built a flow between Google Sheets and another tool. Congratulations!

If you followed the steps above, your flow will now:

  • Automatically create new Google Sheets rows based on specific work items in another tool;
  • Keep those work items and rows updated in real-time.

Here’s an example of another completed demo syncing Google Sheets with monday.com:

If you have any questions, please don’t hesitate to reach out and let us know.

What’s next after automating Google Sheets with Unito?

If you want to know what else you can do with Unito, here’s some inspiration to help you power up your workflows: