How To Get Text Messages into Google Form Responses

Continuing on my data collection and analysis streak, I've got a little project to throw out into the wild and see what happens.

Student response systems are expensive and can be clunky. Google Forms are taking over the role of the stand-alone response system because of their flexibility in the wild during collection (device agnostic) and during analysis.

I like using forms, but they don't work so well when students don't have access to an Internet-ready device. So, I came up with a way to allow students (or anyone, really) to respond to a poll using either a form input or a text message.


The challenging part of this is getting the message into the spreadsheet. I have a Google Voice number, so I looked into Apps Scripts for Voice. Unfortunately, there is no way to access Voice using Google Scripts. There is an API for Hangouts, but it's not documented very well and doesn't look like it would even work with Apps Scripts or Spreadsheets. Maybe I'll head back down that road someday, but not right now.

I decided to go with IFTTT, which has some benefits (easy to set up and manage) and drawbacks (only checks every 15 minutes-ish, so you need to manually run it during a session). It's clunky and adds a breakpoint in the flow, but it works.

The Setup

You need to start by making a Google Form. For this, I want to allow students to send in four things: Their name, a multiple choice answer, a confidence rating, and any comments. Create the form with your collection in mind.

In IFTTT, you have to tell the script which cells to populate in the sheet with three straight bars, "|||". Knowing your form setup is really important before you make the IFTTT recipe because it'll save you a headache later. My recipe puts the responses into the first three columns and then I use a Google Apps Script to break them into the correct columns.

The Flow

Method 1 - Pure Google Forms The audience opens the Form and answers the question displayed live. They hit submit.

Easy.

Method 2 - Text Message to IFTTT First, in Voice, you set up email notifications for text messages. Then, in Gmail, set up a filter to catch the emails and do two things: add a label (I used "text") and then mark it as "read" (mostly for your inbox-zero sanity). Now, have the audience send a text to your Google Voice number (see below for formatting).

In IFTTT, set up a trigger which uses Gmail -> Google Drive. The Gmail trigger should do a search for your new Gmail label. When it finds that email, it appends a row to your form input spreadsheet.

2015-04-30_21-38-48

More complicated, but now we have a way to get raw text data into the spreadsheet. Now, you need to make sure the text message is formatted correctly.

Splitting the text

Google forms split fields into their own columns. You can't do that with a text because IFTTT puts the whole message into one cell. To split it, you need a way for a script to find the individual components. For this, I have participants do the following:

Name [comma] MC answer [comma] Confidence (1-4) [comma] comments

IFTTT dumps this into a placeholder cell in the spreadsheet and it can now be broken up at each comma using the following script:

splitsms

function splitCells(from) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var colC = sheet.getRange(2, 3, sheet.getLastRow()).getValues();  // C2:C, only non-blank rows
  //Logger.log(colC);
  for(var i=0; i< colC.length; i++){
    if(colC[i][0] != ''){
      // If we received a SMS response, set a formula to parse it
      sheet.getRange(2+i,4).setValue('=if(istext(C2:C),split(C2:C,",",true),"")')
    }
  }
}

After running this script, you now have all the responses in the correct column and you can play with the data more and look for patterns in the responses.


So, there's the start of the idea. I have a second sheet going, but I'll write that up in a subsequent post. If you'd like to try this yourself and you're having trouble reading my rambles, here's the tl;dr version:

  1. Get a Google Voice number.
  2. Turn on email notices when you receive a text message.
  3. In Gmail, set a filter for your email notifications.
  4. Sign into IFTTT and copy this recipe.
  5. In the spreadsheet, copy and paste the Google Apps Script above, which splits the text message rows into columns, into the script editor. You may need to refresh the sheet to have the menu added to run the script.

I'll follow this up with the data analysis sheet explanation.

15 thoughts on “How To Get Text Messages into Google Form Responses

  1. […] How To Get Text Messages into Google Form Responses | Nodes […]

  2. TomT says:

    Great recipe! Trying to normalize data received by spreadsheet by have an onEdit(e) function check – no to NO, No to NO, etc. It runs great when I’m in the spreadsheet, but when the recipe adds data, it does not fire.

    Any ideas or another way to do it?

    Thanks,
    TomT

    • Brian Bennett says:

      You might need to use a script trigger rather than a trigger in the function alone. I’m not sure how the IFTTT script adds content to the spreadsheet and if it’s using a different method than going into the sheet and typing, if that makes sense.

      So, go into the script editor and click on Resources to add a trigger to fire each time the sheet is updated. You can set a specific script to run when the sheet is edited.

  3. Brenda says:

    I am trying to set up a system where students can send a sms in response to a question and have those responses go to a Google spreadsheet. I have completed all of the steps and am trying to accomplish the last step. I have never worked with script before. The instructions say to copy and paste the script that is listed, but that doesn’t seem to work Is it because it’s a screenshot? How can I accomplish this?
    Thank you

  4. John says:

    @Brenda, the script isn’t a screenshot, but i can see why one might think so. just highlight over the text. any updates, @Brian Bennett ? i’m curious to see how your results came out after this post… thx

  5. Brian Bennett says:

    @John, I worked with @Brenda back in July to get this working. From what I remember, she was able to have her students send texts into the spreadsheet as expected.

  6. Yitzy says:

    Thank you so much! I was looking all over the web for something like this, and this was a lifesaver. I do orders for food like a canteen. I do this in school and kids can’t use smartphones during school so I needed a way for kids to text in their orders. This program works amazingly! Thanks a ton.

  7. Cristian says:

    Hello,

    Im trying to use this method in order to receive information of the condition of the restroom on my workplace but I can’t manage to get the data from my email to the spreadsheet. I have followed all your instructions but have had no luck with it. Any help will be greatly appreciated.

    Thanks,
    Cristian

    • Brian Bennett says:

      Hey Cristian,
      Are you seeing any error messages? Either in the script or in IFTTT? Without more information on what’s going wrong, it’s hard to diagnose the problem and provide more help.

      • Cristian says:

        Thank you for replying,
        I already have the emails appearing at googlesheets. I used zapier instead of IFTTT because it usually works better for me and, in this case, it did. They only problem I have now is that in the spreadsheet the text is not being separated, even if I run the script manually. I received this error message by email, “Script function not found: splitCells”. Again, thank you for taking your time to help me.

  8. Brian says:

    Hi! This article is great! I wanted to know if there was a way to cut off the information after the actual text. I get all the coded stuff, (like your account, help center) in the cell with the body and i want to cut it out. How do I do this?

Leave a Reply

Your email address will not be published. Required fields are marked *