How to Save your RapidPro flow result to Google Sheets

Jan. 8, 2018

Save your RapidPro flow result to Google Sheet

Background RapidPro has good abilities to strengthen communication with end users, and can do real time monitoring for surveys or any other reporting. But, if our clients can’t access RapidPro and need to know the result, how can they do so?  This guide shows how you can share results with a person or organisation who has a prorgammatic realtionship to your RapidPro project, without the RapidPro manager downloading the results and sending them manually. In this tutorial, we will use the Google sheets app, Thanks to Google who provided the API script for doing this.

Preparation 

You need to prepare some steps to make this happen. 1. Flow in RapidPro 2. Google Drive → Googlesheet file 3. Scripting with GoogleScript

Let’s Do It! 1. Flow I will not explain how to create the flow. If you can’t create one, then you need to go to this tutorial and documentation (http://docs.rapidpro.io). Sample flow contain with two parameters: name, age. Create this flow below in your workspace and skip Call Webhook URL, we will modify that later.


2. Prepare your Google sheets file 

In GoogleDrive Open your Gmail (http://gmail.google.com), make sure you have only one active Gmail, please logout for other Gmail if you have.


Open your Googledrive, from Top Right, click on Grid and choose Drive


GoogleDrive ready, Click New → Google Sheets → Blank spreadsheet

Untitled Spreadsheet shows up, Change the file name into anything but space. (NO SPACE ALLOWED). You need simplify with GetTheResult, TheResult, Result, FlowAB. For this example, I give GetTheResult.

Prepare the column for adding the result. From our Flow, we will save Name and Age, I added three more rows for Date, Channel, and ID that can get from RapidPro.


Define each column using right click, more you have column, more you need to define. Make sure you highlight the column to make sure the range is all column. Example: Sheet1!A:A (for date). Do the same for other columns and will look like below.




3. Scripting time! 


Get the Script on Board! This is the final step that will connect your Google spreadsheet and your flow, please follow the step carefully. Go to your spreadsheet, click Tools → Script Editor




Name your project! Same as the Googlesheet filename, please name it without space, recommended if you name it with the same file name with your google sheet.


Copy paste this Script:

function doPost(request) {

var sheets = SpreadsheetApp.openById('YourGoogleSheetID');

var response = request

function nextRow(sheets) {

var firstColumn = sheets.getRangeByName('date').getValues();

for (cell in firstColumn) {

if(firstColumn[cell][0] == "") {

return Number(cell);

break;

}

}

}

/* This is the script to receive variable from RapidPro */

var nextRow = nextRow(sheets) + 1;

var date = request.parameters.date;

var channel = request.parameters.channel;

var id = request.parameters.id;

var age = request.parameters.age;

var name=request.parameters.name;


/* This is to put the value into your GoogleSheet */

sheets.getRangeByName('date').getCell(nextRow,1).setValue(date);

sheets.getRangeByName('channel').getCell(nextRow,1).setValue(channel);

sheets.getRangeByName('id').getCell(nextRow,1).setValue(id);

sheets.getRangeByName('age').getCell(nextRow,1).setValue(age);

sheets.getRangeByName('name').getCell(nextRow,1).setValue(name);

}


Where to Find Google Sheet ID? You can find in the address browser of your Google Sheet. For my example is: 1ABc31nJYlROJ-8H2XhDOIMuxUTbcrIPT088CYL2aY_w


Replace the text Google Sheet ID with your ID.

Deploy your web app: If you finish with the script, publish it. Go to Publish → Deploy as web app. Setting the option as below.


Authorization Required, click on Review Permission, make sure the name of project stated there.


Choose your account:

I don't know why the browser chooses to show this, but, all you need to do is click Advance → Click GoTo GetTheResult. (or any name that you already decided in your project name)

Type Continue:

Click Allow:

Your Google API is ready!

https://script.google.com/macros/s/AKfycbxIy4DNdO8jyL6GQF9vafeQ67T4YlDj86p9uBk

Qdx69_oMWcPcP/exec



Now you need to add some parameters behind your API. From our sample, you need to add id, date, channel, name and age. (id=@contact.id&date=@date.now&channel=@channel.name&name=@flow.name&age=@ flow.age.text). The script will be like this:


Put the script in the web hook! Open your Flow, click on the call Webhook, set the Call URL as POST, and Put URL above into your URL Column. Test your Flow with Simulator.


If your flow works, the result will come up instantly to your Google Sheet.

I suggest you use Comment for editing the parameters, it’s quite easy to read and clear.

Happy days! Don’t hesitate to contact me @ isuwancita@gmail.com if you have any questions.

End of Script


Tags