Invoice CRUD, get and send data with JSON to Google Sheets

bysemicon -
0

 



Sheet

Invoice IDProduct ListNet priceVat
Total product price

index.html

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Receipt Form</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
.receipt-container {
max-width: 700px;
margin: 20px auto;
padding: 20px;
border: 1px solid #ddd;
border-radius: 8px;
}
.table th, .table td {
vertical-align: middle;
text-align: center;
padding: 3px;
}
.totals {
font-weight: bold;
}
</style>
</head>
<body>
<div class="receipt-container">
<h1 id="head-title" class="text-center mb-4">ส่งข้อมูลใบเสร็จ</h1>

<div class="mb-4">
<label for="receiptNumber" class="form-label">ค้นหาใบเสร็จเลขที่</label>
<div class="input-group">
<input type="text" id="receiptNumber" class="form-control" placeholder="ปี-เดือน-วัน-เลขที่">
<button type="button" class="btn btn-primary" onclick="fetchData()">ค้นหา</button>
</div>
</div>

<!-- Table for items -->
<table class="table table-bordered text-center">
<thead class="table-secondary">
<tr>
<th>สินค้า</th>
<th>หน่วย</th>
<th>ราคา</th>
<th>รวม</th>
<th>จัดการ</th>
</tr>
</thead>
<tbody id="itemContainer">
<tr class="item">
<td><input type="text" name="goods" class="form-control" required></td>
<td><input type="number" name="unit" class="form-control" required oninput="updateTotal(this)"></td>
<td><input type="number" name="price" class="form-control" required oninput="updateTotal(this)"></td>
<td><input type="number" name="total" class="form-control" readonly></td>
<td><button type="button" class="btn btn-danger btn-sm" onclick="removeItem(this)">ลบ</button></td>
</tr>
</tbody>
</table>
<div class="mb-4 text-end">
<button type="button" class="btn btn-success mb-3" onclick="addItem()">เพิ่มรายการ</button>
</div>

<!-- Totals Section -->
<div class="mb-3 row">
<label for="subtotal" class="col-sm-4 col-form-label text-end">ยอดรวมสุทธิ:</label>
<div class="col-sm-8">
<input type="number" id="subtotal" class="form-control" readonly>
</div>
</div>
<div class="mb-3 row">
<label for="vat" class="col-sm-4 col-form-label text-end">ภาษี (7%):</label>
<div class="col-sm-8">
<input type="number" id="vat" class="form-control" readonly>
</div>
</div>
<div class="mb-3 row">
<label for="netTotal" class="col-sm-4 col-form-label text-end">ราคาสินค้า:</label>
<div class="col-sm-8">
<input type="number" id="netTotal" class="form-control" readonly>
</div>
</div>

<!-- Buttons for actions -->
<div class="text-center">
<input type="button" value="ส่งข้อมูล" class="btn btn-primary me-2" onclick="submitData()">
<input type="button" value="อัปเดตข้อมูล" class="btn btn-warning" onclick="updateData()">
</div>

<div id="response" class="mt-4 text-center"></div>
</div>

<script>
function addItem() {
document.getElementById("head-title").innerText = '';
const itemContainer = document.getElementById("itemContainer");
const newItem = document.createElement("tr");
newItem.className = "item";
newItem.innerHTML = `
<td><input type="text" name="goods" class="form-control" required></td>
<td><input type="number" name="unit" class="form-control" required oninput="updateTotal(this)"></td>
<td><input type="number" name="price" class="form-control" required oninput="updateTotal(this)"></td>
<td><input type="number" name="total" class="form-control" readonly></td>
<td><button type="button" class="btn btn-danger btn-sm" onclick="removeItem(this)">ลบ</button></td>
`;
itemContainer.appendChild(newItem);
updateAllTotals();
document.getElementById("head-title").innerText = 'ส่งข้อมูลใบเสร็จ';
}

function removeItem(button) {
const item = button.closest('tr');
item.parentNode.removeChild(item);
updateAllTotals();
}

function updateTotal(input) {
const item = input.closest('tr');
const unit = parseFloat(item.querySelector("input[name='unit']").value) || 0;
const price = parseFloat(item.querySelector("input[name='price']").value) || 0;
const total = item.querySelector("input[name='total']");
total.value = unit * price;
updateAllTotals();
}

function updateAllTotals() {
const items = document.querySelectorAll("#itemContainer .item");
let subtotal = 0;

items.forEach(item => {
const total = parseFloat(item.querySelector("input[name='total']").value) || 0;
subtotal += total;
});

const vat = subtotal * 0.07;
const netTotal = subtotal - vat;

document.getElementById("subtotal").value = subtotal.toFixed(2);
document.getElementById("vat").value = vat.toFixed(2);
document.getElementById("netTotal").value = netTotal.toFixed(2);
}



