.
Creating Custom Survey with Google Apps 3
OBJECTIVE
1. Perform a loop to generate multiple Survey Forms and their linked Spreadsheets
Add the for each loop control as shown in the highlighted codes below. The object variable grouprow represent each of the rows in the groups sheet.
//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);
}
function createForms(){
var db = objDB.open(SSID);
grouprows = objDB.getRows( db, 'groups' ); //get all rows in the groups sheet
grouprows.forEach(function (grouprow) { //for each row, not including the header row
var Gid=grouprow.Gid;
var filename=grouprow.Group + "~"+ grouprow.SubGroup;
//create the form
var form = FormApp.create(filename);
var formFile = DriveApp.getFileById( form.getId() );
DriveApp.getFolderById(FOLDERID).addFile( formFile );
//create the sheet
var sheet = SpreadsheetApp.create(filename);
var sheetFile = DriveApp.getFileById( sheet.getId() );
DriveApp.getFolderById(FOLDERID).addFile( sheetFile );
/*when we create a document, it will be placed both in the parent folder and root folder*/
/*so we need to remove them from the root folder*/
DriveApp.getRootFolder().removeFile(formFile);
DriveApp.getRootFolder().removeFile(sheetFile);
// Link Form to Spreadsheet
form.setDestination(FormApp.DestinationType.SPREADSHEET, sheetFile.getId());
// Update form properties via chaining.
form.setTitle(filename)
.setDescription(grouprow.SubGroup)
.setConfirmationMessage('Thanks for responding!')
.setAllowResponseEdits(true)
.setAcceptingResponses(true);
// Update survey master file
objDB.updateRow( db, 'groups', {FormId:form.getId(),SheetId:sheet.getId()}, {Gid:Gid } )
//get questions from the sheet questions
//specify the colums that we want to extract ie ['itype','mtype','descriptions']
//specify the condition based on JSON data format ie SubGroup contains grouprow.SubGroup value
rows = objDB.getRows( db, 'questions', ['itype','mtype','description'], {SubGroup:grouprow.SubGroup} );
var questions=[];
//prepare the questions array
rows.forEach(function (row) {
if (row.itype=='t'){return true;} //skip the title row
questions.push(row.description); // push the remaining rows
});
//create grid item
//add questions array to grid item
var item = form.addGridItem();
item.setTitle('Rate your preference')
.setRows(questions)
.setColumns(['Strongly Disagree', 'Disagree', 'Not Sure','Agree', 'Strongly Agree']);
});//end for each
//return response
return ContentService
.createTextOutput(JSON.stringify({
"result": "success",
"values": grouprows
}))
.setMimeType(ContentService.MimeType.JSON);
}
|
DEMO
.
No comments:
Post a Comment