If you're using TextIt, you're likely interested in analyzing the results of each interaction your contacts have with your messaging bot. You probably also want to streamline the process of viewing and analyzing them. You can do so by sending the results of each flow run to a Google Sheet as your contacts finish them. There, you can visualize your data as it's being updated and even build a custom dashboard.
It's possible to set this up with a service called Zapier, but you can also use Google's powerful, built-in, JavaScript-based scripting language Apps Script. In this tutorial, you'll learn how to use Apps Script to add the results of a run down the 'Satisfaction Survey' sample flow to a Google Sheet. Feel free to follow along with the copy in your account, 'Sample Flow - Satisfaction Survey'.
Prerequisites
- TextIt account
- Google account
Let's Begin
Our goal: To send flow responses to a Google Sheet.
This messages in this flow run will be sent to a Google Sheet. A new row will be created for each run.
Create your Spreadsheet
First, we’ll create a new spreadsheet and set up the rows that will hold our data. To make these columns easier to interact with, we’re going to make use of Named Ranges. This allows us to give each column a name that we can reference in our script. To name a range, double-click the column, then select 'Define named range...'. Give it a one-word name. It's vital that all rows are selected, e.g. 'Sheet1!B:B'.
Go ahead and name all 7 of your new columns.
Create a Script
Now we'll start scripting. From the Tools menu, select Script Editor. Give your script a name.
Erase the existing code in the editor and add the following code:
Let’s look at the first 3 lines of code:
-
Line 1: we create a
doPost() function so that our script can receive flow results via POST requests. These POST requests will be made when the active contact reaches a Call Webhook RuleSet. This function, unique to Apps Script web apps, will process any new POST request from our flow once we’ve deployed our script as a web app.
-
Line 2: we're gaining access to our spreadsheet file. Google Apps Script differentiates between a spreadsheet and a sheet (a spreadsheet is a file that may have one or more sheets–or worksheets or tabs). We can gain access to the entire sheet by referencing its ID (the long string of characters in the spreadsheet’s URL after '/d/'). Replace the placeholder in the code with your spreadsheet ID.
-
Line 3: the
request object will contain the entire payload of the incoming POST request. We're assigning it to a variable in order to conveniently access its parameters.
Publish your Script
Select Deploy as Web App from the Publish menu in the script editor, then choose the following options:
- Execute the app as: Me
- Who has access to the app: Anyone, even anonymous
Click Deploy, then copy the address of the Current web app URL to your clipboard. You’ll need that for your webhook. Congradulations, you've just published a web app!
Add a Webhook to your Flow
Add a 'Call Webhook' RuleSet to the end of your 'Satisfaction Survey' flow. Select POST and paste the URL provided in the previous step:
Before you click Ok, make sure to add a query string to the end of the URL to include the flow variables that represent the responses your flow has collected. Not familiar with flow variables? Learn about them here.
Here's mine:
https://script.google.com/macros/s/AKfycby-lk-z7dOqUHBdBJx5ciJxanpiqcQeLBB-vNTbZOzTTVDd9OM/exec?date=@date.now&number=@contact.tel_e164&returning=@flow.shop_again&recommend=@flow.recommend&suggestion=@flow.suggestion&gender=@flow.gender&age=@flow.age
The query string represents everything from the '?' to the end of the URL. It allows you to create parameters to be sent to your web app. We're simply adding flow fields to the URL so that TextIt knows to send them to our web app and the Google Sheet knows to add them to its columns. The names you give these values should be one word and describe them accurately as you'll be referencing them in your script.
Finish your Script
First, we'll need to add a function that crawls the rows in each column. The nextRow() function will do just that. Copy this code, replacing the parameter in SpreadsheetApp.openById() with the ID after the /d/ in your Google Sheet's URL. I've highlighted its location below:
https://docs.google.com/spreadsheets/d/<sheet_id>/edit#gid=0
The final step is to map the incoming data–represented by TextIt variables–to the columns in the your Google Sheet. Copy the code below (starting from the comment '// Isolate flow field values...') and replace the parameter and column names in the code above. For example, consider these two separate but related lines:
var date = request.parameters.date;
sheets.getRangeByName('date').getCell(nextRow, 1).setValue(date);.
In this case, date in request.parameters.date and .getCell(nextRow, 1).setValue(date) should be changed to the name you gave your date flow field (if other than @flow.date), and 'date' in sheets.getRangeByName('date') should be changed to the name you gave your date column in your Google Sheet (if other than 'date').
Test your Service
Nice job, you're ready to test your web app with the simulator. Complete your flow and watch your responses appear in your Google Sheet
Questions? Comments? Let us know. We love hearing from you.
