Google Sheet Block:
Overview: The Google Sheets Integration in BIK Flow Builder allows seamless interaction with Google Sheets directly from your workflow. This integration enables you to perform three key actions: adding rows, updating rows, and retrieving row data. Recent updates have enhanced these functionalities, improving efficiency and flexibility in data management.
Types of Google Sheets Blocks:
1. Add a Row
The "Add a Row" block allows you to insert new data into a Google Sheet by adding a new row.
New Features:
Add rows based on Column IDs (e.g., A, B, C) instead of just headers.
2. Update a Row
The "Update a Row" block lets you modify existing data in a Google Sheet by updating a specified row.
New Features:
Update multiple columns simultaneously.
Update rows based on row numbers retrieved from the "Add a Row" block, allowing unique row identification even if multiple rows contain the same data.
Supports both search by row number and search for row using lookup column and lookup
3. Get Row Data
The "Get Row Data" block retrieves data from a specific row in a Google Sheet, based on a lookup value.
New Features:
Retrieve rows based on row numbers, allowing accurate data fetching when multiple rows have the same data.
General Setup Steps:
Access the Flow Builder: Go to the BIK Flow Builder.
Open Action Blocks: On the side menu, open the ‘Action’ blocks.
Locate Google Sheets Blocks: Scroll down to find the Google Sheets actions.
Detailed Setup for Each Block:
Add a Row:
Grant Editor Access: Give editor access to
[email protected].Select Action: Choose “Add row data” from the actions on the side panel.
Sheet URL: Copy and paste your sheet’s URL into the Sheet URL input field.
Column Identifier:
Choose the column identifier as Header or ID.
For Header: Enter the Column header from the excel sheet and the field/data you wish to add.
For Id: Enter the Column id like A or B etc and the field/data you wish to add.
Update a Row:
Grant Editor Access: Give editor access to
[email protected].Select Action: Choose “Update row data” from the actions on the side panel.
Sheet URL: Copy and paste your sheet’s URL into the Sheet URL input field.
Specify Target row and Update Information:
a. Specify Row Number: Update rows based on row numbers retrieved from the "Add a Row" block or alternatively enter the row number you wish to update.
Target Column: Specify the column you want to update.
Update Field: Enter the new value to push to the destination column.
b. Search for Row:
Lookup Column: Add the column where the lookup value is found.
Lookup Value: Enter the value to search for in the lookup column.
Target Column: Specify the column you want to update.
Update Field: Enter the new value to push to the destination column.
Get Row Data:
Grant Editor Access: Give editor access to
[email protected].Select Action: Choose “Get row data” from the actions on the side panel.
Sheet URL: Copy and paste your sheet’s URL into the Sheet URL input field.
Specify Target row:
a. Specify Row Number: Get row data based on row numbers retrieved from the "Add a Row" block or alternatively enter the row number from where you wish to get the data.
b. Search for Row:
Lookup Column: Add the column where the lookup value is found.
Lookup Value: Enter the value to search for in the lookup column.
Save Column Names: Specify the column names for the data you want to fetch against the lookup value.
Use Retrieved Data: The retrieved data can be used in the flow later, found under “API data responses”.
Sheet ID Support: Handling Multiple Sheets
Purpose:
The Sheet ID support feature allows you to specify the exact sheet within a Google Sheets document where you want to add, update, or retrieve data. This feature is particularly useful when dealing with multiple sheets in a single document.
How It Solves Issues:
Targeted Operations: In documents with multiple sheets, specifying the Sheet ID ensures that your actions (adding, updating, retrieving data) are performed on the correct sheet, avoiding confusion and potential errors.
Default Behavior: If no Sheet ID is specified, the system defaults to Sheet ID 0, typically the first sheet in the document. This provides flexibility while allowing precision when needed.
Clients experiencing quota exceeded errors should transition to the ID method, which is more optimized and reduces API calls to Google Sheets.
The new versions of Add, Update, and Get Row blocks are expected to handle quota exceeded errors better.
FAQs
Q1: What permissions are required for the integration to work?
A1: You need to grant editor access to [email protected] for the integration to function properly.
Q2: Can I use variables in the "Add a Row" block?
A2: Yes, you can use variables in the "Value" input field when specifying the data to be added.
Q3: What is a Lookup Column in the "Update a Row" and "Get Row Data" blocks?
A3: The Lookup Column is the column where the system will find the value you want to search for to either update or fetch the row data.
Q4: What happens if the Lookup Value is not found in the specified Lookup Column?
A4: If the Lookup Value is not found, the "Update a Row" or "Get Row Data" action will not be able to perform the intended operation.
Q5: Where can I find the data retrieved using the "Get Row Data" block?
A5: The data retrieved can be found under “API data responses” in the Variable picker.
Q6: What is the benefit of using Column IDs over headers?
A6: Using Column IDs (e.g., A, B, C) can be more precise, especially when headers might change or contain special characters.
Q7: What is Sheet ID, and how is it used?
A7: Sheet ID allows you to specify the exact sheet within a Google Sheets document where you want to add, update, or retrieve data. This is an optional field, and if not specified, Sheet ID 0 will be used.
For further assistance or to raise feature requests related to Google sheet in Journeys, please contact [email protected].