Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
670 views
in Technique[技术] by (71.8m points)

google apps script - Form validation on fields and FileUpload

I'm looking to do validation / required fields on a form that has text input and fileupload for file attachments.

The script takes inputs and allows user to attach a file. Upon submit, it adds input fields to spreadsheet and uploads the file to my drive. What I want to do is force the text input and drop down lists to be answered and require a file attachment.

I found this link for examples "Example validation" but I'm having a problem trying to add this to the click handler in my code. Can anyone help point in the right direction?

// Script-as-app template.
var submissionSSKey = 'Insert SS Key';

function doGet(e) {
  var app = UiApp.createApplication().setTitle('Loan Registration Processing');
  var panel = app.createFormPanel();
  var grid = app.createGrid(10,2).setId('loanGrid');
  var loanTypeLabel = app.createLabel('Loan Type');
  var loanList = app.createListBox().setName('Loan List').setWidth('120px').setName('LoanType');
      loanList.addItem('Select Option');    
      loanList.addItem('FHA');
      loanList.addItem('Convential');  
      loanList.addItem('VA');
      loanList.addItem('Reverse');
      loanList.addItem('HELOC');
  var borrowerNameLabel = app.createLabel("Borrower's Name");
  var borrowerTextbox = app.createTextBox().setWidth('150px').setName('borrower');
  var loanAmountLabel = app.createLabel('Loan Amount');
  var loanAmountTextbox = app.createTextBox().setWidth('150px').setName('amount');
  var appDateLabel = app.createLabel('Loan Date');
  var appDateTextbox = app.createDateBox().setWidth('150px').setName('date');
  var lienPostition = app.createLabel('Lien Position');
  var lienPos = app.createListBox().setName('Lien Position').setWidth('150px').setName('LienPosition');
      lienPos.addItem('Select Option');     
      lienPos.addItem('1st');
      lienPos.addItem('2nd');
  var propertyType = app.createLabel('Property Type');
  var propType = app.createListBox().setName('Property Type').setWidth('150px').setName('PropertyType');
      propType.addItem('Select Option');
      propType.addItem('1-4 Units');
      propType.addItem('Manufactured');
  var submitButton = app.createSubmitButton('<B>Submit</B>'); 
  var warning = app.createHTML('<B>PLEASE WAIT WHILE DATA IS UPLOADING<B>').setStyleAttribute('background','yellow').setVisible(false)
  //file upload
  var upLoadTypeLabel = app.createLabel('Point File');
  var upLoad = (app.createFileUpload().setName('thefile'));
  var upLoadTypeLabel2 = app.createLabel('Credit Report');
  var upLoad2 = (app.createFileUpload().setName('thefile2'));

  //Grid layout of items on form
  grid.setWidget(0, 0, loanTypeLabel)
      .setWidget(0, 1, loanList)
      .setWidget(1, 0, borrowerNameLabel)
      .setWidget(1, 1, borrowerTextbox)
      .setWidget(2, 0, loanAmountLabel)
      .setWidget(2, 1, loanAmountTextbox)
      .setWidget(3, 0, appDateLabel)
      .setWidget(3, 1, appDateTextbox)
      .setWidget(4, 0, lienPostition)
      .setWidget(4, 1, lienPos)
      .setWidget(5, 0, propertyType)
      .setWidget(5, 1, propType)
      .setWidget(6, 0, upLoadTypeLabel)
      .setWidget(6, 1, upLoad)
      .setWidget(7, 0, upLoadTypeLabel2)
      .setWidget(7, 1, upLoad2)
      .setWidget(8, 0, submitButton)
      .setWidget(9, 1, warning)

  var cliHandler = app.createClientHandler().forTargets(warning).setVisible(true)
  submitButton.addClickHandler(cliHandler);  
  panel.add(grid);
  app.add(panel);
  return app;

}

 function doPost(e) {
  var app = UiApp.getActiveApplication();
  var LoanType = e.parameter.LoanType;
  var borrower = e.parameter.borrower;
  var amount = e.parameter.amount;
  var date = e.parameter.date;
  var LienPosition = e.parameter.LienPosition;
  var PropertyType = e.parameter.PropertyType;

   //Spreadsheet to load form values to
   var sheet = SpreadsheetApp.openById(submissionSSKey).getActiveSheet();
   var lastRow = sheet.getLastRow();
   var targetRange = sheet.getRange(lastRow+1, 1, 1, 6).setValues([[LoanType,borrower,amount,date,LienPosition,PropertyType]]);
   // data returned is a blob for FileUpload widget
   var fileBlob = e.parameter.thefile;
   var fileBlob2 = e.parameter.thefile2;
   //Grabs the folder to send upload files
   var folder = DocsList.getFolderById('0B8PHKnfhErK-T2IzRW9ZWjcwRmc');
   //Creates the upload file in root
   var doc = DocsList.createFile(fileBlob);
   var doc2 = DocsList.createFile(fileBlob2);
   //moves created files in root to specific folder
   doc.addToFolder(folder);
   doc2.addToFolder(folder);
   //Remove the copy left in the root, leaving only the version in the specific folder
   doc.removeFromFolder(DocsList.getRootFolder());       
   doc2.removeFromFolder(DocsList.getRootFolder());   
   //Message to user after submit of form

   var uplabel = app.createHTML('<B>Thank you for your Loan Registation Subumission. Press F5 on your keyboard to enter another Loan Registration.</B>');

//Send email to group
   var emailAddress = '[email protected]';
   var folderURL = 'Folder URL';
   var ssURL = 'SS URL';

   var message = "<HTML><BODY>"
   + "<P>" + " A new Loan Registration has been submitted."
   + '<P>You can access the Loan Submitted documents <A HREF="' + folderURL + '">here</A>.'
   + '<P>You can access the submitted Loan Registration Spreadsheet <A HREF="' + ssURL + '">here</A>.'
   + "</HTML></BODY>";
   MailApp.sendEmail(emailAddress, "New Loan Registrtion Submittal Posted!", "", {htmlBody: message}); 
   app.add(uplabel);   
   return app;   
 }    

EDIT - Added in second file upload and submit won't enable

I've gone back and edited the script and modified a bit to basically perform same function as before but this one needs to upload two files. I'm following the same code but after testing the submit button will never enable.. Here is the code that was updated:

//Create the Labels, TextBoxes, and Drop downs
var borrowerTextbox = app.createTextBox().setWidth('150px').setName('borrower');
var Lender = app.createTextBox().setWidth('150px').setName('lender');
var correspondentBroker = app.createListBox().setName('Correspondent Broker').setWidth('120px').setName('correspondentbroker');
    correspondentBroker.addItem('Select Option');
    correspondentBroker.addItem('Correspondent');
    correspondentBroker.addItem('Broker');
var loanAmountTextbox = app.createTextBox().setWidth('150px').setName('amount');
var loanprogram = app.createTextBox().setWidth('150px').setName('program');
var rate = app.createTextBox().setWidth('150px').setName('rate');
var ysp = app.createTextBox().setWidth('150px').setName('ysp');
var closingcostcredit = app.createTextBox().setWidth('150px').setName('credit');
var ltv = app.createTextBox().setWidth('150px').setName('ltv');
var impound = app.createListBox().setName('impounds').setWidth('120px').setName('impounds');
    impound.addItem('Select Option');
    impound.addItem('Yes');
    impound.addItem('No');
var mortgageIns = app.createListBox().setName('Correspondent Broker').setWidth('120px').setName('mortgageinsurance');
    mortgageIns.addItem('Select Option');
    mortgageIns.addItem('MI');
    mortgageIns.addItem('PMI Borrower Paid');
    mortgageIns.addItem('PMI Lender Paid');
var upLoad = app.createFileUpload().setName('thefile');
var upLoad2 = app.createFileUpload().setName('thefile2');
var uploadtracker = app.createTextBox().setVisible(false);
var uploadtracker2 = app.createTextBox().setVisible(false);
var submitButton = app.createSubmitButton('<B>Submit</B>');  
var warning = app.createHTML('Please fill in all fields').setStyleAttribute('background','#FFcc99').setStyleAttribute('fontSize','20px');


//Click Handlers are set
var cliHandler2 = app.createClientHandler()
.validateLength(borrowerTextbox, 1, 40)
.validateLength(Lender, 1, 40)
.validateNotMatches(correspondentBroker,'Select Option')
.validateLength(loanAmountTextbox, 1, 40)
.validateLength(loanprogram, 1, 40)
.validateNotMatches(mortgageIns,'Select Option')
.validateLength(rate, 1, 40)
.validateLength(ysp, 1, 40)
.validateLength(closingcostcredit, 1, 40)
.validateLength(ltv, 1, 40)
.validateNotMatches(impound, 'Select Option')
.validateMatches(uploadtracker, 'selected')
.validateMatches(uploadtracker2, 'selected')
.forTargets(submitButton).setEnabled(true)
.forTargets(warning)
.setHTML('Now you can submit your form')
.setStyleAttribute('background','#99FF99')
.setStyleAttribute('fontSize','12px');

var cliHandler3 = app.createClientHandler().forTargets(uploadtracker).forTargets(uploadtracker2).setText('selected')

//Grid layout of items on form
grid.setText(0, 0, 'Borrower Name')
  .setWidget(0, 1, borrowerTextbox)
  .setText(1, 0, "Lender")
  .setWidget(1, 1, Lender)
  .setText(2, 0, 'Correspondent or Broker')
  .setWidget(2, 1, correspondentBroker)
  .setText(3, 0, 'Loan Amount')
  .setWidget(3, 1, loanAmountTextbox)
  .setText(4, 0, 'Loan Program')
  .setWidget(4, 1, loanprogram)  
  .setText(5, 0, 'Rate')
  .setWidget(5, 1, rate)  
  .setText(6, 0, 'YSP')
  .setWidget(6, 1,ysp)
  .setText(7, 0, 'Closing Cost Credit')
  .setWidget(7, 1, closingcostcredit)
  .setText(8, 0, 'LTV')
  .setWidget(8, 1, ltv)
  .setText(9, 0, 'Impounds')
  .setWidget(9, 1,impound)  
  .setText(10, 0, 'Point File')
  .setWidget(10, 1, upLoad.addChangeHandler(cliHandler3).addChangeHandler(cliHandler2))
  .setText(11, 0, 'Credit Report')
  .setWidget(11, 1, upLoad2.addChangeHandler(cliHandler3).addChangeHandler(cliHandler2))
  .setWidget(12, 0, submitButton)
  .setWidget(12, 1, warning)
  .setWidget(13, 0, uploadtracker)
  .setWidget(13, 1, uploadtracker2)
  .addClickHandler(cliHandler2);

var cliHandler = app.createClientHandler().forTargets(warning).setHTML('<B>PLEASE WAIT WHILE DATA IS BEING UPLOADED TO Google Drive<B>').setStyleAttribute('background','yellow');
submitButton.addClickHandler(cliHandler).setEnabled(false);

panel.add(grid);
app.add(panel);
return app;
}

EDIT-to Add CliHandler4 I had update the code to reflect the 4th cliHandler but I'm still unable to active the submitbutton. Not sure why its failing.. I've looked it over several times and I can't seem to figure out what is wrong.. Here is the code

EDIT as answer : one widget was missing in the UI (mortgageIns)

    var submissionSSKey = 'ID GOES HERE';
    var Panelstyle = {'background':'#c0d6e4','padding':'100px','borderStyle':'ridge','borderWidth':'15PX','borderColor':'#31698a'}


function doGet(e) {
  var app = UiApp.createApplication().setTitle('PCH Mortgage Disclosure Request');
  app.add(app.createImage("http://www.pchmortgage.com/img/logo_thumbnail/6644.png"));
  var instructLabel = app.createHTML('<B><p> </p><p>Be sure to fill in each field completely prior to submitting the Disclsure Request</P></B>');
  app.add(instructLabel);

//Create the FormPanel and Grid for the application  
  var panel = app.createFormPanel().setStyleAttributes(Panelstyle).setPixelSize(450, 300);
  var grid = app.createGrid(15,2).setId('loanGrid');

//Create the Labels, TextBoxes, and Drop downs
    var borrowerTextbox = app.createTextBox().setWidth('150px').setName('borrower');
    var lender = app.createTextBox().setWidth('150px').setName('lender');
    var correspondentBroker = app.createListBox()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

One simple way to do it is to use another client handler with validators. I suggest to put it on the fileUpload like this : (replace in your code at the same place)

  var cliHandler2 = app.createClientHandler().validateLength(loanAmountTextbox, 1, 20)
  .validateLength(borrowerTextbox, 1, 20).validateLength(lienPos, 1, 20).forTargets(submitButton).setEnabled(true);// you can add more conditions here (widget name, minimum length, max length)

  var upLoad = app.createFileUpload().setName('thefile').addChangeHandler(cliHandler2);

and this one enables the submit button! you can test it here


EDIT

Here is the more sophisticated option that checks all the widgets on client handler.... I reproduce the whole relevant part and update the online example - EDIT3 : the DateBox validation doesn't work, that's an issue we'll have to go through ! in the mean time I added a new message handling on the same handler

EDIT 4 : (last one !)

I finally found a working solution for each widget type, the date must contain a '2' (which will be true for a couple of years I think ;-) I show the whole doGet function because I made some other changes here and there... It works best when the fileUpload widget is filled in last position (don't know why) and in certain situations one need to re-modify a textBow to get the validation but in most cases it's working as it should.

function doGet(e) {
  var app = UiApp.createApplication().setTitle('Loan Registration Processing');
  var panel = app.createFormPanel();
  var grid = app.createGrid(8,2).setId('loanGrid');
  var loanList = app.createListBox().setName('Loan List').setWidth('120px').setName('LoanType');
      loanList.addItem('Select Option');    
      loanList.addItem('FHA');
      loanList.addItem('Convential');  
      loanList.addItem('VA');
      loanList.addItem('Reverse');
      loanList.addItem('HELOC');
  var borrowerTextbox = app.createTextBox().setWidth('150px').setName('borrower');
  var loanAmountTextbox = app.createTextBox().setWidth('150px').setName('amount');
  var appDatebox = app.createDateBox().setWidth('150px').setName('date');
  var lienPos = app.createListBox().setName('Lien Position').setWidth('150px').setName('LienPosition');
      lienPos.addItem('Select Option');     
      lienPos.addItem('1st');
      lienPos.addItem('2nd');
  var propType = app.createListBox().setName('Property Type').setWidth('150px').setName('PropertyType');
      propType.addItem('Select Option');
      propType.addItem('1-4');
      propType.addItem('Manufactured');
  var submitButton = app.createSubmitButton('<B>Submit</B>'); 
  var warning = app.createHTML('Please fill in all fields').setStyleAttribute('background','#FFcc99').setStyleAttribute('fontSize','20px');
  //file upload
  var upLoad = app.createFileUpload().setName('thefile');
  var cliHandler2 = app.createClientHandler()
  .validateLength(borrowerTextbox, 1, 40).validateLength(loanAmountTextbox, 1, 40).validateNotMatches(loanList,'Select Option')
  .validateNotMatches(lienPos,'Select Option').validateNotMatches(propType, 'Select Option').validateMatches(appDatebox, '2','g')
  .validateNotMatches(upLoad, 'FileUpload').forTargets(submitButton).setEnabled(true).forTargets(warning)
  .setHTML('Now you can submit your form').setStyleAttribute('background','#99FF99').setStyleAttribute('fontSize','12px')

  //Grid layout of items on form
  grid.setText(0, 0, 'Loan Type')
      .setWidget(0, 1, loanList.addClickHandler(cliHandler2))
      .setText(1, 0, "Borrower's Name")
      .setWidget(1, 1, borrowerTextbox.addKeyUpHandler(cliHandler2))
      .setText(2, 0, 'Loan Amount')
      .setWidget(2, 1, loanAmountTextbox.addKeyUpHandler(cliHandler2))
      .setText(3, 0, 'Loan Date')
      .setWidget(3, 1, appDatebox)
      .setText(4, 0, 'Lien Position')
      .setWidget(4, 1, lienPos.addClickHandler(cliHandler2))
      .setText(5, 0, 'Property Type')
      .setWidget(5, 1, propType.addClickHandler(cliHandler2))
      .setText(6, 0, 'File Upload')
      .setWidget(6, 1, upLoad.addChangeHandler(cliHandler2))
      .setWidget(7, 0, submitButton)
      .setWidget(7, 1, warning);

  var cliHandler = app.createClientHandler().forTargets(warning).setHTML('<B>PLEASE WAIT WHILE DATA IS UPLOADING<B>').setStyleAttribute('background','yellow');
  submitButton.addClickHandler(cliHandler).setEnabled(false);  
  panel.add(grid);
  app.add(panel);
  return app;
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...