Send Data to Google Sheet With Google Apps Script
- Author:
- Steve Tsang
Step by Step Tutorial for Setting up the Google Sheets, Google Apps Script and Geogebra's JavaScript
The steps below roughly follow
http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/
1. Setup the Google Sheets
(a) Go to https://drive.google.com, click "NEW" and choose "Google Sheets".
(b) Put the following in the first 7 cells in the first row. You can change the names and the number of items later. These names need to be matched in the JavaScript of the Geogebra file in Step 3. The first cell is used for recording the timestamp.
| Timestamp | id | Class | Class Number | x-coord | y-coord | |
2. Setup the Google Apps Script
(a) Click "Tools" and choose "Script editor...".
(b) Replace the existing script with the following (which is modified from https://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/):
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
Logger.log(e.parameter);
// Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
var output = "";
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
output = JSON.stringify({"result":"success", "row": nextRow});
if (e.parameter.callback){
// return jsonp success results
return ContentService
.createTextOutput(e.parameter.callback+"("+ output + ");")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
else{
// return jsonp success results
return ContentService
.createTextOutput(output)
.setMimeType(ContentService.MimeType.JSON);
}
} catch(e){
output = JSON.stringify({"result":"error", "error": e});
if (e.parameter.callback){
// if error return this, again, in jsonp
return ContentService
.createTextOutput(e.parameter.callback+"("+ output + ");")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
else{
return ContentService
.createTextOutput(output)
.setMimeType(ContentService.MimeType.JSON);
}
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
(c) Then, click the "Save" icon, or click "File", then choose "Save".
(d) Click "Run", then choose "setup".
(e) A dialog of "Authorization required" pops up. Click "Review Permissions". Click "Allow" in the next dialog.
(f) Click "Publish", then choose "Deploy as web app...".
(g) In the popup, choose "New" in "Project version:", then type something about this version in the text box below it. For "Execute the app as:", choose "Me". For "Who has access to the app:", choose "Anyone, even anonymous". Then Click "deploy".
(h) Copy the "Current web app URL", e.g. like https://script.google.com/macros/s/SOME_LONG_KEY_HERE/exec
(i) You can test your script by copy and paste the URL, plus the following without the quotes
"?Name=test&Class=1A&Class%20Number=18&x-coord=1&y-coord=2&id=app_001"
e.g. like
https://script.google.com/macros/s/SOME_LONG_KEY_HERE/exec?Name=test&Class=1A&Class%20Number=18&x-coord=1&y-coord=2&id=app_001
This basically sends a GET request with "Name" = test, Class = 1A, Class Number = 18, x-coord = 1, y-coord=2 and id = app_001. The webpage will show something like "{"result":"success","row":14}", which means the data is successfully added to row 14 of the Google Sheet and you then check the Sheet for this new row.3. Create a geogebra app to send data to the Google Sheet
(a) You can download the above geogebra file or create a new one. Assume that you have a geogebra file and you want to submit the x, y coordinates of a point, as well as the class and class number of a student. Let's assume that these are stored in variables ax, ay, class and classNum.
(b) Create a new button. Right click it and choose "Object Properties...". Choose "Scripting", then choose "On Click". Then paste the following:
var class0 = ggbApplet.getValueString("class"); // cannot name a variable as 'class'
var classNum = ggbApplet.getValueString("classNum");
var ax = ggbApplet.getValue("ax");
var ay = ggbApplet.getValue("ay");
if (confirm("Confirm to submit?\nClass: " + class0 + ", class num: "
+ classNum + ", point A = ("+ax+", "+ay+")" )) {
sendData(class0, classNum, ax, ay);
}
(c) Choose "JavaScript" instead of "Geogebra Script". Then click "OK".
(d) Choose "Global JavaScript". Then paste the following code. (Note that in lines 7 to 11, the names in quotes, i.e. "id", "Class", "ClassNumber", "x-coord" and "y-coord" must exactly match (case sensitive) the names in the header row of the Google Sheet, otherwise, those non-matching cells will contain "undefined" as their value.
function sendData(class0, num, x, y) {
var scriptURL = "https://script.google.com/macros/s/SOME_LONG_KEY/exec";
var app_name = "shortest distance";
if (num!=="") {
// the name "id", "Class", "Class Number", "x-coord", "y-coord" must match the header row in the spreadsheet
jQuery.ajax({url:scriptURL, data: {
"id":app_name,
"Class":class0,
"Class Number":num,
"x-coord":x,
"y-coord":y
},
dataType: "jsonp", // using jsonp to overcome the Same Origin Policy
jsonp: "callback",
success: function( response ) {
if (response.result == "success"){
console.log("added to row " + response.row);
alert("Successfully submitted");
}
else{
alert("Error: " + response.result);
}
}
});
}
else {
alert("Please input the class and class number");}
}
(e) In the second line of the code, replace the URL in quotes with the URL obtained in step 2(h).
(f) Click "OK". Then close it.
(g) Click "File", then choose "Export", and choose "Dynamic Worksheet as Webpage", then following the instruction to upload to Geogebratube. Now you can try the geogebra worksheet.
References
[1] The Original Tutorial for setting up Google Script for the Spreadsheet
https://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
[2] A step-by-step tutorial based on the above one
http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/
[3] Another way to send data to Google Sheet/Google Form by Mr. Alex Chik
https://www.geogebra.org/m/eMBf6HAu
[4] Using jonsp to get the response
https://developers.google.com/apps-script/guides/content#serving_jsonp_in_web_pages