See the Channel Catalog to see which Plans have access to this channel.
Google Sheets is an online spreadsheet app that lets you create and format spreadsheets and simultaneously work with other people. The Google Sheets channel operates solely on rows, not entire files. It is designed to work with existing files you have created and stored in Google Sheets.
How to Connect
- On the My pipelines page, select Create Pipeline.
- Search for or select a step, and then select it to add it to the pipeline.
When you add a step to a pipeline, it is added to the canvas of the pipeline designer. - Expand the Connection section of the step, and add the required information.
For more information about connections, see How to connect to a channel.
Connect to Google Sheets
- Expand Google Sheets in the list of channels and click Connect to Google Sheets.
- Review the permissions that Pipelines will receive once connected. Your permissions are based on your existing Google account. You can select specific permissions or allow all the options. Google Sheets permissions are required. When finished click Sign in with Google.
- Sign in with your Google credentials and click Authorize.
How to reconnect
You may need to reconnect your account to a channel. Reasons may be (but not limited to):
- If you need to connect a different account.
- Authorization updates, such as a changed password.
- Editing the access rights that Pipelines has to the channel.
To reconnect:
- Select a pipeline that already has this channel in it.
- Open a step that contains this channel.
- Under account, select Connect (or reconnect) and follow the process above, How to connect.
Steps
In this channel, steps operate on tables. A table is a considered to be the set of rows between the header row and the first blank row. Each blank row in your data defines another table. You create a header row when you create a new Google Sheet and type text into the first row. Quickbase will detect that as the header row. For more information about using Google Sheets, see Google Sheets training and help.
The steps you can use with Google Sheets fall under one category: Rows
Note: You cannot use jinja expressions in trigger conditions unless explicitly specified as possible.
Type | Name | Description |
---|---|---|
Rows | ||
Trigger | Row Created | Triggered when a row is created. |
Trigger | Row Updated | Triggered when a row is updated. |
Action | Append a Row | Add a row to an existing table. |
Action | Update Row | Update an existing row. |
Action | Delete Row | Delete the row. |
Query | Search Rows | Search the rows. |
Action | Fetch a Linked Row | Fetches a linked row. |
Action | Look Up a Row | Looks for a row and returns a single item. |
What is a spreadsheet ID?
A Spreadsheet resource represents every spreadsheet and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in your Google Sheets URL:
https://docs.google.com/spreadsheets/d/ spreadsheetId /edit#gid=0.
For more information see Google Sheets API Overview.
Row header
Select the sheet row where pipelines should expect to find the column headers.
Supported Datatypes
We support these datatypes in the Google Sheets channel:
- When you select a spreadsheet, sheet and header row, a dropdown field displays so you can select the datatype of each column from the header row. The supported datatypes are string, boolean, number, integer, and datetime. The number datatype is a floating point number.
datetime renders as datetime calendar selector. There is an additional filter that extracts time of day from datetime value. For example, for{{a.some_datetime_field|time_of_day}}
renders the time in AM/PM format including seconds. For a 24 hour format, True should be passed:{{a.some_datetime_field|time_of_day(True)}}
- Datatype selection is available on following actions: Append, Look Up, Search, and Fetch link
Numbers, ints, and dates
The difference between Ints and Numbers: Integers are integers: 1,2,3,4,5,6 while number is floating point number: 1.5, 3.2…
Google Sheets stores date/time values internally as a serial number (floating point number).
The first part is days since December 30th, 1899, the fractional part is the fraction of the time in the day. You can read more about it here: DateTimeRenderOption | Google Sheets | Google for Developers
So, if you type a valid date/time string ‘01-12-2021’ it will still be visible in the same format but Google will parse it and store it as a number internally. If you instead add a serial number it will be treated as a floating point number and you can not perform date operations with it.
Limits
Limit your sheets to 10,000 rows.
Use Cases
Here are some use case examples to help you understand how to use the Google Sheets channel.
In this example, when a row in a Google Sheet is created, we want to update a specific Quickbase table. In our case, we are updating an inventory log.
- We add a Google Sheets Row created trigger.
- Then we add a Quickbase Lookup a Record action.
- Now, we update the record using a Quickbase Update Record action.