function fetchData() {
const receiptNumber = document.getElementById("receiptNumber").value;
google.script.run.withSuccessHandler(function(data) {
if (data) {
loadFormData(data);
} else {
alert("ไม่พบข้อมูลใบเสร็จที่ค้นหา");
}
}).getDataByReceiptNumber(receiptNumber);
}

function loadFormData(data) {
document.getElementById("head-title").innerText = '';
document.getElementById("itemContainer").innerHTML = '';
data.items.forEach(item => {
const newItem = document.createElement("tr");
newItem.className = "item";
newItem.innerHTML = `
<td><input type="text" name="goods" class="form-control" value="${item.goods}" required></td>
<td><input type="number" name="unit" class="form-control" value="${item.unit}" required oninput="updateTotal(this)"></td>
<td><input type="number" name="price" class="form-control" value="${item.price}" required oninput="updateTotal(this)"></td>
<td><input type="number" name="total" class="form-control" value="${item.total}" readonly></td>
<td><button type="button" class="btn btn-danger btn-sm" onclick="removeItem(this)">ลบ</button></td>
`;
document.getElementById("itemContainer").appendChild(newItem);
});
document.getElementById("head-title").innerText = 'แก้ไขข้อมูล';
document.getElementById("subtotal").value = data.subtotal;
document.getElementById("vat").value = data.vat;
document.getElementById("netTotal").value = data.netTotal;
}

function submitData() {
const items = collectItems();
const subtotal = parseFloat(document.getElementById("subtotal").value);
const vat = parseFloat(document.getElementById("vat").value);
const netTotal = parseFloat(document.getElementById("netTotal").value);

google.script.run.withSuccessHandler(function(response) {
document.getElementById("response").innerText = response;
resetForm();
}).submitForm({ items, subtotal, vat, netTotal });
}

function updateData() {
const receiptNumber = document.getElementById("receiptNumber").value;
const items = collectItems();
const subtotal = parseFloat(document.getElementById("subtotal").value);
const vat = parseFloat(document.getElementById("vat").value);
const netTotal = parseFloat(document.getElementById("netTotal").value);

google.script.run.withSuccessHandler(function(response) {
document.getElementById("response").innerText = response;
}).updateForm({ receiptNumber, items, subtotal, vat, netTotal });
}

function collectItems() {
const items = [];
document.querySelectorAll("#itemContainer .item").forEach((input, i) => {
const goods = input.querySelector("input[name='goods']").value;
const unit = parseInt(input.querySelector("input[name='unit']").value);
const price = parseFloat(input.querySelector("input[name='price']").value);
const total = parseFloat(input.querySelector("input[name='total']").value);
items.push({ number: i + 1, goods, unit, price, total });
});
return items;
}

function resetForm() {
document.getElementById("itemContainer").innerHTML = '';
addItem();
document.getElementById("subtotal").value = '';
document.getElementById("vat").value = '';
document.getElementById("netTotal").value = '';
}
</script>
</body>
</html>


code.gs

function doGet() {
return HtmlService.createHtmlOutputFromFile('index');
}


function submitForm(data) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const receiptNumber = generateReceiptNumber(sheet);
const items = JSON.stringify(data.items);
sheet.appendRow([receiptNumber, items, data.subtotal, data.vat, data.netTotal]);

return `ข้อมูลถูกส่งเรียบร้อยแล้ว! ใบเสร็จเลขที่: ${receiptNumber}`;
}


function generateReceiptNumber(sheet) {
const today = new Date();
const datePrefix = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");

const lastRow = sheet.getLastRow();
let lastNumber = "00000";

if (lastRow > 1) {
const lastReceipt = sheet.getRange(lastRow, 1).getValue();
if (lastReceipt.startsWith(datePrefix)) {
lastNumber = lastReceipt.split('-').pop();
}
}
return `${datePrefix}-${('00000' + (parseInt(lastNumber) + 1)).slice(-5)}`;
}


function getDataByReceiptNumber(receiptNumber) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][0] === receiptNumber) {
return {
items: JSON.parse(data[i][1]),
subtotal: data[i][2],
vat: data[i][3],
netTotal: data[i][4],
};
}
}
return null;
}


function updateForm(data) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const dataRange = sheet.getDataRange();
const values = dataRange.getValues();

for (let i = 1; i < values.length; i++) {
if (values[i][0] === data.receiptNumber) {
sheet.getRange(i + 1, 2, 1, 4).setValues([[JSON.stringify(data.items), data.subtotal, data.vat, data.netTotal]]);
return `ข้อมูลถูกอัปเดตเรียบร้อยแล้ว! ใบเสร็จเลขที่: ${data.receiptNumber}`;
}
}
return "ไม่พบใบเสร็จที่ค้นหา";
}


ถ้ามีประโยชน์โปรดสนับสนุนเพื่อเป็นกำลังใจในการพัฒนาต่อไป




แสดงความคิดเห็น (0)
ใหม่กว่า เก่ากว่า