Case Study 2

I want to collect some data from my users, Here main goal is to validate the email address of the user.

There will be two forms, First form will collect personal information and Email Address, After the submitting the first form by user, User will get an email with a link for the second form.

Second form will be having personal Information fields, Email address and some work related Information.

When user clicks the link, He will see the second form in which personal information and Email address fields already populated with the data which he submitted using the first form. Now user will have to enter only the work related Information fields and submit the form.

So here is an example setup for a similar scenario.

Let us create the first Form

Name
Email
Mobile No.

Create second form with extra fields

Name
Email
Mobile No.
Extra Info 1
Extra Info 2

Write the scripts for the first form which will be executed on form submit. This will email the second form link to the user's email (which he provided in the form).Setup a onFormSubmit trigger for this script by going to Triggers>All your triggers... in the scrip Editor window.

You will have to update the key for the second form spreadsheet (shown in red color). You can get it from spreadsheet URL.
e.g If the URL of a form spreadsheet is:
https://spreadsheets0.google.com/spreadsheet/ccc?hl=en_US&key=t6LshNBIf_plsjhswx6K3fQ&hl=en_US#gid=0
The part highlighted in yellow color is the ID or key.
Also you may edit the Mail Subject and Body as per your requirement.

postFormSpreadSheetKey = 'Id of the second form spreadsheet';//Change it to yours

function myFunction() {
var SS = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var lastRow = SS.getLastRow();
var lastColumn = SS.getLastColumn();
var preFormData = SS.getRange(lastRow, 2, 1, lastColumn-1).getValues();
var postFormURL = SpreadsheetApp.openById(postFormSpreadSheetKey).getFormUrl();
var advPostFormUrl = postFormURL+'&entry_0='+preFormData[0][0]
+'&entry_1='+preFormData[0][1]
+'&entry_2='preFormData[0][2];
//You might have to change above entry numbers depending on your second form. You can get correct //entry numbers by going into the html sorce of the second form.

var senderEmail = preFormData[0][1];
var htmlBody = 'Hi<br/>'+'Please complete the details using below form link.<br/>'
+'<a href="'+advPostFormUrl+'"><b>Form Link</b></a>';
var mailSubject = 'Complete the form'

MailApp.sendEmail(senderEmail, mailSubject, 'body', {htmlBody : htmlBody,
name : 'Life Insurance Broker'})
}

Now create an email Alert script when the second form is submitted. Make sure that you are writing the script in the second form spreadsheet Script Editor. Set a trigger onFormSubmit for this script too..

function emailAlert() {
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var SS = Spreadsheet.getSheets()[0];
var submittedData = SS.getRange(SS.getLastRow(), 2, 1, SS.getLastColumn()-1).getValues();
var headerData = SS.getRange(1, 2, 1, SS.getLastColumn()-1).getValues();

var yourEmail = 'Your Email';//Change it to yours
var emailSubject = 'Form has been submitted by a user';//Change as per your requirement
var htmlBody ='<table><tbody>';
for(var i=0; i < SS.getLastColumn()-1; i++){
htmlBody += '<tr><td>'+headerData[0][i]+'</td><td>'+submittedData[0][i]+'</td></tr>';
}
htmlBody += '</tbody></table>';
MailApp.sendEmail(yourEmail, emailSubject, 'body', {htmlBody : htmlBody});
}
Now you are done. If you have any query or feedback please give it through comments!

Comments