Created
          December 19, 2020 10:02 
        
      - 
      
- 
        Save fischerbach/bb07dcd9bac3d10ba01e4ac1b1e648f9 to your computer and use it in GitHub Desktop. 
    Code for article
  
        
  
    
      This file contains hidden or 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
    
  
  
    
  | // 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()); | |
| } | 
  
    Sign up for free
    to join this conversation on GitHub.
    Already have an account?
    Sign in to comment
  
            
Gist for the article:
How to create online survey for free with SurveyJS and Google Sheets