Creating Custom Survey with Google Apps 1

OBJECTIVE

1. Create Survey Data Sheet

2. Create Code to output Survey Data Sheet in JSON Format

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.

1) Create Google Sheet

Name: _survey1

Sheet: groups, questions

Sheet: groups (GREEN = reserved for future use)

Gid	Group	SubGroup	FormId	SheetId
101	PERSONAL ATTRIBUTES	SELF-ESTEEM

Sheet: questions (GREEN = reserved for future use)

Gid	Group	SubGroup	itype	mtype	description	answer	scores
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	t	0	To what extend do the statements below describe you?
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	a	1. In general, you are satisfied with yourself.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	b	2. At times you think you are no good at all.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	a	3. You feel that you have a number of good qualities.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	a	4. You can do things as well as most other people.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	b	5. You feel you do not have much to be proud of.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	b	6. You feel useless at times.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	a	7. You feel that you are at least as good as other people.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	b	8. You wish you could have more respect for yourself.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	b	9. Sometimes you think of yourself as a bad person.
101	PERSONAL ATTRIBUTES	SELF-ESTEEM	q	a	10. You take a positive attitude toward yourself.

2) Add Code Scripts

2.1) Setup Function

//get required object
var ss = SpreadsheetApp.getActiveSpreadsheet();//current spreadsheet
var SSID = ss.getId(); //SSID
var SSDOC = DriveApp.getFileById(SSID);
var Folders = SSDOC.getParents();
var Folder = Folders.next();
var FOLDERID = Folder.getId(); //FOLDERID

function showId(){
Logger.log("SSID:"+SSID);
Logger.log("FOLDERID:"+FOLDERID);
}

Run the function.

1. Select the function name, showid

2. Click Run button

Select View/Logs.

Logs should display the ID of the required objects that may be needed later on.

2.2) Response Handling Function

//get required object

var ss = SpreadsheetApp.getActiveSpreadsheet();//current spreadsheet

var SSID = ss.getId(); //SSID

var SSDOC = DriveApp.getFileById(SSID);

var Folders = SSDOC.getParents();

var Folder = Folders.next();

var FOLDERID = Folder.getId(); //FOLDERID

function showId(){

Logger.log("SSID:"+SSID);

Logger.log("FOLDERID:"+FOLDERID);

}

function doGet(e) {

return handleResponse(e);

}

function doPost(e) {

return handleResponse(e);

}

function handleResponse(e) {

var lock = LockService.getPublicLock();

lock.waitLock(30000); // wait 30 seconds before conceding defeat.

try {

var action = e.parameter.action;

if (action == 'getsheet') {

return getSheet(e);

}

} catch (e) {

// if error return this

return ContentService

.createTextOutput(JSON.stringify({

"result": "error",

"error": e

}))

.setMimeType(ContentService.MimeType.JSON);

} finally { //release lock

lock.releaseLock();

}

function getSheet(e){

var data =[];

//check if parameter is undefined

if ((typeof(e) === "undefined")||

(typeof(e.parameter) === "undefined")||

(typeof(e.parameter.sheet) === "undefined")) {

data.push({'sheet':'undefined'});

}

//else, process request parameter values

else {

var SHEETNAME=e.parameter.sheet;

data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEETNAME).getDataRange().getValues();

}

return ContentService

.createTextOutput(JSON.stringify({

"result": "success",

"values": data

}))

.setMimeType(ContentService.MimeType.JSON);

}

Run the function getSheetQuestions.

Select menu View/Execution Transcript

2.3) Deploy Code as Web App

Call the URL via web browser,

Invalid URL parameter values will get the following response.

Eg: <<Your Web App URL>>

{"result":"success","values":[{"sheet":"undefined"}]}

Valid URL parameter values will get the following response.

Eg: <<Your Web App URL>>?action=getsheet&sheet=questions

{"result":"success","values":[["Gid","Group","SubGroup","itype","mtype","description","answer","scores"],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","t",0,"To what extend do the statements below describe you?","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","a","1. In general, you are satisfied with yourself.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","b","2. At times you think you are no good at all.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","a","3. You feel that you have a number of good qualities.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","a","4. You can do things as well as most other people.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","b","5. You feel you do not have much to be proud of.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","b","6. You feel useless at times.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","a","7. You feel that you are at least as good as other people.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","b","8. You wish you could have more respect for yourself.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","b","9. Sometimes you think of yourself as a bad person.","",""],[101,"PERSONAL ATTRIBUTES","SELF-ESTEEM","q","a","10. You take a positive attitude toward yourself.","",""]]}