I'm in the process of creating a basic form that takes user input via text boxes and drop down list and upon submit will log into a spreadsheet. So far this works fine. What I would like to add to the form is a "file upload" feature, that allows someone to select the file and upon submitting the form the file is uploaded as the data values from text boxes and drop downs are logged into the spreadsheet. I've reviewed the following link https://developers.google.com/apps-script/class_fileupload but i'm having a hard time inserting / adding the example into the existing doGet function.. Anyone able to help or provide suggestions? Here is the Google App Script code so far.
*used the following link to base the form on: https://sites.google.com/site/appsscripttutorial/advanced-examples/insert-data-in-sheet-using-ui-forms
// Script-as-app template.
var submissionSSKey = 'Spreadsheet Key goes Here';
function doGet() {
var app = UiApp.createApplication().setTitle('Loan Registration Processing');
var panel = app.createVerticalPanel();
var grid = app.createGrid(8,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.createTextBox().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');
propType.addItem('Manufactured');
var submitButton = app.createButton('Submit');
//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, submitButton)
//Event Handler
var handler = app.createServerClickHandler('insertInSS');
handler.addCallbackElement(panel);
submitButton.addClickHandler(handler);
panel.add(grid);
app.add(panel);
return app;
}
//Function to insert data in the sheet on clicking the submit button
function insertInSS(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;
//app.getElementById('info').setVisible(true).setStyleAttribute('color','red');
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]]);
return app;
}
here is the code properly formatted :
// Script-as-app template.
var submissionSSKey = '*************';
function doGet(e) {
var app = UiApp.createApplication().setTitle('Loan Registration Processing');
var panel = app.createFormPanel();
var grid = app.createGrid(8,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');
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('File Upload');
var upLoad = (app.createFileUpload().setName('thefile'));
//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, submitButton)
.setWidget(7, 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;
//app.getElementById('info').setVisible(true).setStyleAttribute('color','red');
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 doc = DocsList.createFile(fileBlob);
return app
}