Skip to main content

Link Google Sheets

5 minute read                                                                                                                         Advanced


Video Tutorial


Add Readable Google Sheet

1 . Log in to your Glific account.

2 . Click on Flows from the left panel

image

3 . From the bottom of the Flow page. Click on Google Sheet link.

image

4 . Click on Add Sheet button

image

5 . Add the Sheet URL and Name of the sheet which you want to integrate. Check the Read checkbox and Click on theSave button. See 6 below for how to get the URL

image

  1. 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 link can View

image

6 . Copy the URL

image

Sample File

  1. Paste the copied URL in the Google Sheet Integration screen in Glific and click on SAVE button

image

  1. You can click on the link button to open the added sheet

image

  • 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

image

  1. Use link Google sheet node

image

image

  1. Select Link google Sheet

  2. In the second drop-down select the sheet

  3. In Select row add @calendar.current_date or any variable that uniquely defines the Key column of Google sheet

Click on OK and use the column name like below and access the value of cell values.

@results.sheet.message_1

image


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 :

  1. Create the template messages that you'd like to send via Google Sheets using the normal method. Refer Creating Template Messages

  2. 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 :

    Screenshot 2023-09-15 at 8 54 26 AM
  3. Create a Google Sheet with two headers, Key and template_uuid as shown below. Usually the key column will be having dates on which the individual templates need to be sent.

    Screenshot 2023-09-15 at 1 25 33 PM
  4. Now let us setup the flow. The flow starts with a Link Google Sheets node which has the Google Sheets we created linked. This is how the node looks like :

Screenshot 2023-09-15 at 1 50 48 PM

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.

  1. In the next step, Send contact a message is 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"]) %>

Screenshot 2023-09-15 at 1 56 54 PM

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.

Send Interactive Messages using Google Sheets

Glific enables you to send interactive messages using Google Sheets. This is particularly helpful when you need the options within the interactive messages to be dynamic. This lets you use the same interactive message for creating multiple dynamic list of options to choose from for the beneficiary.

Let's dive in a bit deeper to see how it works.

  1. Create a Google Sheet with the list of options you wish to have in the interactive message in a row as shown below. In this example, we want the values given in row 2 i.e key value as 1 to be used as the options in the interactive message we're going to create. These values could be updated in real-time to change the options inside the interactive message or you can add more rows to have different interactive messages too.

    Screenshot 2023-09-19 at 2 12 47 PM
  2. Add the above created Google Sheet on the Glific. Please find the steps to do that here

  3. Now create a sample Interactive Message with a list message (can do similarly for button options too). Only the first option needs to be entered as shown below with some sample data, as it will be rewrited by the values on the sheet- hence the rest of the options could be left empty. The idea is to fetch values for the options list dynamically from the sheet.

    Screenshot 2023-09-19 at 11 04 36 AM
  4. Now since we have the Google Sheets linked & the interactive message created, let's create a sapmple flow to understand how it works :

    Screenshot 2023-09-19 at 11 51 38 AM
  5. As you can see, the flow starts with a Link Google Sheets node, where the readable google sheets from which the options to be fetched inside the interactive message is added. This is how the Google Sheets node is configured :

    Screenshot 2023-09-19 at 11 53 27 AM
  • In the node above, the sheet we created have been linked & the corresponding action is marked as Read since the options for Interactive Message are being read from the Google Sheets.
  • Here 1 is added in Select row field, as the values for the interactive message are present on the row with the key value as 1 and it is stored under the variable name sheet
  • Instead of 1, you can also use dynamic values like flow variables or contact variables, functions like @calendar.current_date etc.
  1. The next node consists of Send contact an interactive message node and this is how it is configuredScreenshot 2023-09-19 at 2 14 22 PM
  • The interactive message we've created is chosen from the dropdown.
  • The dynamic fields checkbox is selected which enables us to use dynamic values inside the interactive message.
  • In the input field box with number of variables, the number of options in the interactive message needs to be entered. Here we've given 8, since we need 8 options in the list sent (as also shown on google sheets).
  • The id and variable values need to be filled. The id could be any unique value for referencing & variable value is being fetched from the linked Google Sheet.
  • The variable names are filled as @results.sheet.course1, @results.sheet.course2 and so on representing each row value from the row we've chosen to read from.
  1. Now since the flow is configured, let's test the flowScreenshot 2023-09-19 at 12 07 16 PM

