I have created an HTML web app form to allow submitter to submit the data with attachment (multiple files) attachment and maths function is not working. However, I am able to record the form data in the sheet.
I want to allow submitter to submit data from form and attachment links should be updated in sheet.
Please help me to identify fix the issue.
Script is attached below, Google Sheet
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('index').setTitle("Testing");
}
function getOAuthToken() {
DriveApp.getRootFolder();
Logger.log(ScriptApp.getOAuthToken())
return ScriptApp.getOAuthToken();
}
function getCurrentUserEmail()
{
var email={'email':Session.getActiveUser().getEmail()};//returned as an object
return email;
}
function saveDate(data) {
var openSpreadSheet = SpreadsheetApp.openById("1fA5qPNfoegZoEpl1al3XudNEyvomgAbko5XDBA_i7aw").getSheetByName("Sheet1");
var sheetHeader = openSpreadSheet.getRange(1, 1, 1, openSpreadSheet.getLastColumn()).getValues()[0];
Logger.log(sheetHeader);
var arrayToSave = [];
data["Timestamp"] = Utilities.formatDate(new Date, "IST", "dd/MM/yyyy hh:mm:ss");
data["Email Address"] = Session.getActiveUser().getEmail().toString();
for (i = 0; i < sheetHeader.length; i++) {
if (Object.keys(data).indexOf(sheetHeader[i]) != -1) {
arrayToSave[i] = data[sheetHeader[i]];
} else {
arrayToSave[i] = "";
}
}
openSpreadSheet.appendRow(arrayToSave);
}
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>123</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css?family=Roboto" rel="stylesheet">
<!--Icons-->
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<!--[if lt IE 9]>
<script src="js/html5shiv.js"></script>
<script src="js/respond.min.js"></script>
<![endif]-->
<style>
/*.form-group.num, .form-group.txt {
margin-bottom:0; width:100px; display:block; clear:both; margin:0 auto}
.form-group.txt { width:inherit;}*/
th { text-align: center;
background-color: #e3f2fd;color:#263238 !important}
tr.one th{
background-color: #f5f5f5;color:#263238 !important}
.navbar-brand {
padding: 0px;
}
.navbar-brand>img {
height: 100%;
padding: 15px;
width: auto;
}
.navbar-default { background-color:#0a3474}
.navbar-default .navbar-nav>.active>a, .navbar-default .navbar-nav>.active>a:focus, .navbar-default .navbar-nav>.active>a:hover {
color: #0a3474;
background-color: #FFF;
}
.navbar-default .navbar-nav>li>a{
color:#fff}
.navbar-default .navbar-nav>li>a:focus, .navbar-default .navbar-nav>li>a:hover{
color: #0a3474;
background-color: #FFF;
}
.gap10{
height:10px; display:block; clear:both;}
th {
text-align: center;
background-color: #009e16;
color: #fff !important;}
</style>
<style>
#attachmentTable{
background: white;
}
tr th,tr td{
text-align: center;
}
</style>
</head>
<body>
<div class="container">
<div class="row">
<nav class="navbar navbar-default">
<div class="container-fluid">
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar1">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href=""><img src="" alt="Dispute Bills">
</a>
</div>
<ul class="nav navbar-nav navbar-right">
<li><a href="#"><span class="glyphicon glyphicon-user"></span> : <h9 id="email"></h9></a></li>
</ul>
</div>
</nav>
</div>
</div>
<hr>
<form name="newpurchase" action="" method="post" enctype="application/x-www-form-urlencoded">
<div class="container">
<table class="table table-bordered" style="width:65%;margin:0 auto">
<thead>
<tr>
<th colspan="4">Procurement Request Form</th>
</tr>
</thead>
<tbody>
<tr>
<td>Request Type </td>
<td>
<div class="form-group">
<select name="supplier_name" class="form-control select" id="RequestType">
<option value="Supply">Supply</option>
<option value="Service">Service</option>
<option value="Software">Software</option>
</select></div></td>
<td>Urgency</td>
<td> <div class="form-group"><select name="supplier_name" class="form-control select"id="Urgency" >
<option value="Low">Low</option>
<option value="Medium">Medium</option>
<option value="High">High</option>
<option value="Critical">Critical</option>
</select></div></td>
</tr>
<tr>
<td>Requester For</td>
<td> <div class="form-group"><select name="supplier_name" class="form-control select" id="RequestFor" >
<option value="Cost for Approval">Cost for Approval</option>
<option value="PO Process">PO Process</option>
</select></div></td>
<td>Currency</td>
<td> <div class="form-group"><select name="payment_mode" id="select" class="form-control select" >
<option value="INR">INR</option>
<option value="USD">USD</option>
</select></div></td>
</tr>
<tr>
<td>Requester Name </td>
<td>
<div class="form-group"><input type="text" name="contact_person" class="form-control input-md" id="RequesterName"/></div></td>
<td>Business User</td>
<td><input type="text" name="contact_person" class="form-control input-md" id="BusinessUser"/></td>
</tr>
<tr>
<td>Short Description:</td>
<td><textarea class="form-control input-md" id="ShortDescription" name="address_line2"></textarea></td>
<td>Business Reason:</td>
<td><textarea class="form-control input-md" id="BusinessReasonforRequestPurpose" name="address_line2"></textarea></td>
</tr>
</tbody>
</table>
</div>
<hr>
<div class="container">
<td><table id="productList" class="table table-bordered">
<tr>
<th colspan="9">Requested Material/Service Details-Cost</th>
</tr>
<tbody>
<tr id="head">
<td>SR No</td>
<td>Product/Material/ServiceDescription</td>
<td>Make & Model No</td>
<td>UOM</td>
<td>Unit Price</td>
<td>Quantity</td>
<td>Total Price </td>
<td>GST% </td>
<td>Total Price with GST </td>
</tr>
<tr>
<td align="center">1</td>
<td><div class="form-group"><input class="form-control input-md" name="purchase_description" type="text" id="ProductMaterialServiceDescription1"></div></td>
<td><div class="form-group"><input class="form-control input-md" name="on_hand" type="text" id="MakeModelNo1"></div></td>
<td> <div class="form-group"><select class="form-control " name="productname" id="UOM1" >
<option value=""></option>
<option value="">Nos</option>
<option value="">Set</option>
<option value="">LS</option>
<option value="">Per MDY</option>
<option value="">Per Month</option>
<option value="">Per Year</option>
</select></div></td>
<td><div class="form-group"><input class="form-control input-md" class="price" data-a-sign="` " data-w-empty="zero" type="text" id="UnitPrice1"name="price"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="qty" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="QTY1"name="qty"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="subtot" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="Price1"name="subtot"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="gst" data-a-sign="` " data-w-empty="zero" type="text" id="GST1" name="gst"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="total" data-a-sign="` " data-w-empty="zero" type="text" id="TotalPrice1"name="total"/></div></td>
</tr>
<tr>
<td align="center">2</td>
<td><div class="form-group"><input class="form-control input-md" name="purchase_description" type="text" id="ProductMaterialServiceDescription2"></div></td>
<td><div class="form-group"><input class="form-control input-md" name="on_hand" type="text" id="MakeModelNo2"></div></td>
<td><div class="form-group"><select class="form-control " name="productname" id="UOM2" >
<option value=""></option>
<option value="">Nos</option>
<option value="">Set</option>
<option value="">LS</option>
<option value="">Per MDY</option>
<option value="">Per Month</option>
<option value="">Per Year</option>
</select></div></td>
<td><div class="form-group"><input class="form-control input-md" class="price" data-a-sign="` " data-w-empty="zero" type="text" id="UnitPrice2"name="price"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="qty" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="QTY2"name="qty"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="subtot" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="Price2"name="subtot"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="gst" data-a-sign="` " data-w-empty="zero" type="text" id="GST2" name="gst"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="total" data-a-sign="` " data-w-empty="zero" type="text" id="TotalPrice2"name="total"/></div></td>
</tr>
<tr>
<td align="center">3</td>
<td><div class="form-group"><input class="form-control input-md" name="purchase_description" type="text" id="ProductMaterialServiceDescription3"></div></td>
<td><div class="form-group"><input class="form-control input-md" name="on_hand" type="text" id="MakeModelNo3"></div></td>
<td><div class="form-group"><select class="form-control " name="productname" id="UOM3" >
<option value=""></option>
<option value="">Nos</option>
<option value="">Set</option>
<option value="">LS</option>
<option value="">Per MDY</option>
<option value="">Per Month</option>
<option value="">Per Year</option>
</select></div></td>
<td><div class="form-group"><input class="form-control input-md" class="price" data-a-sign="` " data-w-empty="zero" type="text" id="UnitPrice3"name="price"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="qty" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="QTY3"name="qty"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="subtot" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="Price3"name="subtot"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="gst" data-a-sign="` " data-w-empty="zero" type="text" id="GST3" name="gst"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="total" data-a-sign="` " data-w-empty="zero" type="text" id="TotalPrice3"name="total"/></div></td>
</tr>
<tr>
<td align="center">4</td>
<td><div class="form-group"><input class="form-control input-md" name="purchase_description" type="text" id="ProductMaterialServiceDescription4"></div></td>
<td><div class="form-group"><input class="form-control input-md" name="on_hand" type="text" id="MakeModelNo4"></div></td>
<td><div class="form-group"><select class="form-control " name="productname" id="UOM4" >
<option value=""></option>
<option value="">Nos</option>
<option value="">Set</option>
<option value="">LS</option>
<option value="">Per MDY</option>
<option value="">Per Month</option>
<option value="">Per Year</option>
</select></div></td>
<td><div class="form-group"><input class="form-control input-md" class="price" data-a-sign="` " data-w-empty="zero" type="text" id="UnitPrice4"name="price"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="qty" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="QTY4"name="qty"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="subtot" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="Price4"name="subtot"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="gst" data-a-sign="` " data-w-empty="zero" type="text" id="GST4" name="gst"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="total" data-a-sign="` " data-w-empty="zero" type="text" id="TotalPrice4"name="total"/></div></td>
</tr>
<tr>
<td align="center">5</td>
<td><div class="form-group"><input class="form-control input-md" name="purchase_description" type="text" id="ProductMaterialServiceDescription5"></div></td>
<td><div class="form-group"><input class="form-control input-md" name="on_hand" type="text" id="MakeModelNo5"></div></td>
<td width="120"><div class="form-group"><select class="form-control " name="productname" id="UOM5" >
<option value=""></option>
<option value="">Nos</option>
<option value="">Set</option>
<option value="">LS</option>
<option value="">Per MDY</option>
<option value="">Per Month</option>
<option value="">Per Year</option>
</select></div></td>
<td><div class="form-group"><input class="form-control input-md" class="price" data-a-sign="` " data-w-empty="zero" type="text" id="UnitPrice5"name="price"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="qty" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="QTY5"name="qty"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="subtot" type="text" data-a-sign="%" data-p-sign="s" data-w-empty="zero" id="Price5"name="subtot"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="gst" data-a-sign="` " data-w-empty="zero" type="text" id="GST5" name="gst"/></div></td>
<td><div class="form-group"><input class="form-control input-md" class="total" data-a-sign="` " data-w-empty="zero" type="text" id="TotalPrice5"name="total"/></div></td>
</tr>
</tbody>
</table></td>
</div>
<body style='font-family: cursive;'>
<div>
<left><button onclick='getOAuthToken()' style="margin-top:50px;outline:0;" class="btn btn-success">Upload File(s)</button></left>
<div class='table-responsive' style="display:none;" id="attachmentTableDiv">
<table id="attachmentTable" class="table table-bordered" style="width:900px;margin:20px auto;float:none;">
<thead>
<tr style="background:#f1f1f1;">
<th>Title</th>
<th>ID</th>
<th>URL</th>
<th>Date Created</th>
<th>Download</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
<hr>
<div class="container">
<div class="col-md-2 col-md-offset-5">
<button style="width: 100%;" type="submit" id="updateBtn" class="btn-btn-success" onclick="submitForm(); return false;">Submit</button>
</div>
</div>
<div class="container">
<div class="input-field col s12" id = "progress">
</div>
</div>
<div id="success" style="display:none">
<p>Your Data has been successfully uploaded.</p>
</div>
<hr>
<script>
var DEVELOPER_KEY = 'AIzaSyD9UvBsf_wdqxpTFgxzWhRtaI-VfITb_RY';
var pickerApiLoaded = false;
/**
* Loads the Google Picker API.
*/
function onApiLoad() {
gapi.load('picker', {'callback': function() {
pickerApiLoaded = true;
}});
}
function getOAuthToken() {
google.script.run.withSuccessHandler(createPicker).getOAuthToken();
}
function createPicker(token) {
if (pickerApiLoaded && token) {
// var all = new google.picker.DocsView(google.picker.ViewId.DOCS); //To upload from Google Drive
var uploadview = new google.picker.DocsUploadView().setParent('0B0Oj_1v-CAzNYmFMRkVUZjd0aHM'); //To upload from local machine..Add you google drive folder
var picker = new google.picker.PickerBuilder()
.addView(uploadview)
//.addView(all)
.hideTitleBar()
//.setLocale('nl') //--Regional language settings
//.enableFeature(google.picker.Feature.NAV_HIDDEN)
.setOAuthToken(token)
.setSize(536, 350)
.setDeveloperKey(DEVELOPER_KEY)
.setCallback(pickerCallback)
.setOrigin(google.script.host.origin)
.enableFeature(google.picker.Feature.MULTISELECT_ENABLED)
.build();
picker.setVisible(true);
} else {
showError('Unable to load the file picker.');
}
}
/**
* A callback function that extracts the chosen document's metadata from the response object.
*/
function pickerCallback(data) {
var action = data[google.picker.Response.ACTION];
if (action == google.picker.Action.PICKED) {
for(var i in data[google.picker.Response.DOCUMENTS]){
var doc = data[google.picker.Response.DOCUMENTS][i];
var id = doc[google.picker.Document.ID];
var url = doc[google.picker.Document.URL];
var title = doc[google.picker.Document.NAME];
var dateCreated = doc[google.picker.Document.LAST_EDITED_UTC];
var date = new Date(dateCreated);
date=date.toLocaleString();
$('#attachmentTable tbody').append("<tr><td>"+title+"</td><td>"+id+"</td><td><a href='"+url+"' target='_blank'>Link</a></td><td>"+date+"</td><td><a href='https://drive.google.com/drive/uc?export=download&id="+id+"'><i class='glyphicon glyphicon-download-alt'></i></a></td></tr>");
$('#attachmentTableDiv').show();
}
}
}
</script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script>google.load("picker", "1", {callback:function(){pickerApiLoaded =!0}});</script>
<script>
var file,
reader = new FileReader();
reader.onloadend = function(e) {
if (e.target.error != null) {
showError("File " + file.name + " could not be read.");
return;
} else {
google.script.run
.withSuccessHandler(showSuccess)
.uploadFileToGoogleDrive(e.target.result, file.name, $('input#name').val(), $('input#email').val());
}
};
function showSuccess(e) {
if (e === "OK") {
$('#forminner').hide();
$('#success').show();
} else {
showError(e);
}
}
function submitForm() {
var files = $('#files')[0].files;
if (files.length === 0) {
showError("Please select a file to upload");
return;
}
file = files[0];
if (file.size > 1024 * 1024 * 5) {
showError("File Size is more than 5 MB");
return;
}
showMessage("Uploading file..");
reader.readAsDataURL(file);
}
function showError(e) {
$('#progress').addClass('red-text').html(e);
}
function showMessage(e) {
$('#progress').removeClass('red-text').html(e);
}
</script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script>
$(function () {
$('.pnm, .price, .subtot, .grdtot');
var $tblrows = $("#productList tbody tr");
$tblrows.each(function (index) {
var $tblrow = $(this);
$tblrow.find('.qty').on('change', function () {
var qty = $tblrow.find("[name=qty]").val();
var price = $tblrow.find("[name=price]").val();
var subTotal = parseInt(qty, 10) * parseFloat(price);
if (!isNaN(subTotal)) {
$tblrow.find('.subtot').val(subTotal.toFixed(2));
var grandTotal = 0;
$(".subtot").each(function () {
var stval = parseFloat($(this).val());
grandTotal += isNaN(stval) ? 0 : stval;
});
$('.grdtot').val(grandTotal.toFixed(2));
}
});
});
});
var $price = $("input[name='subtot']"),
$percentage = $("input[name='gst']").on("input", calculatePrice),
$total = $("input[name='total']").on("input", calculatePerc);
function calculatePrice() {
var percentage = $(this).val();
var price = parseFloat($(this).closest('tr').find('.subtot').val());
var calcPrice = (price + ( price * percentage / 100 )).toFixed(2);
$(this).closest('tr').find('.total').val(calcPrice);
}
function calculatePerc() {
var total = $(this).val();
var price = $price.val();
var calcPerc = 100 - (total * 100 / price);
$percentage.val( calcPerc );
}
</script>
<head>
<base target="_top">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script>
$(function() {
google.script.run
.withSuccessHandler(dispEmail)
.getCurrentUserEmail();
});//runs after dom is loaded
function dispEmail(data)
{
$('#email').text(data.email);//put's email into h1 tag
}
console.log('My Code');
function submitForm(btnClicked) {
$("button").attr("disabled", true);
var jsonObj = {};
jsonObj["Requester Name"] = $("#RequesterName").val();
jsonObj["Business User"] = $("#BusinessUser").val();
jsonObj["Request Type"] = $("#RequestType").val();
jsonObj["Request For"] = $("#RequestFor").val();
jsonObj["Short Description"] = $("#ShortDescription").val();
jsonObj["Business Reason for Request/Purpose"] = $("#BusinessReasonforRequestPurpose").val();
jsonObj["Urgency "] = $("#Urgency").val();
jsonObj["S. No(1)"] = $("#SNo1").val();
jsonObj["S. No(2)"] = $("#SNo2").val();
jsonObj["S. No(3)"] = $("#SNo3").val();
jsonObj["S. No(4)"] = $("#SNo4").val();
jsonObj["S. No(5)"] = $("#SNo5").val();
jsonObj["Product/Material/ServiceDescription(1)"] = $("#ProductMaterialServiceDescription1").val();
jsonObj["Product/Material/ServiceDescription(2)"] = $("#ProductMaterialServiceDescription2").val();
jsonObj["Product/Material/ServiceDescription(3)"] = $("#ProductMaterialServiceDescription3").val();
jsonObj["Product/Material/ServiceDescription(4)"] = $("#ProductMaterialServiceDescription4").val();
jsonObj["Product/Material/ServiceDescription(5)"] = $("#ProductMaterialServiceDescription5").val();
jsonObj["Make & Model No(1)"] = $("#MakeModelNo1").val();
jsonObj["Make & Model No(2)"] = $("#MakeModelNo2").val();
jsonObj["Make & Model No(3)"] = $("#MakeModelNo3").val();
jsonObj["Make & Model No(4)"] = $("#MakeModelNo4").val();
jsonObj["Make & Model No(5)"] = $("#MakeModelNo5").val();
jsonObj["UOM(1)"] = $("#UOM1").val();
jsonObj["UOM(2)"] = $("#UOM1").val();
jsonObj["UOM(3)"] = $("#UOM1").val();
jsonObj["UOM(4)"] = $("#UOM1").val();
jsonObj["UOM(5)"] = $("#UOM1").val();
jsonObj["QTY(1)"] = $("#QTY1").val();
jsonObj["QTY(2)"] = $("#QTY2").val();
jsonObj["QTY(3)"] = $("#QTY3").val();
jsonObj["QTY(4)"] = $("#QTY4").val();
jsonObj["QTY(5)"] = $("#QTY5").val();
jsonObj["Unit Price(1)"] = $("#UnitPrice1").val();
jsonObj["Unit Price(2)"] = $("#UnitPrice2").val();
jsonObj["Unit Price(3)"] = $("#UnitPrice3").val();
jsonObj["Unit Price(4)"] = $("#UnitPrice4").val();
jsonObj["Unit Price(5)"] = $("#UnitPrice5").val();
jsonObj["Price(1)"] = $("#Price1").val();
jsonObj["Price(2)"] = $("#Price2").val();
jsonObj["Price(3)"] = $("#Price3").val();
jsonObj["Price(4)"] = $("#Price4").val();
jsonObj["Price(5)"] = $("#Price5").val();
jsonObj["GST(1)"] = $("#GST1").val();
jsonObj["GST(2)"] = $("#GST2").val();
jsonObj["GST(3)"] = $("#GST3").val();
jsonObj["GST(4)"] = $("#GST4").val();
jsonObj["GST(5)"] = $("#GST5").val();
jsonObj["Total Price(1)"] = $("#TotalPrice1").val();
jsonObj["Total Price(2)"] = $("#TotalPrice2").val();
jsonObj["Upload the documents )"] = $("#files").val();
jsonObj["updateBtn"] = $(btnClicked).text();
google.script.run.withSuccessHandler(afterSaving).saveDate(jsonObj);
return false;
}
function afterSaving() {
alert("Thanks, Your response has been recorded");
$("button").attr("disabled", false);
}
</script>