Labels

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Friday, March 10, 2017

Creating Custom Survey with Google Apps 4

.
Creating Custom Survey with Google Apps 4

OBJECTIVE

1. Add Text Fields; Name, Email and Reference No.

1) Add Text Fields

//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 } )  
   // Add text fields
  form.addTextItem().setTitle('Name').setRequired(true);
  form.addTextItem().setTitle('Email').setRequired(true);
  form.addTextItem().setTitle('RefNo (optional)');
     
  //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

REFERENCES


.

No comments:

Post a Comment