Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Apps Script example for handling custom forms
var sheetID = "xxx-id-goes-here-xxx";
var rowConfig = "timestamp name location favorite note lifespan season contact lat lng zone approve feature".split(" ");
/***
Requests may come in with the following parameters:
name
favorite - player name (number?)
note
location - geocodable location
Resulting in the following derived fields
lat - (will be rounded to nearest .01 degrees)
lng - see lat
zone - state or country for heatmapping
approve - flags them for inclusion on the map
feature - flags them for star status
Approval is contingent on a flag to give us some editorial leeway. Truly offensive stuff can just be deleted.
*/
//maps a parameter hash to a row in the spreadsheet according to column names
var rowMapper = function(data) {
var row = [];
for (var key in data) {
var index = rowConfig.indexOf(key);
if (index > -1) {
var value;
if (key in data) {
value = data[key];
} else {
value = "";
}
row[index] = data[key];
}
}
for (var i = 0; i < row.length; i++) {
if (typeof row[i] == "undefined") {
row[i] = "";
}
}
return row;
}
//called on web request
function doGet(e) {
if (!e && !e.parameter) return;
//lock the sheet
var lock = LockService.getPublicLock();
lock.tryLock(30 * 1000);
var sheet = SpreadsheetApp.openById(sheetID).getActiveSheet();
var params = process(e.parameter);
var row = rowMapper(params);
sheet.appendRow(row);
lock.releaseLock();
//return JSONP if given callback, otherwise plain JSON
var output = ContentService.createTextOutput();
var rowContents = JSON.stringify(row);
if (params.callback) {
output.setContent(params.callback + "(" + rowContents + ")");
output.setMimeType(ContentService.MimeType.JAVASCRIPT);
} else {
output.setContent(rowContents);
output.setMimeType(ContentService.MimeType.JSON);
}
return output;
}
function buildAddress(result) {
var address = {};
result.address_components.forEach(function(c) {
var key = c.types[0];
var val = c.long_name;
address[key] = val;
});
return address;
}
function process(params) {
params.timestamp = Date.now();
//prefer GPS to geocoding
var geocoder = Maps.newGeocoder();
var rateLimited = false;
if (params.gps) {
var latlng = params.gps.split(",");
params.lat = latlng[0];
params.lng = latlng[1];
try {
var reverse = geocoder.reverseGeocode(params.lat, params.lng).results.shift();
var address = buildAddress(reverse);
if (address.country == "United States") {
params.zone = address.administrative_area_level_1;
} else {
params.zone = address.country;
}
} catch(e) {
Logger.log("Geocoding error: " + JSON.stringify(e));
}
} else if (params.location) {
try {
var geocoded = geocoder.geocode(params.location);
var result = geocoded.results.shift()
var loc = result.geometry.location;
params.lat = loc.lat;
params.lng = loc.lng;
var address = buildAddress(result);
if (address.country == "United States") {
params.zone = address.administrative_area_level_1;
} else {
params.zone = address.country;
}
} catch (e) {
Logger.log("Geocoding error: " + JSON.stringify(e));
}
}
return params;
}
function doPost(e) {
return doGet(e);
}
//test function, so you don't have to deploy each time
function test() {
doGet({
parameter: {
name: "Thomas",
gps: "47,-122",
location: "beijing, china",
note: "hello, world",
favorite: 3
}
});
}
//fill function to add random people to the sheet, so that we can test lots of points
function fill() {
var sheet = SpreadsheetApp.openById(sheetID).getActiveSheet();
var count = 1000;
var firstNames = ["Alice", "Bob", "Charles", "Dawn", "Erin", "Fred", "Gwen", "Harry"];
var lastNames = ["I.", "J.", "K.", "L.", "M.", "N."];
var getRandom = function(arr) { return arr[Math.floor(Math.random() * arr.length)] };
for (var i = 0; i < count; i++) {
var position = Math.PI + (Math.PI / 4) - (Math.random() * Math.PI * .75);
var distance = 5 * Math.random() + 7;
var params = {
timestamp: Date.now(),
name: getRandom(firstNames) + " " + getRandom(lastNames),
location: "Nowhere, USA",
lifespan: 4,
season: Math.random() > .5 ? true : "",
lat: 43.1 - (distance * Math.sin(position)),
lng: -111 + (distance * Math.cos(position)),
favorite: Math.round(Math.random() * 90),
note: Utilities.base64Encode(Utilities.computeDigest(
Utilities.DigestAlgorithm.MD5,
Math.round(Math.random() * 100000000) + "",
Utilities.Charset.US_ASCII
))
};
params.gps = params.lat + "," + params.lng;
process(params);
var row = rowMapper(params);
sheet.appendRow(row);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment