Skip to content

Instantly share code, notes, and snippets.

@fischerbach
Created December 19, 2020 10:02
Show Gist options
  • Save fischerbach/bb07dcd9bac3d10ba01e4ac1b1e648f9 to your computer and use it in GitHub Desktop.
Save fischerbach/bb07dcd9bac3d10ba01e4ac1b1e648f9 to your computer and use it in GitHub Desktop.
Code for article
// Enter sheet name where results will be collected
var SHEET_NAME = "Sheet1";
var CHOICES_SHEET_NAME = "Choices"; //We will use it later
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
//Fetching choice options from spreadsheet
function doGet(e){
return getDataFromSheet();
}
function doPost(e){
return handleResponse(e);
}
function getDataFromSheet() {
try{
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(CHOICES_SHEET_NAME);
var headers = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
var data = {}
for (i in headers) {
var question = headers[i][0];
var value = headers[i][1];
var title = headers[i][2];
if(Array.isArray(data[question])) {
data[question].push({
value: value,
title: title
});
}else {
data[question] = [
{
value: value,
title: title
}
]
}
}
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({"status":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
}
}
function handleResponse(e) {
// The LockService allows you to have only one invocation of the script or portions thereof run at a time.
// More about: http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
var lock = LockService.getPublicLock(); //Public lock locks for any invocation of script.
lock.waitLock(30000);
try {
// Alternatively, you can hard code spreadsheet here
// eg. SpreadsheetApp.openById("1AcsuboS3xxk0kj02ACcE_j4ASb8GrxyZscTU5IM-wqc")
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var data = JSON.parse(e.postData.contents);
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
for (i in headers){
if (headers[i] == "datetime"){
row.push(new Date());
}
else if (headers[i] == "raw_data"){
row.push(JSON.stringify(data));
}
else {
//To support multiple choice question (checkboxes)
if(Array.isArray(data[headers[i]])) {
row.push(data[headers[i]].join('|'));
}else {
row.push(data[headers[i]]);
}
//TODO: Support other types of questions
}
}
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"status":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch (e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"status":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally {
//Release public lock from line 19
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
@fischerbach
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment