Best Practices and Troubleshooting Tips for Unito's Google Sheets Integration

Before you start syncing information to Google Sheets, here's a breakdown of best practices and troubleshooting tips for this integration.

What topics does this article cover?

Check that the Unito add-on for Google Sheets is installed

When you first add a Google Sheet to a Unito flow, you'll see this box pop up.

By clicking on Verify add-on status, Unito will automatically check to ensure the add-on for Google Sheets is installed and properly configured. If it's not, you'll see this:

Click on Install Unito add-on, and you'll be directed to install the add-on and ensure it's fully configured.

Spreadsheet Sync B

Once that's done, clicking on Verify add-on status will give you this:

Meaning you've successfully installed the add-on and you're ready to keep building your flow.

Need more help? Follow our video guide:

Dealing with an "add-on is not configured" error

Often, this error is caused by your sheet not being completely configured for a Unito flow. To avoid this, make sure you've done the following things:

  • The first row of your sheet is used as a header.
  • Every column has a name.
  • The first column is labeled as UnitoID and the last column is labeled as Last Modified.

Use the top row as a header

When you create a flow with Google Sheets, Unito will look at the top row to find the fields it needs to sync. So if you named cell A3 "Description," you'd find a field called "Description" in Unito when it comes time to sync your information. That means you could map the description field in a tool like Asana or Trello to this field, and that column would be automatically populated with information pulled right out of the task descriptions in these tools.

It's a good idea to freeze the first row of your Google Sheet. Otherwise, it might get moved around when you sort your sheet, which could break your Unito flow.

Add UnitoID and Last Modified columns

The UnitoID and Last Modified fields are essential for any Unito flow involving Google Sheets. Unito will only sync information found within these two columns. In the above screenshot, that means only information between the A and E columns will be synced. If you needed to add more data to your sheet, you can absolutely create a new column, as long as it's between the UnitoID and Last Modified columns.

When you install the Unito add-on for Google Sheets, you can automatically add these columns to your sheet at the click of a button.

You can hide these columns if you want, but they do need to exist. We don't recommend hiding them, however, otherwise someone in your workspace might accidentally delete them.

Columns didn't get automatically added?

Don't worry! Since these columns are just plain text, you can add them yourself. Just make sure that you create a new column called UnitoID at the extreme left of your spreadsheet and a new column called Last Modified at the extreme right.

Recover deleted UnitoID and Last Modified columns

Say you've deleted the UnitoID and Last Modified columns. Is your flow doomed? Not quite! Should you recreate the columns? Sure, if you want duplicated data.

