Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Last active Dec 17, 2015
Embed
What would you like to do?
Color World: Collect data from Google Form and insert into CartoDB

Steps for building a google form + cartodb table to collect data

Figure out what data you want to collect. I'm going to collect

  • What color people like (color)
  • Where they are located (location)

Create a table to store the results

  • Create blank table with columns for color and location

Create a form to collect data

  • docs.google.com
  • select Create new Form
  • create form
  • store results in a new spreadsheet

Go to the new spreadsheet

  • Tools
  • Script editor
  • Create a script for => Spreadsheet

Edit script

  • rename to CartoDBColors
  • use the attached set of functions
  • add trigger on onFormSubmit for 'on form submit'

Share your form with the world!

  • enjoy data collection straight into CartoDB :)
#color_world {
   marker-fill: transparent;
   marker-opacity: 0.5;
   marker-width: 33;
   marker-line-width: 0;
   marker-line-opacity: 0.9;
   marker-placement: point;
   marker-type: ellipse;marker-allow-overlap: true;
marker-comp-op: multiply;
[zoom < 7] {marker-width: 36;
[zoom < 6] {marker-width: 46;
[zoom < 5] {marker-width: 56;
[zoom < 4] {marker-width: 60;
[zoom < 3] {marker-width: 66;
}}}}}
[named_color='whitesmoke']{ marker-fill: #F5F5F5;}
[named_color='indianred']{ marker-fill: #B0171F;}
[named_color='darkblue']{ marker-fill: #00008B;}
[named_color='mediumaquamarine']{ marker-fill: #66CDAA;}
[named_color='mediumblue']{ marker-fill: #0000CD;}
[named_color='yellowgreen']{ marker-fill: #9ACD32;}
[named_color='aliceblue']{ marker-fill: #F0F8FF;}
[named_color='antiquewhite']{ marker-fill: #FAEBD7;}
[named_color='aqua']{ marker-fill: #00FFFF;}
[named_color='aquamarine']{ marker-fill: #7FFFD4;}
[named_color='azure']{ marker-fill: #F0FFFF;}
[named_color='banana']{ marker-fill: #E3CF57;}
[named_color='beet']{ marker-fill: #8E388E;}
[named_color='beige']{ marker-fill: #F5F5DC;}
[named_color='bisque']{ marker-fill: #FFE4C4;}
[named_color='black']{ marker-fill: #000000;}
[named_color='blanchedalmond']{ marker-fill: #FFEBCD;}
[named_color='blue']{ marker-fill: #0000FF;}
[named_color='blueviolet']{ marker-fill: #8A2BE2;}
[named_color='brick']{ marker-fill: #9C661F;}
[named_color='brightgray']{ marker-fill: #C5C1AA;}
[named_color='brown']{ marker-fill: #A52A2A;}
[named_color='burlywood']{ marker-fill: #DEB887;}
[named_color='burntsienna']{ marker-fill: #8A360F;}
[named_color='burntumber']{ marker-fill: #8A3324;}
[named_color='cadetblue']{ marker-fill: #98F5FF;}
[named_color='cadmiumorange']{ marker-fill: #FF6103;}
[named_color='cadmiumyellow']{ marker-fill: #FF9912;}
[named_color='carrot']{ marker-fill: #ED9121;}
[named_color='chartreuse']{ marker-fill: #7FFF00;}
[named_color='chocolate']{ marker-fill: #D2691E;}
[named_color='cobalt']{ marker-fill: #3D59AB;}
[named_color='cobaltgreen']{ marker-fill: #3D9140;}
[named_color='coldgrey']{ marker-fill: #808A87;}
[named_color='coral']{ marker-fill: #FF7F50;}
[named_color='cornflowerblue']{ marker-fill: #6495ED;}
[named_color='cornsilk']{ marker-fill: #FFF8DC;}
[named_color='crimson']{ marker-fill: #DC143C;}
[named_color='cyan']{ marker-fill: #00EEEE;}
[named_color='darkcyan']{ marker-fill: #008B8B;}
[named_color='darkgoldenrod']{ marker-fill: #B8860B;}
[named_color='darkgray']{ marker-fill: #555555;}
[named_color='darkgreen']{ marker-fill: #006400;}
[named_color='darkkhaki']{ marker-fill: #BDB76B;}
[named_color='darkmagenta']{ marker-fill: #8B008B;}
[named_color='darkolivegreen']{ marker-fill: #CAFF70;}
[named_color='darkorange']{ marker-fill: #FF8C00;}
[named_color='darkorchid']{ marker-fill: #9932CC;}
[named_color='darkred']{ marker-fill: #8B0000;}
[named_color='darksalmon']{ marker-fill: #E9967A;}
[named_color='darkseagreen']{ marker-fill: #8FBC8F;}
[named_color='darkslateblue']{ marker-fill: #483D8B;}
[named_color='darkslategray']{ marker-fill: #2F4F4F;}
[named_color='darkturquoise']{ marker-fill: #00CED1;}
[named_color='darkviolet']{ marker-fill: #9400D3;}
[named_color='deeppink']{ marker-fill: #FF1493;}
[named_color='deepskyblue']{ marker-fill: #00BFFF;}
[named_color='dimgray']{ marker-fill: #696969;}
[named_color='dodgerblue']{ marker-fill: #1E90FF;}
[named_color='eggshell']{ marker-fill: #FCE6C9;}
[named_color='emeraldgreen']{ marker-fill: #00C957;}
[named_color='firebrick']{ marker-fill: #B22222;}
[named_color='flesh']{ marker-fill: #FF7D40;}
[named_color='floralwhite']{ marker-fill: #FFFAF0;}
[named_color='forestgreen']{ marker-fill: #228B22;}
[named_color='fuchsia']{ marker-fill: #FF00FF;}
[named_color='gainsboro']{ marker-fill: #DCDCDC;}
[named_color='ghostwhite']{ marker-fill: #F8F8FF;}
[named_color='gold']{ marker-fill: #FFD700;}
[named_color='goldenrod']{ marker-fill: #DAA520;}
[named_color='gray']{ marker-fill: #808080;}
[named_color='green']{ marker-fill: #008000;}
[named_color='greenyellow']{ marker-fill: #ADFF2F;}
[named_color='honeydew']{ marker-fill: #F0FFF0;}
[named_color='hotpink']{ marker-fill: #FF69B4;}
[named_color='indianred']{ marker-fill: #CD5C5C;}
[named_color='indigo']{ marker-fill: #4B0082;}
[named_color='ivory']{ marker-fill: #FFFFF0;}
[named_color='ivoryblack']{ marker-fill: #292421;}
[named_color='khaki']{ marker-fill: #FFF68F;}
[named_color='khaki']{ marker-fill: #F0E68C;}
[named_color='lavender']{ marker-fill: #E6E6FA;}
[named_color='lavenderblush']{ marker-fill: #FFF0F5;}
[named_color='lawngreen']{ marker-fill: #7CFC00;}
[named_color='lemonchiffon']{ marker-fill: #FFFACD;}
[named_color='lightblue']{ marker-fill: #ADD8E6;}
[named_color='lightcoral']{ marker-fill: #F08080;}
[named_color='lightcyan']{ marker-fill: #E0FFFF;}
[named_color='lightgoldenrod']{ marker-fill: #FFEC8B;}
[named_color='lightgoldenrodyellow']{ marker-fill: #FAFAD2;}
[named_color='lightgray']{ marker-fill: #AAAAAA;}
[named_color='lightgreen']{ marker-fill: #90EE90;}
[named_color='lightgrey']{ marker-fill: #D3D3D3;}
[named_color='lightpink']{ marker-fill: #FFB6C1;}
[named_color='lightsalmon']{ marker-fill: #FFA07A;}
[named_color='lightseagreen']{ marker-fill: #20B2AA;}
[named_color='lightskyblue']{ marker-fill: #87CEFA;}
[named_color='lightslateblue']{ marker-fill: #8470FF;}
[named_color='lightslategray']{ marker-fill: #778899;}
[named_color='lightsteelblue']{ marker-fill: #B0C4DE;}
[named_color='lightyellow']{ marker-fill: #FFFFE0;}
[named_color='lime']{ marker-fill: #00FF00;}
[named_color='limegreen']{ marker-fill: #32CD32;}
[named_color='linen']{ marker-fill: #FAF0E6;}
[named_color='magenta']{ marker-fill: #EE00EE;}
[named_color='manganeseblue']{ marker-fill: #03A89E;}
[named_color='maroon']{ marker-fill: #FF34B3;}
[named_color='mediumorchid']{ marker-fill: #BA55D3;}
[named_color='mediumpurple']{ marker-fill: #9370DB;}
[named_color='mediumseagreen']{ marker-fill: #3CB371;}
[named_color='mediumslateblue']{ marker-fill: #7B68EE;}
[named_color='mediumspringgreen']{ marker-fill: #00FA9A;}
[named_color='mediumturquoise']{ marker-fill: #48D1CC;}
[named_color='mediumvioletred']{ marker-fill: #C71585;}
[named_color='melon']{ marker-fill: #E3A869;}
[named_color='midnightblue']{ marker-fill: #191970;}
[named_color='mint']{ marker-fill: #BDFCC9;}
[named_color='mintcream']{ marker-fill: #F5FFFA;}
[named_color='mistyrose']{ marker-fill: #FFE4E1;}
[named_color='moccasin']{ marker-fill: #FFE4B5;}
[named_color='navajowhite']{ marker-fill: #FFDEAD;}
[named_color='navy']{ marker-fill: #000080;}
[named_color='oldlace']{ marker-fill: #FDF5E6;}
[named_color='olive']{ marker-fill: #808000;}
[named_color='olivedrab']{ marker-fill: #6B8E23;}
[named_color='orange']{ marker-fill: #FFA500;}
[named_color='orangered']{ marker-fill: #FF4500;}
[named_color='orchid']{ marker-fill: #DA70D6;}
[named_color='palegoldenrod']{ marker-fill: #EEE8AA;}
[named_color='palegreen']{ marker-fill: #98FB98;}
[named_color='paleturquoise']{ marker-fill: #BBFFFF;}
[named_color='palevioletred']{ marker-fill: #DB7093;}
[named_color='papayawhip']{ marker-fill: #FFEFD5;}
[named_color='peachpuff']{ marker-fill: #FFDAB9;}
[named_color='peacock']{ marker-fill: #33A1C9;}
[named_color='peru']{ marker-fill: #CD853F;}
[named_color='pink']{ marker-fill: #FFC0CB;}
[named_color='plum']{ marker-fill: #FFBBFF;}
[named_color='powderblue']{ marker-fill: #B0E0E6;}
[named_color='purple']{ marker-fill: #800080;}
[named_color='raspberry']{ marker-fill: #872657;}
[named_color='rawsienna']{ marker-fill: #C76114;}
[named_color='red']{ marker-fill: #FF0000;}
[named_color='rosybrown']{ marker-fill: #BC8F8F;}
[named_color='royalblue']{ marker-fill: #4169E1;}
[named_color='saddlebrown']{ marker-fill: #8B4513;}
[named_color='salmon']{ marker-fill: #FF8C69;}
[named_color='sandybrown']{ marker-fill: #F4A460;}
[named_color='sapgreen']{ marker-fill: #308014;}
[named_color='seagreen']{ marker-fill: #54FF9F;}
[named_color='sepia']{ marker-fill: #5E2612;}
[named_color='sienna']{ marker-fill: #A0522D;}
[named_color='silver']{ marker-fill: #C0C0C0;}
[named_color='skyblue']{ marker-fill: #87CEFF;}
[named_color='slateblue']{ marker-fill: #6A5ACD;}
[named_color='slateblue']{ marker-fill: #7171C6;}
[named_color='slategray']{ marker-fill: #708090;}
[named_color='smoke']{ marker-fill: #F5F5F5;}
[named_color='snow']{ marker-fill: #FFFAFA;}
[named_color='springgreen']{ marker-fill: #00FF7F;}
[named_color='steelblue']{ marker-fill: #4682B4;}
[named_color='tan']{ marker-fill: #D2B48C;}
[named_color='teal']{ marker-fill: #008080;}
[named_color='teal']{ marker-fill: #388E8E;}
[named_color='thistle']{ marker-fill: #D8BFD8;}
[named_color='tomato']{ marker-fill: #FF6347;}
[named_color='turquoise']{ marker-fill: #40E0D0;}
[named_color='turquoiseblue']{ marker-fill: #00C78C;}
[named_color='violet']{ marker-fill: #EE82EE;}
[named_color='violetred']{ marker-fill: #FF3E96;}
[named_color='warmgrey']{ marker-fill: #808069;}
[named_color='wheat']{ marker-fill: #F5DEB3;}
[named_color='white']{ marker-fill: #FFFFFF;}
[named_color='yellow']{ marker-fill: #FFFF00;}
 }
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<!--Edit the title of the page-->
<title>CartoDB map of Colors</title>
<meta name="description" content="">
<meta name="author" content="">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v2/themes/css/cartodb.css" />
<!--[if lte IE 8]>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v2/themes/css/cartodb.ie.css" />
<![endif]-->
<!--Switch between the different themes changing the stylesheet below - light-theme.css |dark-theme.css -->
<style>
/* Change the styles below in order to customize your template */
body{font-family: Helvetica, Arial; font-weight: regular; font-size: 15px; color: #555; background-color: #FFF; margin: 0;}
h1{font-weight: bold; font-size: 31px; letter-spacing: -1px; color: #333; line-height: 33px;}
h3{font-weight: bold; font-size: 12px; color: #CCC; text-transform: uppercase; margin: 10px 0 0 0;}
p{margin: 8px 0 20px 0; line-height: 18px;}
a, a:visited{color: #397DB8; text-decoration: none;}
a:hover{text-decoration: underline;}
.wrapper{display: block; padding: 4px 30px 0 30px;}
.map{background-color:#eee; position: absolute; top: 0; left: 0; bottom: 0; width: 67%; *height:100%;}
.sidepanel{background-color:#FFF; position: absolute; top: 0; right: 0; bottom: 0; width: 33%; height: 100%; overflow: auto;}
.context{font-family: Helvetica, Arial; font-size: 13px; color: #999; padding: 10px 0 0 0;}
.subheader{border-bottom: 1px solid #ddd;}
.footer{border-top: 1px solid #ddd; margin-top: 30px;}
.titleBlock{text-align: right;}
/* Here are the styles that makes the template responsive */
@media only screen and (max-width: 768px) {
.map{position: inherit; height: 400px; width: 100%; display: block;}
.sidepanel{position: inherit; width: 100%;}
}
@media only screen and (max-width: 480px) {
.map {height: 300px;}
}
.bar rect {
shape-rendering: crispEdges;
}
.bar text {
text-anchor: end;
}
.axis path,
.axis line {
fill: none;
stroke: #000;
shape-rendering: crispEdges;
}
</style>
<script src="http://libs.cartocdn.com/cartodb.js/v3/cartodb.js"></script>
<script src="http://d3js.org/d3.v3.js"></script>
</head>
<body>
<div class="map" id="map"></div>
<div class="sidepanel">
<div class="wrapper">
<div class="context subheader">
<p>Map created by <a href="http://twitter.com/andrewxhill">andrewxhill</a></p>
</div>
<h1>Rank Colors</h1>
<div id="graph"></div>
<div class="context footer">
<p>Create your maps with ease using <a href="http://cartodb.com">CartoDB</a></p></p>
</div>
</div>
</div>
<!--Change the URL below in order to change the map that is being shown.
Go to your map in CartoDB, click on share, and copy the URL undert the API section
Check the cartodb.js documentation for more info
http://developers.cartodb.com/documentation/cartodb-js.html-->
<script type="text/javascript">
cartodb.createVis('map', 'http://viz2.cartodb.com/api/v1/viz/color_world/viz.json');
var formatPercent = d3.format(".1%");
console.log($("#graph").width())
var margin = {top: 10, right: 30, bottom: 30, left: 30},
width = $("#graph").width() - margin.left - margin.right,
height = 500 - margin.top - margin.bottom;
var data_query = "SELECT named_color, count, hex FROM (SELECT distinct named_color, count(*) as count FROM color_world GROUP BY named_color) a, named_colors WHERE named_color = color ORDER BY count DESC";
var data_host = "http://viz2.cartodb.com/"
var api_route = "api/v2/sql?q="
d3.json(data_host + api_route + encodeURIComponent(data_query), function(error, results) {
var histogram = results.rows;
var n = d3.sum(histogram, function(d) { return d.count; });
var y = d3.scale.linear()
.range([0, height]);
var x = d3.scale.linear()
.domain([0, histogram[0].count])
.range([width, 0]);
var xAxis = d3.svg.axis()
.scale(x)
.orient("bottom");
var svg = d3.select("#graph").append("svg")
.attr("width", width + margin.left + margin.right)
.attr("height", height + margin.top + margin.bottom)
.append("g")
.attr("transform", "translate(" + margin.left + "," + margin.top + ")");
svg.append("g")
.attr("class", "x axis")
.attr("transform", "translate(0," + height + ")")
.call(xAxis);
y.domain([0, d3.max(histogram, function(d) { return d.count; })]);
var bar = svg.insert("g", ".axis")
.attr("class", "bar")
.selectAll("g")
.data(histogram)
.enter().append("g")
.attr("transform", function(d) { return "translate(" + x(d.x) + ",0)"; });
var barw = Math.floor(height/histogram.length);
bar.append("rect")
.attr("class", "a")
.attr("y", function(d,i) { return i*barw; })
.attr("x", function(d) { return x(d.count); })
.attr("fill", function(d) { return d.hex; })
.attr("height", barw-2)
.attr("width", function(d) { return width - x(d.count); });
});
</script>
</body>
</html>
/**
* Read values coming from the form
*/
function onFormSubmission(e) {
Logger.log("data!");
/**
* Use Google services to get coordinates from a locality string
*/
//Georeference the submission
var loc = geocode(e.namedValues.location);
/**
* Use our own function to post to our table
*/
postToCartoDB(
e.namedValues.location[0],
e.namedValues.color[0],
loc.lat,
loc.lng
);
}
/**
* Geocode using Google's services
*/
function geocode(address) {
var response = UrlFetchApp.fetch("http://maps.googleapis.com/maps/api/geocode/json?address="+escape(address)+"&sensor=false");
var respObj=Utilities.jsonParse(response.getContentText());
var loc = {lat:NaN,lng:NaN};
try {
loc = respObj.results[0].geometry.location
} catch(e) {
Logger.log("Error geocoding: "+address);
}
return loc;
}
/**
* Insert color into CartoDB
*/
function postToCartoDB(location,color,latitude,longitude) {
Logger.log("posting to CartoDB");
/**
* Keep your key private!
*/
var cartodb_host = "viz2.cartodb.com"; //Your CartoDB domain
var cartodb_api_key = "#######"; //Your CartoDB API KEY
/**
* Insert NULL as the_geom if no location is provided
*/
var loc = "";
if (latitude && longitude) {
loc = "CDB_LatLng("+latitude+","+longitude+")";
} else {
loc="null";
}
/**
* Remove all single quotes
*/
location = location.replace("'","''");
color = color.replace("'","''");
/**
* Here is the INSERT statement
*/
var query = "INSERT INTO color_world(location,named_color,the_geom) VALUES('"+location+"','"+color.replace(/'/g, "''")+"',"+loc+")";
Logger.log("SQL: "+query);
/**
* Assemble the POST parameters
*/
var options = {
"method" : "post",
"payload" : {q:query,api_key:cartodb_api_key}
};
/**
* Ship It
*/
var response = UrlFetchApp.fetch("https://"+cartodb_host+"/api/v1/sql", options);
var respObj=Utilities.jsonParse(response.getContentText());
Logger.log("CDB call result: "+response.getContentText());
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment