google-apps-script - 字段和 FileUpload 的表单验证

Question

我希望在具有文本输入和 fileupload 的表单上进行验证/必填字段用于文件附件。

该脚本接受输入并允许用户附加文件。提交后，它会将输入字段添加到电子表格并将文件上传到我的驱动器。我想要做的是强制文本输入和下拉列表得到回答并要求文件附件。

我找到了示例链接 "Example validation"但我在尝试将其添加到代码中的点击处理程序时遇到问题。任何人都可以帮助指出正确的方向吗？

// 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@email.com';
   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;   
 }    

编辑 - 在第二次文件上传和提交中添加将不会启用

我回去编辑了脚本并做了一些修改以基本上执行与以前相同的功能，但是这个需要上传两个文件。我遵循相同的代码，但在测试后提交按钮将永远不会启用。这是更新的代码:

//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;
}

编辑添加 CliHandler4我已经更新了代码以反射(reflect)第 4 个 cliHandler，但我仍然无法激活提交按钮。不知道为什么它会失败。我已经看过好几次了，但我似乎无法弄清楚哪里出了问题。这是代码

编辑为答案: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().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','#9999ff').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)
  .validateLength(rate, 1, 40).validateLength(ysp, 1, 40).validateLength(closingcostcredit, 1, 40)
  .validateLength(ltv, 1, 40).validateNotMatches(mortgageIns,'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).setText('selected')
  var cliHandler4 = app.createClientHandler().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, 'Mortgage Insurance')
  .setWidget(10, 1,mortgageIns)  
  .setText(11, 0, 'Point File')
  .setWidget(11, 1, upLoad.addChangeHandler(cliHandler3).addChangeHandler(cliHandler2))
  .setText(12, 0, 'Credit Report')
  .setWidget(12, 1, upLoad2.addChangeHandler(cliHandler4).addChangeHandler(cliHandler2))
  .setWidget(13, 0, submitButton)
  .setWidget(13, 1, warning)
  .setWidget(14, 0, uploadtracker)
  .setWidget(14, 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;
}

Answer 1

一个简单的方法是使用另一个带有验证器的客户端处理程序。我建议像这样把它放在 fileUpload 上:(在同一个地方替换你的代码)

  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);

并且这个启用了提交按钮!你可以test it here

---

编辑

这是检查客户端处理程序上的所有小部件的更复杂的选项....我重现了整个相关部分并更新了在线示例 - EDIT3:DateBox 验证不行不通，这是我们必须解决的问题!与此同时，我在同一个处理程序上添加了一个新的消息处理

编辑 4:(最后一个!)

我终于为每种小部件类型找到了一个可行的解决方案，日期必须包含一个“2”(我认为这将持续几年 ;-) 我展示了整个 doGet 函数，因为我在这里做了一些其他更改那里......当 fileUpload 小部件填充到最后一个位置(不知道为什么)时效果最好，并且在某些情况下需要重新修改 textBow 以获得验证但在大多数情况下它可以正常工作。

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;
}