- Joined
- Oct 5, 2016
- Posts
- 150
- Reaction
- 38
- Points
- 105
html
gs
Revise/fix my script. The table is not showing. Add table in my form - the database of the table is Sheet "Expenses" with Range C13:I to last row
Code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="expenseForm">
<label>Date:</label><input type="date" name="date"><br>
<label>Description:</label><input type="text" name="description"><br>
<label>Category:</label><select name="category" onchange="getSubcategories()"></select><br>
<label>Sub-Category:</label><select name="subCategory"></select><br>
<label>Amount:</label><input type="number" name="amount"><br>
<label>Lessed In:</label><select name="lessedIn"></select><br>
<label>Need/Want:</label><select name="needOrWant"></select><br>
<input type="button" value="Submit" onClick="submitForm()">
</form>
<div id="expensesList"></div>
<script>
// Get expenses and populate corresponding table
google.script.run.withSuccessHandler(function(expenseArray) {
var expensesList = document.getElementById("expensesList");
var table = document.createElement("table");
table.id = "expensesTable";
expensesList.appendChild(table);
var headerRow = document.createElement("tr");
table.appendChild(headerRow);
var headers = ["Date", "Description", "Category", "Sub-Category", "Amount", "Lessed In", "Need/Want"];
for (var i = 0; i < headers.length; i++) {
var headerCell = document.createElement("th");
headerCell.appendChild(document.createTextNode(headers[i]));
headerRow.appendChild(headerCell);
}
for (var i = 0; i < expenseArray.length; i++) {
var row = document.createElement("tr");
table.appendChild(row);
var rowData = [ expenseArray[i][0],
expenseArray[i][1],
expenseArray[i][2],
expenseArray[i][3],
expenseArray[i][4],
expenseArray[i][5],
expenseArray[i][6]
];
for (var j = 0; j < rowData.length; j++) {
var cell = document.createElement("td");
cell.appendChild(document.createTextNode(rowData[j]));
row.appendChild(cell);
}
}
// Add border to table
table.style.border = "1px solid #ddd";
}).getExpensesFromSheet();
function submitForm() {
var result = confirm('Do you want to add the entry?');
if (result) {
google.script.run.withSuccessHandler(function() {
alert('Expense details added successfully!');
document.getElementById('expenseForm').reset();
// Refresh expenses table
var table = document.getElementById("expensesTable");
table.parentNode.removeChild(table);
google.script.run.withSuccessHandler(function(expenseArray) {
var expensesList = document.getElementById("expensesList");
var table = document.createElement("table");
table.id = "expensesTable";
expensesList.appendChild(table);
var headerRow = document.createElement("tr");
table.appendChild(headerRow);
var headers = ["Date", "Description", "Category", "Sub-Category", "Amount", "Lessed In", "Need/Want"];
for (var i = 0; i < headers.length; i++) {
var headerCell = document.createElement("th");
headerCell.appendChild(document.createTextNode(headers[i]));
headerRow.appendChild(headerCell);
}
for (var i = 0; i < expenseArray.length; i++) {
var row = document.createElement("tr");
table.appendChild(row);
var rowData = [ expenseArray[i][0],
expenseArray[i][1],
expenseArray[i][2],
expenseArray[i][3],
expenseArray[i][4],
expenseArray[i][5],
expenseArray[i][6]
];
for (var j = 0; j < rowData.length; j++) {
var cell = document.createElement("td");
cell.appendChild(document.createTextNode(rowData[j]));
row.appendChild(cell);
}
}
// Add border to table
table.style.border = "1px solid #ddd";
}).getExpensesFromSheet();
}).addExpenseDetails(document.getElementById('expenseForm'));
}
}
// Get categories and need/want from Sheets and populate corresponding dropdowns
google.script.run.withSuccessHandler(function(categoryArray) {
var categoryDropdown = document.getElementsByName("category")[0];
for (var i = 0; i < categoryArray.length; i++) {
var option = document.createElement("option");
option.text = categoryArray[i];
categoryDropdown.add(option);
}
}).getCategoriesFromSheet();
function getSubcategories() {
var category = document.getElementsByName("category")[0].value;
google.script.run.withSuccessHandler(function(subcategoryArray) {
var subcategoryDropdown = document.getElementsByName("subCategory")[0];
subcategoryDropdown.innerHTML = ""; //Clear the current options
for (var i = 0; i < subcategoryArray.length; i++) {
var option = document.createElement("option");
option.text = subcategoryArray[i];
subcategoryDropdown.add(option);
}
}).getSubcategoriesFromSheet(category);
}
google.script.run.withSuccessHandler(function(needOrWantArray) {
var needOrWantDropdown = document.getElementsByName("needOrWant")[0];
for (var i = 0; i < needOrWantArray.length; i++) {
var option = document.createElement("option");
option.text = needOrWantArray[i];
needOrWantDropdown.add(option);
}
}).getNeedOrWantFromSheet();
google.script.run.withSuccessHandler(function(accountsArray) {
var lessedInDropdown = document.getElementsByName("lessedIn")[0];
for (var i = 0; i < accountsArray.length; i++) {
var option = document.createElement("option");
option.text = accountsArray[i];
lessedInDropdown.add(option);
}
}).getAccountsFromSheet();
</script>
</body>
</html>
Code:
function onOpen() {
SpreadsheetApp.getUi().createMenu('Expense Menu')
.addItem('Add New Expense', 'showForm')
.addToUi();
}
function showForm() {
var html = HtmlService.createHtmlOutputFromFile('ExpenseForm')
.setWidth(400)
.setHeight(600); // Increased height to accommodate expenses table
SpreadsheetApp.getUi().showModalDialog(html, 'Add New Expense');
}
function addExpenseDetails(data) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Expenses');
var lastRow = sheet.getLastRow();
var rowData = [[data.date, data.description, data.category, data.subCategory, data.amount, data.lessedIn, data.needOrWant]];
sheet.getRange(lastRow+1, 3, 1, 7).setValues(rowData);
}
function getCategoriesFromSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Categories');
var values = sheet.getRange("2:2").getValues()[0].filter(function(value){
return value !== ""; // Include only non-blank options in dropdown list
});
return values;
}
function getNeedOrWantFromSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Categories');
var values = sheet.getRange("A13:A14").getValues().filter(function(value){
return value !== ""; // Include only non-blank options in dropdown list
});
var needOrWantArray = [];
for (var i = 0; i < values.length; i++) {
needOrWantArray.push(values[i][0]);
}
return needOrWantArray;
}
function getAccountsFromSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Accounts');
var lastRow = sheet.getLastRow();
var values = sheet.getRange(3, 2, lastRow-2, 1).getValues().filter(function(value){
return value !== ""; // Include only non-blank options in dropdown list
});
var accountsArray = [];
for (var i = 0; i < values.length; i++) {
accountsArray.push(values[i][0]);
}
return accountsArray;
}
function getSubcategoriesFromSheet(category) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Categories');
var headers = sheet.getRange("2:2").getValues()[0];
var colIndex = headers.indexOf(category) + 1;
var lastRow = sheet.getLastRow();
var values = sheet.getRange(3, colIndex, lastRow-1, 1).getValues();
var subcategoryArray = [];
for (var i = 0; i < values.length; i++) {
if (values[i][0] !== "") { // Only include non-blank options in dropdown list
subcategoryArray.push(values[i][0]);
}
}
return subcategoryArray;
}
function getExpensesFromSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Expenses');
var lastRow = sheet.getLastRow();
var expensesRange = sheet.getRange("C13:I" + lastRow);
var expensesData = expensesRange.getValues();
return expensesData;
}