Back to top

A Google App script tutorial for Google Sheet, Google Form and Google Mail

Scenario:

We are going to design a Google form. For one of the fields we are going to create a select list which shall pull content from an existing Google Spreadsheet. 

When the user submits data through this form, the response is first stored in another Google spreadsheet and response details are also sent to a Gmail account.

Code:

function myFunction() {
  
  var form=FormApp.create('Survey form').setConfirmationMessage('Thank You for your input.');
    
  //textfield
  var name=form.addTextItem();
  name.setTitle('Enter your name');
  
  //dropdown
  var dsgn=form.addListItem();
  dsgn.setTitle('Please mention your designation');
  var choice1=dsgn.createChoice('Information Officer');
  var choice2=dsgn.createChoice('ICT Volunteer');
  var choice3=dsgn.createChoice('Focal Person');
  dsgn.setChoices([choice1,choice2,choice3]);
  dsgn.setRequired(true);

  //checkbox
  var role=form.addCheckboxItem();
  role.setTitle('Enter your recruiting organization');
  var rolechoice1=role.createChoice('UNV');
  var rolechoice2=role.createChoice('UNDP');
  var rolechoice3=role.createChoice('gov');
  role.setChoices([rolechoice1,rolechoice2,rolechoice3]);
  role.showOtherOption(true);
  role.setRequired(true);
  
  //radio button
  var contract=form.addMultipleChoiceItem();
  contract.setTitle('Enter your contract type');
  contract.setRequired(true);
  var contractchoice1=contract.createChoice('local');
  var contractchoice2=contract.createChoice('central');
  contract.setChoices([contractchoice1,contractchoice2]);
  contract.showOtherOption(true);

  //textarea
  var details=form.addParagraphTextItem()
  details.setTitle('Enter your job description');

  
  //dropdown
  var lb=form.addListItem();
  lb.setTitle('Please select your local body');
  

  //pulling content from a spreadsheet as select list choice

  var spread=SpreadsheetApp.openById("1gjj87jPq7jc2_33NArrYN-4eeuUGpKSN4GTO7FX1BJE");
  //selecting the spreadsheet at bit.ly/lbdomains
  
  var mysheet=spread.getSheets()[0];
  //selecting the first sheet of the spreadsheet for the list of ddcs
  
  var lastrow=spread.getLastRow();
  //counting the total number of rows
  
  var ulastrow=lastrow-2;
  //values for ddcs start from the third row, hence offsetting the first two rows
 // thus, ulastrow returns 75

  
  var sheetlist=mysheet.getRange(3,3,ulastrow,1).getValues();
  // the sheet represented by the variable mysheet looks like below.
  

/* as we can see that the list of ddc name in english starts from 3rd row and 3rd column of the sheet (the cell containing value Achham).
likewise, the number of rows to be pulled is 75.
hence, in order to pull 75 rows of ddc name in english we use the following function 
mysheet.getRange(3,3,ulastrow,1).getValues();
getRange(3,3,ulastrow,1) function selects a range from the sheet with ulastrow number of rows (75) and 1 column starting from 3rd row and 3rd column.
getRange(3,3,ulastrow,1).getValues() returns a two dimensional array with 75 ddc english names as members of the array.
please note that getValues() function always returns a two dimensional array.
hence, we cannot pass this array to setChoices() function since this function only takes one dimensional array as argument.
now in order to use this array as the select list, first we need to declare individual rows of this array as options for the select list.
second, we need to create a one dimensional array out of this multidimensional array.
for this we go throug the following loop.*/

  var currentval="";
  var newchoice="";
  var pusharray=[];
  
  for(var x=0;x<ulastrow;x++){
      
     currentval=sheetlist[x];
     // getting the name of ddc for the row x of the array
     // this selects a single row from the column at a time in the loop

     newchoice=lb.createChoice(currentval);    
     // creating a choice for the select list  
    
    pusharray.push(newchoice);
    // pushing the value in the array
    
      }
  
  lb.setChoices(pusharray);
  // passing the array as the set of choices for the select list
  
 var emailadd=form.addTextItem();
  emailadd.setTitle("Your email address please");
  // email address
  
  ScriptApp.newTrigger('onFormSubmit').forForm(form).onFormSubmit().create();
  // creating trigger for the form "form" for the event onFormSubmit

  
}

function onFormSubmit(e) {
  // trigger function
  // e is the event object  

    
    var fR=e.response;
    // fR is the response object
    
    var iR=fR.getItemResponses();
    // iR is the response array
    
    var rname=iR[0].getResponse();
    var rdsgn=iR[1].getResponse();
    var rrole=iR[2].getResponse();
    var rcontract=iR[3].getResponse();
    var rdetails=iR[4].getResponse();
    var rlb=iR[5].getResponse();
    var remail=iR[6].getResponse();
    
    // response is the Form Response object
    // getItemResponses returns an array from the response object 

    // getResponse can be used to pull specific data from the response array

    
    var rarray=[rname,rdsgn,rcontract,rdetails,rlb,remail];
    
    var respread=SpreadsheetApp.openById('1EfWorQ1yfJVphXECy_anSfOrlDX9YnnukX-BqUAuNHk');
    // opening an already existing spreadsheet for storing form responses
    
    var resheet=respread.getSheets()[0];
    // selecting the first sheet
    
    resheet.appendRow(rarray)
    // adding new row to the google spreadsheet
    
    var formadmin='apurwa@gmail.com';
    var mailbody='Email:'+remail+'\n'+'Name: '+rname+'\n'+'Local Body: '+rlb; 
    var mailsub='New response for the survey form';

    GmailApp.sendEmail(formadmin, mailsub, mailbody);
    // sending email to the admin account, apurwa@gmail.com in this case
}