Google Sheets - Migrating from v3 to v4

Bottom Line: If you use Google Sheets Row IDs in your flows, we strongly suggest you immediately revise your flows to use column-based lookups instead to avoid any disruption to your flows on October 1, 2020.

Warning: If Google Sheets are used in critical flows, you should begin testing copies of these flows with the Sheets (v4 beta) integration as soon as possible, to give us as much time as possible to fix any bugs and answer any questions before the September 30 cutoff.

On September 30, 2020, Google will deprecate version 3 of their API, in favor of version 4. Version 3 is the version that Flow XO has historically used. In order to prepare for the cutover, we have released a new Google Sheets integration for version 4.

Who it Applies To

Since we will automatically migrate the v3 version of our integration to v4 on or before September 30, 2020, this change applies to everyone in that it would be wise to test your flows with the new plugin to ensure they function as expected. However, a subset of our users will need to make certain changes now to prevent flows from breaking when the transition happens.

The API changes discussed in this article requires explicit action from you only you use the Google Sheets API in the following ways:

* You store Row IDs you receive from Google Sheets in user attributes, a database, or any other persistent storage and use those IDs to look up or update rows elsewhere in your flows
* You use the Search Rows method in your chatbot flows where response time is important

Unfortunately, Version 4 of the Google Sheets API is not backwards compatible with Version 3, and have removed some frequently functionality, primarily unique row identifiers and the ability to perform server side searches of rows by search criteria.

Although we have made every effort to keep the V3 and V4 Flow XO integrations as similar as possible, the lack of unique row identifiers in the v4 API means that we could not simply upgrade our v3 integration behind the scenes, but had to produce a new integration that could live side by side with the v3 integration until you have had a chance to migrate.

Row IDs Removed from Sheets API v4

Although under the hood the v3 and v4 versions of our Sheets integration are quite different, the main difference to end users will be that the previous Row IDs can no longer be used to find or update rows in your Google Sheets.

Instead, any API actions that used to return a Row ID will return a Row Number instead. We continue to call this field Row ID for historical purposes, but in actuality is simply the position of the row within the spreadsheet. This means two things: if your flows rely on previously saved Sheets Row IDs, those IDs will not work with the v4 API, and storing Row IDs from version 4 will not be guaranteed to refer to the same record over time if you ever remove rows from your sheet or insert rows anywhere except after the last row.

What do I need to do?

The best course of action you can take is to modify your flows to lookup rows based on column values rather than using a specific Row ID. For instance, if you use Google Sheets to store user data by e-mail address, look up a user using their e-mail address instead of using Row ID. You can make these changes to your v3 APIs now, and then when the switch to v4 happens in September, your flows will continue to run without error.

Server Side Query API Removed from Sheets API v4

In version 3 of the Google Sheets API, Google exposed an API that would allow us to construct filtered queries of Sheets rows to find rows that met a certain criteria. This is how we implemented Search Rows. This API method is missing in v4, so we changed our Search Rows implementation to load the entire spreadsheet, including all of its rows, and perform the query ourselves. This means potentially loading a lot more data each time Search Rows is used.

What do I need to do?

If you use Search Rows in conversational (chatbot) flows where response time matters, and your sheets have many rows, you will want to thoroughly test your flows that use Google Sheets to ensure they perform well enough for your use case. If they don't, you may need to remove some rows from your sheets.

Migrating from v3 to v4

As of this writing (August 14, 2020) when adding Google Sheets to your flows, you will see two versions: Google Sheets, and Google Sheets (v4 beta).

To migrate, simply use the v4 beta task instead of the Google Sheets beta. Other than the format of the Row IDs, the integrations have identical functionality. If you have important flows that use Google Sheets, we strongly recommend you duplicate these flows, convert them to v4, and thoroughly test them, reporting any issues you find with the beta version as soon as possible.

Still need help? Contact Us Contact Us