Combining address fields into a Google Spreadsheet from a form

I was asked to help out with a project, involving a form-to-map website idea. This was to be based on a Google Form, feeding a Google Spreadsheet. This was then pushed to a Google Fusion table and eventually consumed in a WordPress app.

The form collected address data, and the first hurdle was that the data was in several fields (the fusion table expected one for geocoding). After they showed me what they needed I immediately started googling (as you do) to see what had been done before.

Fairly quickly I found that a) Google Scripts use javascript and b) you could connect a script to a spreadsheet and trigger it from a form submit. However much of the code I saw online used the e.values[i] approach, and I was using e.namedValues['name']. Once I tracked down a comment mentioning that you needed to either use e.namedValues['name'].toString() or e.namedValues['name'][0] it all came together quickly. Chris Keller has a particularly informative post on this sort of thing, so I included my version of his geocoding code below.

The input data is then concatenated to provide a geocodable string. The optional section (currently commented out) does the geocode and returns the lat long values. Either (or both) could be used as desired. This example assumes there is a "Location" column in the spreadsheet for the concatenated string, and a "LatLng" column if you are doing the geocode.

1. Open your spreadsheet, and click Tools, Script Manager. 
2. Make a new script and choose "spreadsheet". 
3. Add the code below, change as desired and save.
4. Click Resources and "Current Projects Triggers".
5. Add a new trigger as below, with 'concatAddress' under Run, 'From Spreadsheet' under Events and 'On form submit' at the end. You can edit the notification if you like.
6. Give it permission if requested.
7. Save that, and you should be done.

Trigger properties. When the form is submitted, concatAddress is run.

Code:

//concatenate address data and add it to a 'Location' column

function concatAddress(e) {

  var sheet = SpreadsheetApp.getActiveSheet(); //get the sheet

  var lastRow = sheet.getLastRow(); //get the last row (this is the newly added one)

  //fields to concatenate

  var address1 = e.namedValues["Address"].toString();

  var city = e.namedValues["City"].toString();

  var state = e.namedValues["State"].toString();



  //join the fields into single string, with delimiters

  var geocodeString = address1 + ", " + city + ", " + state;



  //update the value in the appropriate column

  sheet.getRange(lastRow, getColIndexByName("Location")).setValue(geocodeString);



  //we can also geocode the result directly into another column

  // uncomment this to do so

  /*

  var results = Maps.newGeocoder().geocode(geocodeString);

  if (results.status == 'OK') {

    var bestResult = results.results[0];

    var lat = bestResult.geometry.location.lat;

    var lng = bestResult.geometry.location.lng;

    var latLng = lat + ',' + lng;

    sheet.getRange(lastRow, getColIndexByName("LatLng")).setValue(latLng);

  }

  */



};

// look up the column index by supplied name

function getColIndexByName(colName) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var numColumns = sheet.getLastColumn();

  var row = sheet.getRange(1, 1, 1, numColumns).getValues();

  for (i in row[0]) {

    var name = row[0][i];

    if (name == colName) {

      return parseInt(i) + 1;

    }

  }

  return -1;

};