Blog

Hacking Google Forms to Auto-generate Clever Project Titles

Hacking Google Forms to Auto-generate Clever Project Titles

At Discuss.io, the Dev-Team is a cauldron, a hot pot, a ground-zero for solving real-world problems, both those intrinsic to our programmatic approach to market research, and to the the problems posed by business users and processes.

We like to “solve” productivity. So, despite our focus on providing an innovative online qualitative research platform using cutting edge real-time web technologies, sometimes we get into the nitty-gritty details of devising a custom solution to make our operations and internal work flow more smoothly.

The Problem

When our founder wanted to automate the way projects are requested by our clients, we decided that a quick solution that was both easy to implement and efficient would be best.

(So that we had even more time to build the “cool” part of our tool!)

The Solution

We began with a basic set of ingredients. We were already using Google Forms to collect the initial data: client contact information and some basic parameters about their ask. And eventually, that information (and a few meta-data details) needed to end up into our Salesforce CRM.

A basic Google Form, in the screenshot at right, shows how easy it is to have a client enter project information.

The path from there, however, could be difficult and convoluted; how do we connect Google Forms with Salesforce?

 Screen Shot 2016-04-21 at 16.39.43
We wanted to stay away from solutions that would require manual export and import. That’s when, after googling a bit, we realized we could convey the resulting submissions, housed on single rows, to our Salesforce using Zapier.

(More about this on Zapier)

Screen Shot 2016-04-21 at 16.45.09

The (Sub) Problem

As we looked at the resulting opportunities, we found it difficult to discern the differences among them–our naming conventions didn’t help us to differentiate projects, especially if many were submitted by the same person.

After a bit of discussion around how to identify these projects and save us work at the same time, we decided that it would be necessary to assign project IDs to each new submission and send that along to Zapier as the “Opportunity” name in Salesforce.

The 90-Minute Hack

Idea: Every time the form is submitted, add an unique identifier in a specific column.
HackID = form row

Googling led us, yet again to StackExchange

Google forms lets us do this by adding a script trigger.

Suppose your form’s responses sheet has two columns. Columns A and B are then populated with data from previous responses: Timestamp and the answer to one question. You currently populate columns A and B with data. Let’s also assume that column C should automatically count forward in order to provide an indexical value.

  1. Summon the Script Editor: Tools > Script Editor
  2. Enter the following:
    function onFormSubmit(e) {
         var sheet = SpreadsheetApp.getActiveSheet();
         var row = SpreadsheetApp.getActiveSheet().getLastRow();
         sheet.getRange(row,3).setValue(row); }
  3. Save the script and then go to the Triggers menu and select Current script's triggers
  4. Populate and save the drop downs as below:Google Script Triggers

Now when your form is submitted it will populate the row number in column C along with the data that has been submitted via your form.

In you want to change the column that the row number gets saved to you need to change this line of the script:

sheet.getRange(row,3).setValue(row);

and change the value 3 to the corresponding column index number.

This solution worked great until somebody updated the google form to add a few fields…

This knocked off our column index number. In addition, the numerical ID was difficult to identify as our OPS team scrolled through the hundreds of Salesforce opportunities for the many projects we complete each month. We needed something better…

The Better (60-Minute) Hack

Better Idea: Automatically Generate Clever Names for Each Project.
Better Hack: Auto-detect the last column in the spreadsheet and fill it with a randomly generated pair of adjective-animal project names.

Screen Shot 2016-04-08 at 15.46.06First, we created another sheet in the spreadsheet to hold a two-column list, right:

We used RandomLists Nouns and Adjectives generator to make a few hundred. Zubrag‘s HTML Tags Stripper made it easy to pull these out of the code (no need to write your own parser)!

With a couple of hints from StackOverflow to generate random numbers with Google AppScript, we had ourselves some working code:

function generateProjectID(e) {

var sheet = SpreadsheetApp.getActiveSheet();
var row = SpreadsheetApp.getActiveSheet().getLastRow();

sheet.getRange(row,19).setValue(generatePhrase());

}

function generatePhrase() {

// Opens SS by its ID
var ss = SpreadsheetApp.openById("1h6hcvWI-ilhx0sHwXg3VLgDer-6ClvZgw6QqA49EsNw");

var sheet = ss.getSheetByName('Project Phrases');
// var range = sheet.getActiveRange();

// depends on both columns being the same height of 1000
var number1 = Math.floor(Math.random() * 876) + 1; // 877 adjectives
var cell1 = sheet.getRange(number1,1);
var adjective = cell1.getValue();

var number2 = Math.floor(Math.random() * 876) + 1; // 877 nouns
var cell2 = sheet.getRange(number2,2);
var noun = cell2.getValue();

var phrase = adjective + "-" + noun;
return phrase;

}

After running this by the OPS team, we made some minor improvements to the naming conventions (we now use animals instead of nouns) and adding a function to check for duplicate entries.

We finally had our final (magical) code:

function generateProjectID(e) {

var sheet = SpreadsheetApp.getActiveSheet();
var row = SpreadsheetApp.getActiveSheet().getLastRow();

var projectID = getPhrase(sheet);
sheet.getRange(row,sheet.getLastColumn()).setValue(projectID);
// Logger.log(data)

}

function getPhrase(sheet) {
phrase = generatePhrase();
var data = sheet.getDataRange().getValues(); // read all data in the sheet
var col = sheet.getLastColumn()-1;
for(n=0;n<data.length;++n){ // iterate row by row and examine data in opportunity column
if(data[n][col].toString().match(phrase)){
phrase = generatePhrase();
}; // if column contains phrase, generate a new phrase
}
return phrase;
}

function generatePhrase() {

// Opens SS by its ID
var ss = SpreadsheetApp.openById("1h6hcvWI-ilhx0sHwXg3VLgDer-6ClvZgw6QqA49EsNw");

var sheet = ss.getSheetByName('Project Phrases');
// var range = sheet.getActiveRange();

var number1 = Math.floor(Math.random() * 876) + 1; // 877 = number of options in 1st column
var cell1 = sheet.getRange(number1,1);
var adjective = cell1.getValue();

var number2 = Math.floor(Math.random() * 459) + 1; // 459 = number of options in 2nd column
var cell2 = sheet.getRange(number2,2);
var noun = cell2.getValue();

var phrase = adjective + " " + noun ;
//var phrase = adjective + " " + noun + " (" + cell2.getLastRow() + ")" ;
return phrase;
}

We successfully hybridized programmatic efficiency with memorable and unique identifiers. With this code, we can now easily identify projects internally from the time a client requests it all the way through its successful execution!

Discuss ❤ Automation