Created
July 15, 2014 19:55
-
-
Save zhenyanghua/d5e153aad185e778c6c3 to your computer and use it in GitHub Desktop.
Googlemaps + Google Sheets + Google Forms
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var SPREADSHEET_ID='Google Sheets ID'; | |
| var SHEET_NAME = 'Google Sheets Table Name'; | |
| function doGet(request) { | |
| var callback = request.parameters.jsonp; | |
| var range = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME).getDataRange(); | |
| var json = callback+'('+Utilities.jsonStringify(range.getValues())+')'; | |
| return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JAVASCRIPT); | |
| } |
We can make this file beautiful and searchable if this error is corrected: It looks like row 2 should actually have 2 columns, instead of 3. in line 1.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Timestamp Place Name Address | |
| 7/15/2014 10:29:43 home Salem,MA | |
| 7/15/2014 11:06:02 Nearby Beverly,MA | |
| 7/15/2014 11:23:44 Big City Boston,MA | |
| 7/15/2014 11:35:20 Many Auto shops Peabody,MA | |
| 7/15/2014 11:37:04 Autos.. Danvers,MA | |
| 7/15/2014 12:22:18 Commute Wonderland Station, MA |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <title>Googlemaps + Google Sheets + Google Forms</title> | |
| <style> | |
| html,body{ | |
| margin:0; | |
| padding:0; | |
| width:100%; | |
| height:100%; | |
| } | |
| #map_canvas{ | |
| float:left; | |
| width:50%; | |
| height:100%; | |
| } | |
| #panel{ | |
| float:right; | |
| width:50%; | |
| height:100%; | |
| background-color:#000; | |
| } | |
| #gform{ | |
| height: calc(100% - 22px); | |
| } | |
| </style> | |
| </head> | |
| <script src="https://maps.google.com/maps/api/js?sensor=false"></script> | |
| <script> | |
| var DATA_SERVICE_URL="https://script.google.com/macros/s/AKfycbxHrqv5x-2lCtZJ1W49q0rjU6ATnSLZWAtJADqBj1Kil32H80pL/exec?jsonp=callback"; | |
| var geocoder; | |
| var map; | |
| function initialize() { | |
| //Create a geocoder | |
| geocoder = new google.maps.Geocoder(); | |
| map=new google.maps.Map(document.getElementById('map_canvas'),{ | |
| center:new google.maps.LatLng(42.456187,-71.0653), | |
| zoom:10, | |
| maxZoom:20, | |
| mapTypeId:google.maps.MapTypeId.ROADMAP | |
| }); | |
| //Inject JavaScript (returned JSON) into the head of the page. | |
| var scriptElement=document.createElement('script'); | |
| scriptElement.src=DATA_SERVICE_URL; | |
| document.getElementsByTagName('head')[0].appendChild(scriptElement); | |
| } | |
| function callback(data) { | |
| for (var i=1;i<data.length;i++) { | |
| address=data[i][2]; | |
| //Geocode the JSON returned from callback function. | |
| codeAddress(); | |
| } | |
| } | |
| function codeAddress(){ | |
| //Google Async service. | |
| geocoder.geocode({'address':address},function(results,status){ | |
| if (status == google.maps.GeocoderStatus.OK) { | |
| var marker = new google.maps.Marker({ | |
| position:results[0].geometry.location, | |
| map:map | |
| }); | |
| }else{ | |
| alert('Geocode was not successful for the following reason:' + status); | |
| } | |
| }); | |
| } | |
| </script> | |
| <body onload="initialize()"> | |
| <div id="map_canvas"></div> | |
| <div id="panel"> | |
| <div id="ctrl"> | |
| <input type="button" value="Update Map" onclick="document.location.reload(true)"> | |
| </div> | |
| <div id="gform"> | |
| <iframe id="form" src="https://docs.google.com/forms/d/1u0aw5rz6JFfJf0KvjYn900VMOLWlI8yfiQnKysZFHaA/viewform?embedded=true" width="100%" height="100%" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe> | |
| </div> | |
| </div> | |
| </body> | |
| </html> |
Hi, were cesrulidc questions answered? Would love to try this solution! Thank you!
hi
is it possible to get a video tutorial for this great script please
From looking at the code, here's what this actually does:
index.htmlis a web page which includes a map, an 'Update Map' button and an embedded Google Form- The map shows all the locations that have been submitted to the Google form in the past (everyone's responses)
- You can submit another response and click Update Map
- Now your response will be on the map too
This doesn't work without adding a Google Maps API key and creating your own form
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi Sameer, I'm a newbie in Google Forms and i'm trying to figure it out how to make this script work and i have the next questions about your answer in Quora:
*What do you mean with "change the coordinates"?
*Where do I have to paste the provided code, in the Form or in the Spreadsheet?
*Should my Form have a specific layout?
Maybe I'm may asking for a lot, but it's worth taking the shot
Thank you in advance