A Guide to Unito Spreadsheet Sync - Google Sheets Integration

Looking to add Google Sheets to your workflow? Here's what you can expect from this integration and Unito Spreadsheet Sync.

What does this article cover?

Unito's Google Sheets integration enables you to pull crucial data out of your other tools and into a spreadsheet or vice versa when you create a flow. 

A flow is the connection between Google Sheets and your other apps or tools that enables Unito to keep your data synced in real-time with automated updates based on rules set by you.

Note: We have a separate guide for Trello users getting started with Unito's Export & Sync Power-Up here.

In order to set up a flow, you'll first need to add the Google Sheets extension: Unito Spreadsheet Sync from the Google Workspace Marketplace. This extension must be added to the Google Sheet you plan on syncing.

Unito Spreadsheet Sync

Note: If you have pop-ups blocked while attempting to install the Unito add-on to Google Sheets, it won't be able to load. You'll have to enable pop-up windows and try again.

Installation instructions for Unito Spreadsheet Sync

Get the Unito Spreadsheet Sync Add-On

You can either get the add-on from the Google Workspace Marketplace or install it directly from Google Sheets. To do so, click on Extensions, then Add-ons, and Get add-ons and search for Unito.

Insert Two Columns: Unito ID and Last Modified

The add-on includes a button that automatically inserts two columns into your sheet: UnitoID and Last Modified. These columns are necessary in order for your data to sync. You can either add them by clicking the button within Unito Spreadsheet Sync, or you can add them yourself. 

Screenshot 2024-03-27 at 1.45.32 PM

These columns determine what data will sync (or not) between your Sheet and other tool(s). Only data between those columns will sync. You can hide them once they're created, but they need to exist in every sheet you sync with Unito. 

Note: If the column to the far right of your spreadsheet has special formatting, the Last Modified column won't be created properly. If needed, insert another blank column before adding the Last Modified column through the Unito add-on.

Auto-populate your header row and fields with Unito

Once the add-on is installed, make sure your table includes a header row with the names of each field you plan on syncing. Unito will then use the text in your header row to match fields from your other app or tool sync fields into the columns below.

Unito Spreadsheet Sync now allows you to automatically populate a header row in a blank spreadsheet by selecting import from Unito Spreadsheet Sync. Here's how:

  1. After you insert two columns into a blank sheet, click Create Flow from within Unito Spreadsheet Sync.
  2. Import data from the tool of your choice.
  3. When you create a flow this way, you'll be given an option to auto-populate your row header.

Autopopulate

Here are a few limitations to keep in mind before auto-populating your header row:

  • This only works when importing data into Google Sheets from another tool.
  • Your data needs to be imported into an empty spreadsheet.
  • Your data must be spread across fewer than 50 fields (columns).

Watch how to install Unito Spreadsheet Sync

Supported fields and other features of this Google Sheets integration

Any text or number field in your other tools will appear in Google Sheets synced to your columns.

In order to know where to sync your data, you'll first need to create a row header (or table header) in Google Sheets.

Note: Unito's Google Sheets integration does not sync subtasks.

So be sure to give each cell in the first row a similar name to each of the fields you'll be syncing. It doesn't need to be case-sensitive. We also recommend freezing the first row once your fields are set.

Example: Each cell in the first row will become its own field. So if cell B1 has the word "Title" in it, you'll see that field appear in Unito's list of possible field mappings with the same name. In the image below, you can see what that looks like in Unito.

Unito syncs rows as individual work items. So if you sync a Google sheet to a Trello Board, row two of your sheet will become a Trello card, populated with the information in each column.

Syncing dropdowns with other tool

Did you know you can sync dropdowns in Google Sheets with fields in other tools? As long as these fields have multiple options — like Trello labels or Asana tags — then you can map them. One thing to keep in mind; when you make changes to dropdowns in Google Sheets, you'll get this pop-up:

Google Sheets apply to all

Make sure you hit Apply to all to keep things working right.

What counts as an item in sync?

For Google Sheets, Unito counts every synced row as an item in sync.

Setting rules in Unito to manage your spreadsheets

With Unito, you can build rules that filter information out of your flow. For this integration, you can build two types of rules:

  • Sync based on a dropdown field : Single-select dropdown fields can be used as filters in Unito and your options will all be selectable when you're setting up flow rules. 

    Google Sheets Dropdown 1
    sync by dropdown sheets
  • Sync by value: If you don't use dropdown fields, you can only sync data from Google Sheets based on whether a cell has any value or empty value.
    sync by value sheets
  • Any value refers to, as you might imagine, any alphanumeric character: a letter, number, symbol or other. 

Read more about setting rules in Unito.

For modern integrations with an API that offers it, you will get additional rules:

  • Dimension value is
  • Dimension value contains 
  • Metric value is equal, above or below some value
  • Max number of rows in report that you can edit (limit)

Creating spreadsheets with Unito

