Labels

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Friday, March 10, 2017

Create Custom Response with Google Apps 1


.
Create Custom Response with Google Apps 1

OBJECTIVE

1. Create script to send form response
Get the form from the previous tutorial.

1. Create script to send form response

function submitResponse(){
  var form=FormApp.getActiveForm();
  var items=form.getItems();

  var itemName = items[0].asTextItem();
  var itemNameResponse = itemName.createResponse('mr razoph');
  var itemEmail = items[1].asTextItem();
  var itemEmailResponse = itemEmail.createResponse('razoph@gmail.com');  
  var itemRefNo = items[2].asTextItem();
  var itemRefNoResponse = itemRefNo.createResponse('razoph@gmail.com');  

    var formResponse = form.createResponse();
    formResponse.withItemResponse(itemNameResponse );
    formResponse.withItemResponse(itemEmailResponse );
    formResponse.withItemResponse(itemRefNoResponse );
    formResponse.submit();

}
Run.
Outcome.

REFERENCE


.

Creating Custom Survey with Google Apps 5


.
Creating Custom Survey with Google Apps 5

OBJECTIVE

1. Create a _SurveyMasterForm.
2. Add Trigger to _SurveyMasterForm.
3. Test the survey form.

1. Run script to create _SurveyMasterForm

Script: _createSurveyMasterForm
//get required object
   var SCID =ScriptApp.getScriptId(); //SCID
   var SSDOC = DriveApp.getFileById(SCID);
   var Folders = SSDOC.getParents();
   var Folder = Folders.next();
   var FOLDERID = Folder.getId(); //FOLDERID
function showId(){
  Logger.log("SCID:"+SCID);
  Logger.log("FOLDERID:"+FOLDERID);
}
function createForm(){

  var filename="SurveyMasterForm";

  var files = Folder.getFiles();
 while (files.hasNext()) {
   var file = files.next();
   if (filename==file.getName()){
     Logger.log(file.getName());
     Folder.removeFile(file)
   }
 }
  //create the form
  var form = FormApp.create(filename);
  var formFile = DriveApp.getFileById( form.getId() );
  DriveApp.getFolderById(FOLDERID).addFile( formFile );
  /*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);
  // Update form properties via chaining.
  form.setTitle(filename)
     .setDescription("Description")
     .setConfirmationMessage('Thanks for responding!')
     .setAllowResponseEdits(true)
     .setAcceptingResponses(true);  

   // Add text fields
  form.addTextItem().setTitle('Name').setRequired(true);
  form.addTextItem().setTitle('Email').setRequired(true);
  form.addTextItem().setTitle('RefNo (optional)');  

  //return response
  return ContentService
  .createTextOutput(JSON.stringify({
    "result": "success",
    "values": filename
  }))
  .setMimeType(ContentService.MimeType.JSON);  
}  
Run the script.

2. Add Trigger to _SurveyMasterForm

Open the form _SurveyMasterForm
Open Script Editor.
Add codes.
function sendConfirmationEmail(e) {
  // e is a Form Event object - see https://developers.google.com/apps-script/guides/triggers/events#google_forms_events
  // Edit this to set the subject line for the sent email
  var subject = "Submission Successful";
  // This will show up as the sender's name
  var sendername = "Survey Master";
     
  // This is the body of the registration confirmation message
  var message = "Thank you for registering.<br>We will be in touch.<br><br>";
  message += "Your form responses were:<br><br>";
  // response is a FormResponse - see https://developers.google.com/apps-script/reference/forms/form-response
  var response = e.response;
  var textbody, sendTo, bcc;
  // Get the script owner's email address, in order to bcc: them
  bcc = Session.getActiveUser().getEmail();
  // Now loop around, getting the item responses and writing them into the email message
  var itemResponses = response.getItemResponses();
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "<br>";
    // If this field is the email address, then use it to fill in the sendTo variable
    // Check that your form item is named "Email" or edit to match
    if (itemResponse.getItem().getTitle() == "Email") {
      sendTo = itemResponse.getResponse();
    }
  }
  message += "<br>If you wish to edit your response, please click on <a href=\"" + response.getEditResponseUrl() + "\">this link</a>.";
  message += "<br><br>";
  textbody = message.replace("<br>", "\n");
     
  GmailApp.sendEmail(sendTo, subject, textbody,
                       {bcc: bcc, name: sendername, htmlBody: message});
}
Add Trigger.

3. Test the survey form.

Check the respondent email.

REFERENCES


.

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


.

Creating Custom Survey with Google Apps 3


.
Creating Custom Survey with Google Apps 3

OBJECTIVE

1. Perform a loop to generate multiple Survey Forms and their linked Spreadsheets
Copy demo document from the previous tutorial. Rename the sheet and the script to _survey3.
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


.