I have a web form made from google app script that accept data and add the data in google sheet. I also want to add a file upload input but it breaks when I add the that, only only input gene_by coming in the sheet. I also want to add code that will save the attached file on google drive and its link in google sheet.
code.gs
function doGet(request) {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function processForm(formObject){
var url="https://docs.google.com/spreadsheets/d/1eqq5nVtPZIZP1zun0i6XFsGUDquDxaelWhqPUF_oGS4/edit";
var ss= SpreadsheetApp.openByUrl(url);
var ws=ss.getSheetByName("Sheet1");
var timestamp = Utilities.formatDate(new Date(),"GMT+0530","dd-MM-yyyy HH:mm:ss");
ws.appendRow([
formObject.expa_number,
timestamp,
formObject.gene_by,
formObject.company_name,
formObject.bill_number,
formObject.bill_date,
formObject.party_name,
formObject.description,
formObject.ledger1,
formObject.amount1,
formObject.ledger2,
formObject.amount2,
formObject.ledger3,
formObject.amount3,
formObject.ledger4,
formObject.amount4,
formObject.gst_app,
formObject.tds_app,
formObject.tds_rate,
formObject.payment_status,
formObject.paid_by,
formObject.payment_mode,
formObject.total_amount,
formObject.attach_bill
]);
}
function getEmployee() {
var ss = SpreadsheetApp.openById('1uuOUGssKRCEq8YG7P1R0V33-d6C6p6BoVXa-COckqLo');
var sheet = ss.getSheetByName('Employee Database')
var list = sheet.getRange(2, 2, sheet.getLastRow()-1, 1).getValues();
return list
}
function getParty() {
var ss = SpreadsheetApp.openById('1ntaSlBcWpSJ35ygGpKxUk3QZ-Ipd3CilKtr3_GXlp7w');
var sheet = ss.getSheetByName('Ledger Names')
var list = sheet.getRange(2, 7, sheet.getRange('G2:G').getValues().filter(String).length, 1).getValues();
return list
}
function getCompany() {
var ss = SpreadsheetApp.openById('1ntaSlBcWpSJ35ygGpKxUk3QZ-Ipd3CilKtr3_GXlp7w');
var sheet = ss.getSheetByName(`Company's Bank Details`)
var list = sheet.getRange(2, 8, sheet.getRange('H2:H').getValues().filter(String).length, 1).getValues();
return list
}
function getExpenseLedger() {
var ss = SpreadsheetApp.openById('1ntaSlBcWpSJ35ygGpKxUk3QZ-Ipd3CilKtr3_GXlp7w');
var sheet = ss.getSheetByName(`Expense Ledgers | Headings`)
var list = sheet.getRange(2, 9, sheet.getRange('I2:I').getValues().filter(String).length, 1).getValues();
return list
}
index.html
<!DOCTYPE html>
<html>
<head>
<!-- Select2 CSS -->
<link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/css/select2.min.css" rel="stylesheet" />
<!-- jQuery --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<!-- Select2 JS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/js/select2.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css"
integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2"
crossorigin="anonymous">
<?!= include('CSS'); ?>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-12">
<form id="myForm" onsubmit="handleFormSubmit(this)">
<p class="h4 mb-4 text-center">EXPENSES VOUCHER FORM</p>
<div class="form-row">
<div class="form-group col-md-12">
<label for="company_name">Company Name</label>
<select type="search" class="form-control" name="company_name" id="company_name" required>
<option value="" selected hidden> -- select an option -- </option>
<option></option>
</select>
</div>
</div> <!--O1-->
<div class="form-row">
<div class="form-group col-md-4">
<label for="expa_number">EXPA Number?</label>
<input type="text" class="form-control" id="expa_number" name="expa_number" placeholder="EXPA#" readonly required>
</div>
<div class="form-group col-md-4">
<label for="expa_date">EXPA Date?</label>
<input type="date" class="form-control" id="expa_date" name="expa_date" placeholder="EXPA Date" readonly required>
</div>
<div class="form-group col-md-4">
<label for="gene_by">Generated By?</label>
<select class="form-control" name="gene_by" id="gene_by" required>
<option>Dipali Chauhan</option>
</select>
</div>
</div> <!--O2-->
<div class="form-row">
<div class="form-group col-md-4">
<label style="font-size: 14px" for="payment_status">Payment Status? (Select Paid Only if Paid by employee)</label>
<select class="form-control" name="payment_status" id="payment_status" required>
<option disabled selected value> -- select an option -- </option>
<option value="Paid">Paid</option>
<option value="Not_Paid">Not Paid</option>
</select>
</div>
<div class="form-group col-md-4">
<label for="paid_by">Paid By?</label>
<select class="form-control" name="paid_by" id="paid_by" required>
<option disabled selected value> -- select an option -- </option>
</select>
</div>
<div class="form-group col-md-4">
<label for="payment_mode">Mode of Payment?</label>
<input type="text" class="form-control" id="payment_mode" name="payment_mode" placeholder="Mode of Payment" required>
</div>
</div> <!--O3-->
<div class="form-row">
<div class="form-group col-md-12">
<label for="description">Description</label>
<input type="text" class="form-control" id="description" name="description" placeholder="Description" required>
</div>
</div> <!--O4-->
<div class="form-row">
<div class="form-group col-md-4">
<label for="party_name">Party Name?</label>
<select class="form-control" name="party_name" id="party_name" required>
<option disabled selected value> -- select an option -- </option>
</select>
</div>
<div class="form-group col-md-4">
<label for="bill_number">Bill Number</label>
<input type="text" class="form-control" id="bill_number" name="bill_number" placeholder="Bill Number" required>
</div>
<div class="form-group col-md-4">
<label for="bill_date">Bill Date</label>
<input type="date" class="form-control" id="bill_date" name="bill_date" placeholder="Bill Date" required>
</div>
</div> <!--O5-->
<div class="form-row">
<div class="form-group col-md-3">
<label for="ledger1">Ledger Heading 1</label>
<select class="form-control" name="ledger1" id="ledger1" required>
<option disabled selected value> -- select an option -- </option>
</select>
</div>
<div class="form-group col-md-3">
<label for="amount1">Amount 1</label>
<input type="number" class="form-control" id="amount1" name="amount1" placeholder="Amount 1" required>
</div>
<div class="form-group col-md-3">
<label for="ledger2">Ledger Heading 2</label>
<select class="form-control" name="ledger2" id="ledger2">
<option disabled selected value> -- select an option -- </option>
</select>
</div>
<div class="form-group col-md-3">
<label for="amount2">Amount 2</label>
<input type="number" class="form-control" id="amount2" name="amount2" placeholder="Amount 2">
</div>
</div> <!--O6-->
<div class="form-row">
<div class="form-group col-md-3">
<label for="ledger3">Ledger Heading 3</label>
<select class="form-control" name="ledger3" id="ledger3">
<option disabled selected value> -- select an option -- </option>
</select>
</div>
<div class="form-group col-md-3">
<label for="amount3">Amount 3</label>
<input type="number" class="form-control" id="amount3" name="amount3" placeholder="Amount 3">
</div>
<div class="form-group col-md-3">
<label for="ledger4">Ledger Heading 4</label>
<select class="form-control" name="ledger4" id="ledger4">
<option disabled selected value> -- select an option -- </option>
</select>
</div>
<div class="form-group col-md-3">
<label for="amount4">Amount 4</label>
<input type="number" class="form-control" id="amount4" name="amount4" placeholder="Amount 4">
</div>
</div> <!--O7-->
<div class="form-row">
<div class="form-group col-md-3">
<label for="gst_app">GST Applicable?</label>
<select class="form-control" name="gst_app" id="gst_app" required>
<option disabled selected value> -- select an option -- </option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</select>
</div>
<div class="form-group col-md-3">
<label for="tds_app">TDS Applicable?</label>
<select class="form-control" name="tds_app" id="tds_app" required>
<option disabled selected value> -- select an option -- </option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</select>
</div>
<div class="form-group col-md-3">
<label for="tds_rate">TDS Rate (%)?</label>
<input type="number" class="form-control" id="tds_rate" name="tds_rate" placeholder="TDS Rate" min="0.1" max="20" required>
</div>
<div class="form-group col-md-3">
<label for="total_amount">Total Bill Amount?</label>
<input type="number" class="form-control" id="total_amount" name="total_amount" placeholder="Total Bill Amount" required>
</div>
</div> <!--O8-->
<div class="form-row">
<div class="form-group col-md-12">
<label for="attach_bill">Attach Bill?</label>
<input type="text" class="form-control" id="attach_bill" name="attach_bill" placeholder="Attach Bill" readonly required>
</div>
</div> <!--O9-->
<!-- <div class="form-row">
<div class="form-group col-md-12">
<label for="attach_ledger">Attach Ledger?</label>
<input type="file" class="form-control" id="attach_ledger" name="attach_ledger" required>
</div>
</div> 10 -->
<button type="submit" class="btn btn-primary btn-block">Submit</button>
</form>
<div id="output"></div>
</div>
</div>
</div>
<script>
google.script.url.getLocation(function(location){
document.getElementById('expa_number').value = location.parameters.expa_number[0];
document.getElementById('attach_bill').value = location.parameters.attach_bill[0];
})
</script>
<?!= include('JavaScript'); ?>
</body>
</html>
JavaScript.html
<script>
$(document).ready(function(){
var arrayCompanyName = google.script.run.withSuccessHandler(function(ar) {
var companySelect = document.getElementById("company_name");
ar.forEach(function(pb)
{
let option = document.createElement("option");
option.textContent = pb[0];
companySelect.appendChild(option);
})
}).getCompany();
var arrayEmployeeName = google.script.run.withSuccessHandler(function(ar) {
var employeeSelect = document.getElementById("paid_by");
var employeeSelect2 = document.getElementById("gene_by");
ar.forEach(function(pb)
{
let option = document.createElement("option");
option.textContent = pb[0];
employeeSelect.appendChild(option);
})
ar.forEach(function(pb1)
{
let option = document.createElement("option");
option.textContent = pb1[0];
employeeSelect2.appendChild(option);
})
}).getEmployee();
var arrayPartyName = google.script.run.withSuccessHandler(function(ar) {
var partySelect = document.getElementById("party_name");
ar.forEach(function(pb)
{
let option = document.createElement("option");
option.textContent = pb[0];
partySelect.appendChild(option);
})
}).getParty();
var arrayLedgerName = google.script.run.withSuccessHandler(function(ar) {
var ledgerSelect1 = document.getElementById("ledger1");
var ledgerSelect2 = document.getElementById("ledger2");
var ledgerSelect3 = document.getElementById("ledger3");
var ledgerSelect4 = document.getElementById("ledger4");
ar.forEach(function(pb1)
{
let option = document.createElement("option");
option.textContent = pb1[0];
ledgerSelect1.appendChild(option);
})
ar.forEach(function(pb2)
{
let option = document.createElement("option");
option.textContent = pb2[0];
ledgerSelect2.appendChild(option);
})
ar.forEach(function(pb3)
{
let option = document.createElement("option");
option.textContent = pb3[0];
ledgerSelect3.appendChild(option);
})
ar.forEach(function(pb4)
{
let option = document.createElement("option");
option.textContent = pb4[0];
ledgerSelect4.appendChild(option);
})
}).getExpenseLedger();
$("#company_name").select2({
data: arrayCompanyName
});
$("#paid_by").select2({
data: arrayEmployeeName
});
$("#gene_by").select2({
data: arrayEmployeeName
});
$("#party_name").select2({
data: arrayPartyName
});
$("#ledger1").select2({
data: arrayLedgerName
});
$("#ledger2").select2({
data: arrayLedgerName
});
$("#ledger3").select2({
data: arrayLedgerName
});
$("#ledger4").select2({
data: arrayLedgerName
});
});
let paymetStaus = document.querySelector("#payment_status");
let paidBy = document.querySelector("#paid_by");
let modeOfPayment = document.querySelector("#payment_mode");
let amount1 = document.querySelector("#amount1");
let ledger2 = document.querySelector("#ledger2");
let ledger3 = document.querySelector("#ledger3");
let ledger4 = document.querySelector("#ledger4");
let amount2 = document.querySelector("#amount2");
let amount3 = document.querySelector("#amount3");
let amount4 = document.querySelector("#amount4");
let tdsApp = document.querySelector("#tds_app");
let tdsRate = document.querySelector("#tds_rate");
paidBy.disabled = true;
ledger2.disabled = true;
ledger3.disabled = true;
ledger4.disabled = true;
amount2.disabled = true;
amount3.disabled = true;
amount4.disabled = true;
modeOfPayment.disabled = true;
tds_rate.disabled = true;
paymetStaus.addEventListener("change", stateHandlePaid_by);
function stateHandlePaid_by() {
if (paymetStaus.value !== "Paid") {
paidBy.disabled = true;
modeOfPayment.disabled = true;
paid_by.value = "";
modeOfPayment.value = "";
} else {
paidBy.disabled = false;
modeOfPayment.disabled = false;
}
}
amount1.addEventListener("change", stateHandleLedger2);
function stateHandleLedger2() {
if (amount1.value !== "") {
ledger2.disabled = false;
amount2.disabled = false;
} else {
ledger2.disabled = true;
amount2.disabled = true;
ledger2.value = "";
amount2.value = "";
}
}
amount2.addEventListener("change", stateHandleLedger3);
function stateHandleLedger3() {
if (amount2.value !== "") {
ledger3.disabled = false;
amount3.disabled = false;
} else {
ledger3.disabled = true;
amount3.disabled = true;
ledger3.value = "";
amount3.value = "";
}
}
amount3.addEventListener("change", stateHandleLedger4);
function stateHandleLedger4() {
if (amount3.value !== "") {
ledger4.disabled = false;
amount4.disabled = false;
} else {
ledger4.disabled = true;
amount4.disabled = true;
ledger4.value = "";
amount4.value = "";
}
}
tds_app.addEventListener("change", stateHandleTdsRate);
function stateHandleTdsRate() {
if (tds_app.value !== "Yes") {
tds_rate.disabled = true;
tds_rate.value = "";
} else {
tds_rate.disabled = false;
}
}
$('#expa_date').val(new Date().toJSON().slice(0,10));
function preventFormSubmit(){
var forms=document.querySelectorAll('form');
for (var i=0;i<forms.length;i++){
forms[i].addEventListener('submit',function(event){
event.preventDefault();
});
}
}
window.addEventListener('load',preventFormSubmit);
function handleFormSubmit(formObject){
google.script.run.processForm(formObject);
$('#myForm').trigger("reset");
$('#myForm').find('select').each(function(){
$(this).change();
});
document.getElementById("myForm").reset();
}
</script>
CSS.html
<style>
.select2-selection__rendered {
line-height: 38px !important;
}
.select2-container .select2-selection--single {
height: 38px !important;
}
.select2-selection__arrow {
height: 38px !important;
}
</style>