Google App Scripts

« Previous Next »

Google Form:

it is used to custom form for surveys and questionaries to share your forms with collaborators.
Google form is free and powerful, online form can be created in faster and responses are collected in online spreadsheet.
It provides feature to analyse the google form responses, We can automate things in spreadsheet using spreadsheet formulas and google app scripts.
Google Form Advantages:

• Free
• You do it when you want it. (No IT to deal with)
• You get the data in digital format that you can read.
• Quick to put together

Google Form Limitations:

• Can be completed as many times as a person wants to access the survey.
• No ability to format text
• Hardly any ability to move questions other than up or down. They are all flush left.

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

This App script is the sample code which can be used in google form to read UI inputs and append in the google spreadsheet result.

Sample App scripts for google form submit:

//Form submit google app scripts
  
// Gets the form object
 var form = FormApp.getActiveForm();
//Gets all the responses of form object
 var formResponses=form.getResponses();

//To open google spreadsheets data
 var ss = SpreadsheetApp.openById("");

 //Creates spreadsheet object for first one
 var sheet = ss.getSheets()[0];

 //Creates spreadsheet object for second one
 var rating = ss.getSheets()[1];

 //Creates spreadsheet object for second one
 var avg=ss.getSheets()[2];

 var totalRows=sheet.getLastRow();
 var totalColumns=sheet.getLastColumn();

 //To get the last responses of google form data
 var formLastResponse=formResponses[formResponses.length-1];

 //To get the controls or items responses in the form response object
 var responseItems=formLastResponse.getItemResponses();
 //To log messages in execution
 //Logger.log(sheet.getLastRow()+ " " + rating.getLastRow());
// Logger.log(sheet.getLastColumn());

//rating.insertRowsAfter(sheet.getLastRow(), 1);

//sheet.getRange("A"+totalRows+":D"+totalRows).copyTo(rating.getRange("A"+totalRows+":D"+totalRows));
//rating.getRange("A"+totalRows).setValue(new Date());
//rating.getRange("B"+totalRows).setValue(responseItems[0].getResponse());
//rating.getRange("C"+totalRows).setValue(responseItems[1].getResponse());
//rating.getRange("D"+totalRows).setValue(responseItems[2].getResponse());
var questionNo=4;
var quesVal=new Array();
var avgVal=new Array();
 quesVal[0]=new Date();
 quesVal[1]=responseItems[0].getResponse();
 quesVal[2]=responseItems[1].getResponse();
 quesVal[3]=responseItems[2].getResponse();
 
 avgVal[0]=new Date();
 avgVal[1]=responseItems[0].getResponse();
 avgVal[2]=responseItems[1].getResponse();
 avgVal[3]=responseItems[2].getResponse();
 

var dimCount=0;
 avgVal[dimCount+4]=0;
 var dimTotalRating=0;
for(var column=5;column<=totalColumns;column++){
  while(form.getItems()[questionNo].getType()!="CHECKBOX"){
       questionNo++;
       avgVal[dimCount+4]=dimTotalRating.toFixed(2);
       dimCount++;
       dimTotalRating=0;
     }
     var itemId=form.getItems()[questionNo].getId();
   //var range=sheet.getRange(totalRows, column);
   var responseIndex=-1;
   for(var responseCount=0;responseCount=0) {
   //Logger.log(form.getItems()[questionNo].getType());
    
     var totalChoices= form.getItems()[questionNo].asCheckboxItem().getChoices().length;
     var selectedChoices=responseItems[responseIndex].getResponse().length;
     
     ratings=(selectedChoices/totalChoices).toFixed(2);
     quesVal[column-1]=ratings;
    // Logger.log(dimCount+" "+level);
     dimTotalRating+=(selectedChoices/totalChoices);
    // rating.getRange(totalRows, column).setValue(rating);
     //Logger.log(range.getValue()+" "+rating);
   }
   else{
     //rating.getRange(totalRows, column).setValue(0.0);
     quesVal[column-1]=ratings;
     // Logger.log(dimCount+" "+level);   
   }
    
  questionNo++;
 }
 avgVal[dimCount+4]=dimTotalRating.toFixed(2);
 rating.appendRow(quesVal);
 avg.appendRow(avgVal);

                

« Previous Next »