5 minute read
Add Readable Google Sheet
1 . Log in to your Glific account.
2 . Click on
Flows from the left panel
3 . From the bottom of the
Flow page. Click on
Google Sheet link.
4 . Click on
Add Sheet button
5 . Add the
Sheet URL and
Name of the sheet which you want to integrate. Check the
Read checkbox and Click on the
Save button. See 6 below for how to get the URL
- Open the Google sheet and click on share button on the right hand side to update the sheet permission to at least
Anyone with the linkcan
6 . Copy the URL
- Paste the copied URL in the Google Sheet Integration screen in Glific and click on
- You can click on the link button to open the added sheet
It is important to note that the first cell in the Sheet should be named as 'Key' for the Read function to work.
Configuring Readable Google Sheet in the Flow
9 . Create a new flow and open it for editing
link Google sheetnode
Link google Sheet
In the second drop-down select the sheet
In Select row add
@calendar.current_dateor any variable that uniquely defines the
Keycolumn of Google sheet
OK and use the column name like below and access the value of cell values.
Send template messages using Google Sheets
Template messages could be sent using Google Sheets using
Read Google Sheets. This is generally useful when different template messages need to be scheduled & sent regularly to the beneficiaries.
Let us dive in a bit deeper by looking at the steps involved :
Create the template messages that you'd like to send via Google Sheets using the normal method. Refer Creating Template Messages
Once the template is created, make note of the UUID of the template messages you need to share using Google Sheets. Refer the screenshot below :
Create a Google Sheet with two headers,
template_uuidas shown below. Usually the
keycolumn will be having dates on which the individual templates need to be sent.
Now let us setup the flow. The flow starts with a
Link Google Sheetsnode which has the Google Sheets we created linked. This is how the node looks like :
As you can see, the row input field is given an expression
@calendar.current_date which tries to match the current date value with a
Key value. If a match is found, then that row is chosen.
- In the next step,
Send contact a messageis used and an expression is given in place of the template selection drop-down. The expression reads
<%= Glific.Clients.ColoredCow.send_template("@results.sheet.template_uuid", ["@contact.name"]) %>
In this expression, the
template_uuid matching the row in the given date gets send to the user. The expression also passes the value of the contact's name. You can pass multiple variables in this expression, if the template message demands it, using comma separation.
In this way, you can use Google Sheets for sending template messages. With the use of Triggers you'll be able to schedule different template messages for specific dates using Google Sheets, enabling you to do it in a single flow, instead of creating separate flows for separate templates. This is particularly useful, when you plan to share a set of instructions/ advisory/ content in a scheduled manner in specific dates & let's you use dynamic content for the same.
Add Writable Google Sheet
Enable Google Sheet APIs and Add Credentials
Use this wizard to create or select a project in the Google Developers Console. This will automatically turn on the API. Click Continue, then Go to credentials.
Add credentials to your projectpage, create a Service account key.
Select your project name as the service account and choose JSON as the key format. Download the generated JSON file containing the credentials.
Go to your Glific account, navigate to Settings, and select Google Sheet.
Paste the service account credentials in the provided field, and click on the Save button.
Link Writable Google Sheet in Glific
- Go to the Google Sheet screen as mentioned in the above steps.
- Click on the Add Sheet button.
- Add the Sheet URL and Name of the sheet you want to integrate. Check the
Writecheckbox and click on the Save button. Refer to Step 8 below for instructions on how to get the URL.
- Open the Google Sheet you want to write data on and click on the share button on the right-hand side. Update the sheet permissions and add the Service Account Identifier (The client email on the service account) as a user invited in the spreadsheet's Collaboration Settings with
- Copy the URL.
Paste the copied URL in the Google Sheet Integration screen in Glific and click on the
You can click on the link button to open the added sheet.
Configuring Writable Google Sheet in the Flow
- Import the below sample flow from the Flow screen and click on
settingicon to configure it.
- Here's how the imported flow would look like
- The first node is the
Send Messagenode, which asks the contact for input to be added to the sheet.
- The second node is the
Wait for Responsenode with the result name sheet_input. The response from the contact will be saved in this result and can be referenced as @results.sheet_input.
- The third node is the
Link Google sheetnode, used to add the user-entered text into the Google sheet. Click on this node to configure it.
- Under Action, select Write from the dropdown menu.
- Select the sheet from the dropdown that was added in Step 7 above i.e. Sample Sheet
- Specify the Sheet range from which you would like to enter the data in the Google sheet. For example, Sheet1!A10:D will add data from row 10 of column A in subsheet Sheet1 up to column D. This means that four data values will be added in 20.
- Enter the data to be written in the Google sheet under Input data to update in sheet. You can use values like @results.sheet_input, @contact.name, @contact.fields.age, etc.
After configuring the flow, click on the Preview button located on the bottom right side of the screen to run the flow in the simulator.
In the simulator, you will see the first message from the
Send Messagenode, which prompts you to enter text.
Type the desired text that you want to see in the sheet, and then press Enter.
This will move the flow forward, and the data will be written to the Google Sheet.
Open the Google Sheet to check the newly inserted data.
To handle blank cells in between the Google Sheet use Split by Expression