Integrate Google sheets in Flows

Below are the steps to Integrate Google sheets in Flows.

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 theSave button. See 6 below for how to get the URL

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

6 . Copy the URL

Sample file

Sample_file_Gsheet_Integration.xlsx

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

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

Configuring Readable Google Sheet in the Flow

9 . Create a new flow and open it for editing

10. Use link Google sheet node

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

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.

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

Link Writable Google Sheet in Glific

5. Go to the Google Sheet screen as mentioned in the above steps.

6. Click on the Add Sheet button.

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

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

9. Copy the URL.

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

    

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

Configuring Writable Google Sheet in the Flow

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

Sample Flow

Write to Google Sheet.json

13. Here's how the imported flow would look like

14. The first node is the Send Message node, which asks the contact for input to be added to the sheet.

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

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

17. Under Action, select Write from the dropdown menu.

18. Select the sheet from the dropdown that was added in Step 7 above i.e. Sample Sheet

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

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.

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

    

22. In the simulator, you will see the first message from the Send Message node, which prompts you to enter text.

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

    

24. This will move the flow forward, and the data will be written to the Google Sheet.

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