As you can see on the preview above, the list of options we had given in the Google Sheets are shown as a list of options inside the Interactive Message.

  • Sending interactive messages using Google Sheets could avoid the redundancy of creating multiple interactive messages when the list of options needs to be updated (some option needs to be added or removed) or multiple interactive messages to be used.
  • It can also be used for handling situations where the list of options is more than 10 by using 'More' as the tenth option and having a separate list of options in the next row.

Add Writable Google Sheet

Enable Google Sheet APIs and Add Credentials

  1. 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.

  2. On the Add credentials to your project page, create a Service account key.

  3. Select your project name as the service account and choose JSON as the key format. Download the generated JSON file containing the credentials.

  4. Go to your Glific account, navigate to Settings, and select Google Sheet.

Screenshot 2023-05-19 at 12 03 31 AM

Paste the service account credentials in the provided field, and click on the Save button.

Screenshot 2023-05-19 at 12 10 26 AM
  1. Go to the Google Sheet screen as mentioned in the above steps.

image

  1. Click on the Add Sheet button.

image

  1. Add the Sheet URL and Name of the sheet you want to integrate. Check the Write checkbox and click on the Save button. Refer to Step 8 below for instructions on how to get the URL.
Screenshot 2023-05-18 at 11 17 01 PM
  1. 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 Editor permission.
Screenshot 2023-05-18 at 11 23 49 PM
  1. Copy the URL.
Screenshot 2023-05-18 at 11 22 26 PM
  1. Paste the copied URL in the Google Sheet Integration screen in Glific and click on the SAVE button.

    Screenshot 2023-05-18 at 11 27 28 PM
  2. You can click on the link button to open the added sheet.

Screenshot 2023-05-18 at 11 40 48 PM

Configuring Writable Google Sheet in the Flow

  1. Import the below sample flow from the Flow screen and click on setting icon to configure it.

Sample Flow

Write to Google Sheet.json

  1. Here's how the imported flow would look like
Screenshot 2023-05-18 at 11 43 21 PM
  1. The first node is the Send Message node, which asks the contact for input to be added to the sheet.
Screenshot 2023-05-18 at 11 45 13 PM
  1. The second node is the Wait for Response node 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.
Screenshot 2023-05-18 at 11 45 36 PM
  1. The third node is the Link Google sheet node, used to add the user-entered text into the Google sheet. Click on this node to configure it.
Screenshot 2023-05-18 at 11 51 07 PM
  1. Under Action, select Write from the dropdown menu.

Screenshot6

  1. Select the sheet from the dropdown that was added in Step 7 above i.e. Sample Sheet
Screenshot 2023-05-18 at 11 51 58 PM
  1. 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.

Screenshot8

  1. 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.
Screenshot 2023-05-18 at 11 54 24 PM
  1. 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.

    Screenshot 2023-05-18 at 11 59 10 PM
  2. In the simulator, you will see the first message from the Send Message node, which prompts you to enter text.

  3. Type the desired text that you want to see in the sheet, and then press Enter.

    Screenshot 2023-05-18 at 11 56 48 PM
  4. This will move the flow forward, and the data will be written to the Google Sheet.

  5. Open the Google Sheet to check the newly inserted data.

    Screenshot 2023-05-18 at 11 58 31 PM

Note

  • This functionality can be used to write upto 30 columns in google sheets
  • To handle blank cells in between the Google Sheet use Split by Expression

Advanced Feature Webinar