Your first step should be to undo the deletion, if you can. Simply hit Ctrl+Z (or Cmd+Z if you're on a Mac). If your columns come back, congratulations, you've fixed the problem.

If a simple Undo can't bring the columns back try checking through your sheet's history. Find a version that had the columns, restore it, and you should be good to go.

This holds true not just for the columns themselves, but all data in them. If you delete a cell in either the UnitoID or Last Modified columns, you'll need to undo that change or restore a previous version of the sheet. Otherwise, the information in that row won't sync anymore.

Note: Don't lock these columns, as this will prevent Unito from making changes to them. That will prevent changes from being synced.

Why sheets with heavy formula usage might fail to connect to Unito

If your Google Sheet makes heavy use of formulas across multiple tabs, Unito might not be able to sync in. This is due to a limitation from Google's API, which will cause sync requests to time out as the entire sheet is calculated. Unfortunately, there is no specific number of formulas at which your Google Sheet won't work with Unito. But if your Sheet runs noticeably slowly, that's a good sign that it won't connect to Unito properly.

You can try referring to Google's support documentation to streamline your formula usage and give your Sheet a better chance of working with Unito.

How to sync custom fields

When you're building a flow with Google Sheets, Unito treats everything on the Google Sheets side as a custom field. But some tools, like Trello and Asana, have their own kind of custom field, separate from regular fields. In order to sync these fields to Google Sheets in a two-way flow, the equivalent field in Google Sheets needs to be written exactly the same.

What does that mean?

Say you have a "High Priority" custom field in another tool, and you want to sync it to a specific column in Google Sheets. The top cell of that column has to be called "High Priority". Same casing, same words, same spelling.

You can make this much simpler by using Google Sheet's Data validation feature. To do this, right-click on a cell in the first row of your sheet and click on Data validation.

In the data validation menu, make sure Criteria is set to List of items. Then, you can enter the different options for your custom field, separated by a comma.

Now, when you click on that cell, you'll have to choose between one of the options you set, preventing any errors.

How to remove a single row from your flow

To remove a row from your spreadsheet (and your flow), 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.

To delete a row in Google Sheets, you simply have to right-click the row in question and select Delete row.

In other words, you cannot clear a row to remove it from your spreadsheet. It must be deleted entirely.

Syncing supported date-time formats

When syncing dates and times from Google Sheets, it's important to ensure that they're compatible with the tool you're trying to sync to. Unito can do most of this work for you, as long as the date and time you're syncing from Google Sheets fits with one of the options in this table.

Supported format

Example

dddd, MMMM D, YYYY

Friday, July 22, 2022

dddd, MMMM Do, YYYY

Friday, July 22nd, 2022

dddd, MMMM D, YY

Friday, July 22, 22

dddd, MMMM Do, YY

Friday, July 22nd, 22

MMM D

Jul 22

MMM Do

Jul 22nd

MMM D, YY

Jul 22, 22

MMM Do, YY

Jul 22nd, 22

MMM D, YYYY

Jul 22, 2022

MMM Do, YYYY

Jul 22nd, 2022

MMMM D

July 22

MMMM Do

July 22nd

MMMM D, YY

July 22, 22

MMMM Do, YY

July 22nd, 22

MMMM D, YYYY

July 22, 2022

MMMM Do, YYYY

July 22nd, 2022

D-MM

22-07, 5-07

D-MMM

22-Jul, 5-07

D-MM-YY

22-07-22, 5-07-22

D-MMM-YY

22-Jul-22, 5-Jul-22

D-MM-YYYY

22-07-2022, 5-07-2022

D-MMM-YYYY

22-Jul-2022, 5-Jul-2022

M-D

7-5

M-D-YY

7-5-22

M-D-YYYY

7-5-2022

MMM-D

Jul-5

MMM-D-YY

Jul-5-22

MMM-D-YYYY

Jul-5-2022

YYYY-M-D

2022-7-5

dddd, MMMM D, YYYY H:mm

Friday, July 22, 2022 12:00

dddd, MMMM D, YYYY h:mm A

Friday, July 22, 2022 12:00 PM

dddd, MMMM Do, YYYY H:mm

Friday, July 22nd, 2022 12:00

dddd, MMMM Do, YYYY h:mm A

Friday, July 22nd, 2022 12:00 PM

MMMM D, YYYY H:mm

July 22, 2022 12:00

MMMM D, YYYY h:mm A

July 22, 2022 12:00 PM

D-MM-YY H:mm

22-07-22 12:00

D-MM-YY h:mm A

22-07-22 12:00 pm

D-MM-YYYY H:mm

22-07-2022 12:00

D-MM-YYYY h:mm A

22-07-2022 12:00 PM

M-D-YY H:mm

07-22-22 12:00

M-D-YY h:mm A

07-22-22 12:00 pm

M-D-YYYY H:mm

07-22-2022 12:00

M-D-YYYY h:mm A

07-22-2022 12:00 pm

YYYY-M-D H:mm

2022-07-22 12:00

YYYY-M-D h:mm A

2022-07-22 12:00 pm

You can also manually set the preferred date and time format you want synced from Google Sheets through advanced manual options.

Avoid sorting Sheets when using 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.