Quick Text Analysis with Google Apps Script

Published: 2023-02-15 09:00 |

Category: Code | Tags: gas, google apps script, text, survey, data

We needed to do a survey at work that included some open ended questions. We don't have access to a survey platform like Qualtrics, so we have to use Google Forms (generally) and then roll our own stats dashboard. Getting charts is easy, but it's more difficult to summarize those open response items.

I came across a nice text analysis snippet for Google Apps Script which takes an input and ranks words based on parameters you set. In the end, you get list of terms by n-occurances and a relevance score calculated by the frequency of the text in relation to the entire sample. It's not Gospel but it's one of the better solutions I've found for quick text analysis in Google Forms surveys.

You'll need a Google Form and its results linked to a spreadsheet. Once you have that done, open your sheet and then click on Extensions > Apps Script.

Copy the script from the gist linked above and paste it into a file. I like to keep mine separate and since this is quick and dirty, we can use the global namespace to call the function.

Then, start your own script file and paste the following:

// Set some globals to get your sheet data.
// I created a new sheet called `Charts` to hold the results
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Form Responses 1');
const stats = ss.getSheetByName('Charts');


// This function gets the text from the column as responses in an array.
function programFreq() {
  const col = sheet.getRange(1, 7, sheet.getLastRow(), 1).getValues()

  // The ngram script is called directly because the function is in the global namespace.
  // Param 1 is your input text. Params 2 - 4 set options for results:
  // - minimum number of occurrences
  // - word groupings length (from 1 - 3 words in this example)
  // - remove stop words (see line 100 of the ngram script)
  let table = KEYWORD_FREQUENCY_TABLE(col, 3, 3, true);

  // clear the original table location
  stats.getRange(1,1,stats.getLastRow(),3).clear();

  // Set a header
  stats.getRange(1, 1).setValue('Program Use Frequency')

  // Write the new table to the sheet.
  stats.getRange(2, 1, table.length, table[0].length).setValues(table)
}

After the script runs, you get a table similar to this:

Again, this could definitely be cleaned up, but it's a helpful method for quickly getting lists of common terms in those pesky free-response items.