This blog post will explore the exciting new integration between Flowlet and Google Sheets. Three new blocks allow you to easily read or write data from Google Sheets in a Flowlet flow. There is no need for complicated authentication setup or coding.
The process
To use the Flowlet and Google Sheets integration, you first need to share the Google Sheet with the Flowlet service account. Share the sheet the same way you share with a colleague, but use the address flowlet@flowlet.iam.gserviceaccount.com. Then, you'll only need the sheet's URL in Flowlet to start using it. Three blocks are available, which are described below.
Reading data
You'll need to use the "Read from Google Sheets" block in your flow. This block requires three pieces of information to work correctly: the URL of the sheet, the sheet name, and the range you'd like to read. The URL of the sheet is simply the web address of the Google Sheet that you'd like to read. The sheet name refers to the name of the specific sheet, as shown below the sheet. This name is often "Sheet1" as shown in the screencast below, but the name differs per language.
Finally, the range refers to the specific cells you want to read within the sheet. You can enter this range in either A1 or R1C1 notation.
A1 notation refers to the standard column and row format, where each cell is identified by its column letter and row number. For example, if you want to read the values in cells A1 to C3, you would enter "A1:C3" as the range. A1 notation is the most common notation in spreadsheets.
On the other hand, R1C1 notation refers to a system where each cell is identified by its row number and column number. For example, the same range from A1 notation would be represented as "R1C1:R3C3" in R1C1 notation. This format does not require you to convert a number to a letter when you know which column number to read.
The "Read from Google Sheets" block will output an array of rows from the specified range. Each row in the array is represented as an array of cells, with the values within each cell being either a number or a string.
In the example sheet shown above, you'll get the output [[2, 135476], [3, 135476]] when reading the range A2:B4.
Writing data
With the "Write to Google Sheets" block, writing data is just as simple as reading data. You'll need to provide the values you want to write. The output is an array, with each value being either a string or a number. Instead of a range, you'll provide a single cell, namely the cell where you want to write the first value of the array.
By default, the "Write to Google Sheets" block will write data as a row, so the second value in the values array will appear to the right of the starting cell. You can change this behavior by setting the "dimension" input to "columns". Then, the second value in the values array will appear below the starting cell. Using this block, you can only write one cell or row at a time.
Finally, you can append data to the bottom of your sheet by setting the append input to true.
This flag is handy when you're adding new data to your sheet and don't want to overwrite any existing data.
In this mode, set the cell input to the starting cell where the table starts.
This cell is "A2" in the example sheet seen earlier.
Get Sheet Metadata
In addition to reading and writing data, Flowlet also provides a block for reading the metadata of a Google Sheet. With the "Get Sheet Metadata" block, you'll receive the name, locale, timezone, and names of the individual sheets within the workbook. Only the URL is required to use this block.
Handling dates
When reading dates from a Google Sheet using Flowlet, you may notice that Google returns them as numbers, such as 44967.5. That is because the values are stored in the sheet using an Excel serial date format. Its value is the number of days since Jan 1st, 1900. Decimals represent time. Lotus 1-2-3 introduced this format decades ago, but it's still the internal date format in spreadsheets.
There is a simple JavaScript one-liner to convert this format to an ISO8601 date string ("YYYY-MM-DD").
new Date(Date.UTC(0, 0, input - 1)).toISOString().substring(0, 10)
Or with time:
new Date(Date.UTC(0, 0, input - 1) + (input - ~~input) * 864e5).toISOString()
This one-liner assumes the UTC timezone. Excel dates are local times with no timezone information.
Writing dates to a Google Sheet is much simpler. You can just pass an ISO date string, such as "2023-02-10 12:00:00", to the "Write to Google Sheets" block. The sheet will recognize the value as a date and format it accordingly.
Using the Google REST API directly
While it is possible to use the Google REST API directly without these blocks, there are some drawbacks. The process can become quite complex, and security is a concern. The Google API requires selecting a specific permission scope. This permission either grants complete access to your Google Drive or limits writing powers to sheets created by the API client. To mitigate these issues, you can use Flowlet's service account to share sheets with read-only or write access. So, there is no need to grant access to other files in your Google Drive. This solution is more secure, as it follows the principle of least privilege. Furthermore, it provides a more straightforward, no-code solution for accessing Google Sheets data.
Nevertheless, using Google REST API directly gives you access to all its features. You can refer to the Google API documentation for more information on the available endpoints. Using the API directly requires setting up an OAuth2 client, which you must register at the Google developer console.
Previously, you needed an OAuth2 client to write to a sheet, but using the pre-made blocks is much simpler and straightforward.
Ready to build your Google Sheets integration?
- Get started with a 7-day trial
- Follow us on Medium, Twitter, or LinkedIn