When building a flow with Google Sheets, you can create a new spreadsheet directly from Unito. Just click Create a new Google Sheet after connecting your Google account.

Screenshot 2023-02-17 at 12.25.39 PM

Your new spreadsheet will be created at the root of your Google Drive — not in any specific folder. Once it's created, it will automatically be selected as the block of work used in your flow.

Note that you'll still need to install the Unito add-on for Google Sheets in this new spreadsheet, otherwise you won't be able to sync any data over.

How to remove rows from your flow

There are two ways to eliminate rows from your spreadsheet and flow.

Set rules to remove inactive rows

A screenshot of a user syncing Xero invoices to Google Sheets and setting rules in UnitoIn the actions section of your rules, you can now tell Unito whether to keep “inactive” rows in Google Sheets or delete them automatically once your bills or invoices have been processed.

  • Choose “Preserve” if you want to keep records of all work items that were synced by Unito.
  • Choose “Remove” if you prefer to only keep track of all active work items in Google Sheets.

Manually deleting rows

Right-click on a row and select  Delete row to remove it completely. If you only clear it, Unito won't replace it with a new row and you'll end up with a blank space in your sheet.

How to close or archive items from Google Sheets

You can close or archive tasks, tickets, issues, incidents or other work items synced to your spreadsheet with a few simple configurations. Here's how to do it with Trello:

 

Adjusting the date time format

You can adjust the date-time format in your spreadsheet to any format you wish simply by opening your sheet and selecting Format > Number > Custom date and time. Then you can choose the format you wish and apply it to all the cells in your date columns.

Screenshot of setting the date time format in Google Sheets for a Unito sync

 

Finding spreadsheets with a URL

When it's time to connect your spreadsheet, all you have to do is paste its URL into Unito to add it to a flow.

spreadsheet url add

 

Limitations and other considerations for Unito's Google Sheets integration

  • Cleared Rows vs. Deleted Rows: To remove a row from your spreadsheet, you must delete the row entirely. If you only clear the row, it will continue to count as an item in sync, and subsequent rows will simply appear beneath it.

  • Cell character limit: You won't be able to sync information into a cell if the source (i.e., the work item you're syncing to Google Sheets) contains more than 50,000 characters due to a limitation in Google Sheets.
  • Attachments: Not currently supported for this integration.

  • No subtasks: Unito's Google Sheets integration does not sync subtasks.
  • Rich text: That means bold and italic text won't sync over.

  • Hyperlinks: These are considered rich text, so they're not supported by this integration. Note: If you try to sync a hyperlink from one Google Sheet to another, the link might get deleted in the destination sheet.

  • Webhooks: These aren't supported yet. This means that a Google Sheets flow will check for changes every few minutes rather than syncing changes in real-time.

  • Google Sheets sorting and filters: If you use filters in your Google Sheets, avoid sorting them. That's because sorting will only impact unfiltered rows, which will prevent your Unito flow from accurately reading the information in them. If you do need to sort your Sheet, make sure that it has no filters whatsoever.
  • Dates: Unito only supports some date and time formats in Google Sheets. You can see them here.

  • Updating cells in header rows: If you need to update the cells in your header row, you'll have to update the field mappings in Unito to match. Otherwise, your flow will stop syncing any cells in a column with an updated header.

You can get a full list of this integration's limitations here.

Common error messages

If you see either of the error messages below, it likely indicates that one or both of the columns: "UnitoID" and "Last Modified" have been deleted or modified from your sheet. You'll need to re-insert them (either manually or with the add-on) before your data will sync again.

"We’ve detected that mandatory columns have been removed from your Google Sheet. Let’s get you back up and running."

"The container configuration does not allow us to sync."

Here's our guide to best practices and troubleshooting tips for Unito Spreadsheet Sync.

 

Which permissions are necessary to connect Google Sheets and Unito?

During the authorization process, you'll need to give Unito access to your Google account so your flow can see and edit your sheets.

Read & Write Metadata Access to the user's file metadata, excluding downloadUrl and thumbnail
Read & Write Sheets Allows read/write access to the user's sheets and their properties
Read Email address View your email address
Read Profile See your personal info, including any personal info you've made publicly available

 

Recap of considerations for syncing spreadsheets:

  • Use the first row as a header. Make sure the first row contains distinct and descriptive names for your columns (like priority or due date).

  • When you click on Insert the two columns in this sheet, Unito will automatically create a column named UnitoID at the beginning of your sheet and one named Last Modified at the end.

  • Don't add, remove, or modify anything in the UnitoID and Last Modified columns. Doing so can disrupt your flow. Instead we recommend hiding them.

  • Once you've been through the two steps above, log in to Unito's standalone sync platform and you can start building your first flow.

Ready to get going? Here's a step-by-step guide to connect Google Sheets and another app or tool to Unito so you can start populating spreadsheets with real-time data.

Got feedback? Submit a Unito feature request to let us know what you'd like to see in the next update to our Google Sheets Integration integration.