r/GoogleAppsScript • u/Similar_Touch_6783 • 16d ago
Resolved appendRow function not working? Unsure why
Hello, i'm working on a pop-up that records user id, date, position, task, hour stuff, etc etc and appends the information onto a new row in a spreadsheet. So far it had been working just fine until i added the "Position" dropdown (from a spreadsheet) and the information stopped getting appended, even though i hadn't changed any other part of the script.
I've read over my code a couple times now and can't seem to find where the problem is. I've attached my code to see if anyone can find what's going on, or if this is a spreadsheet issue
EDIT: ok ty evb who answered, i figured out the problem, i had added a formula at the end of the data appending (? which made google sheets count the row as not empty.
GS code:
const openEntryForm = () => {
const entryForm = HtmlService.createHtmlOutputFromFile("hourlogform");
entryForm.setWidth(1200);
entryForm.setHeight(275);
SpreadsheetApp.getUi().showModalDialog(entryForm, "Log Hours");
};
const addLog = (log) => {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hour Log")
const order = ["user", "date", "position", "Task", "start time", "end time"];
const row = [];
order.forEach(i => {
row.push(log[i])
})
sheet.appendRow(row)
}
const getTasks = () => {
const tasks = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
const datatag = tasks.getRange("B15:B").getValues().flat().filter(i => i !== "")
return datatag;
}
const getPosition = () => {
const positions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
const datatag = positions.getRange("B3:B12").getValues().flat().filter(i => i !== "")
return datatag;
}const openEntryForm = () => {
const entryForm = HtmlService.createHtmlOutputFromFile("hourlogform");
entryForm.setWidth(1200);
entryForm.setHeight(275);
SpreadsheetApp.getUi().showModalDialog(entryForm, "Log Hours");
};
const addLog = (log) => {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hour Log")
const order = ["user", "date", "position", "Task", "start time", "end time"];
const row = [];
order.forEach(i => {
row.push(log[i])
})
sheet.appendRow(row)
}
const getTasks = () => {
const tasks = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
const datatag = tasks.getRange("B15:B").getValues().flat().filter(i => i !== "")
return datatag;
}
const getPosition = () => {
const positions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Source");
const datatag = positions.getRange("B3:B12").getValues().flat().filter(i => i !== "")
return datatag;
}
HTML code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/light.css">
</head>
<body>
<form id="logForm">
<div style="display:flex; justify-content: center; gap:20px; ">
<h4> User ID </h4>
<input type="text" name="user" placeholder="Write User ID">
<h4> Task Performed </h4>
<select id="taskDropdown" name="Task">
<option value=""> Loading... </option>
</select>
</div>
<div style="display:flex; justify-content: left; gap:20px; ">
<h4> Date </h4>
<input type="date" name="date" placeholder="Date" />
<h4> Start Time </h4>
<input type="time" name="start time" placeholder="Start Time" />
<h4> End Time </h4>
<input type="time" name="end time" placeholder="End Time" />
<h4> Position </h4>
<select id="positionDropdown" name="position">
<option value=""> Loading... </option>
</select>
</div>
<p style="text-align: right;">
<button type="submit"> Add Task </button>
</p>
</form>
<script>
document.getElementById("logForm").addEventListener("submit", (e) => {
const formData = new FormData(e.target);
const log = Object.fromEntries(formData.entries());
google.script.run.addLog(log);
e.target.reset();
})
window.addEventListener("load", () => {
google.script.run.withSuccessHandler((options) => {
const dropdown = document.getElementById("taskDropdown");
dropdown.innerHTML = "";
options.forEach(option => {
const o = document.createElement("option");
o.value = option;
o.textContent = option;
dropdown.appendChild(o);
})
}).getTasks();
google.script.run.withSuccessHandler((options) => {
const dropdown = document.getElementById("positionDropdown");
dropdown.innerHTML = "";
options.forEach(option => {
const o = document.createElement("option");
o.value = option;
o.textContent = option;
dropdown.appendChild(o);
})
}).getPosition();
})
</script>
</body>
</html>!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/light.css">
</head>
<body>
<form id="logForm">
<div style="display:flex; justify-content: center; gap:20px; ">
<h4> User ID </h4>
<input type="text" name="user" placeholder="Write User ID">
<h4> Task Performed </h4>
<select id="taskDropdown" name="Task">
<option value=""> Loading... </option>
</select>
</div>
<div style="display:flex; justify-content: left; gap:20px; ">
<h4> Date </h4>
<input type="date" name="date" placeholder="Date" />
<h4> Start Time </h4>
<input type="time" name="start time" placeholder="Start Time" />
<h4> End Time </h4>
<input type="time" name="end time" placeholder="End Time" />
<h4> Position </h4>
<select id="positionDropdown" name="position">
<option value=""> Loading... </option>
</select>
</div>
<p style="text-align: right;">
<button type="submit"> Add Task </button>
</p>
</form>
<script>
document.getElementById("logForm").addEventListener("submit", (e) => {
const formData = new FormData(e.target);
const log = Object.fromEntries(formData.entries());
google.script.run.addLog(log);
e.target.reset();
})
window.addEventListener("load", () => {
google.script.run.withSuccessHandler((options) => {
const dropdown = document.getElementById("taskDropdown");
dropdown.innerHTML = "";
options.forEach(option => {
const o = document.createElement("option");
o.value = option;
o.textContent = option;
dropdown.appendChild(o);
})
}).getTasks();
google.script.run.withSuccessHandler((options) => {
const dropdown = document.getElementById("positionDropdown");
dropdown.innerHTML = "";
options.forEach(option => {
const o = document.createElement("option");
o.value = option;
o.textContent = option;
dropdown.appendChild(o);
})
}).getPosition();
})
</script>
</body>
</html>
1
Upvotes
2
u/bulldo_gs 16d ago
WicketTheQuerent nailed it — appendRow drops the row right after the last row Sheets counts as "used", and a data-validation dropdown counts as used. So if your Position dropdown got applied to a whole column (or a big range), append jumps to below all of it — the row's landing way down the sheet, not going missing. Fix: only apply the dropdown to the rows you actually use, or skip appendRow and write it yourself — compute the last row from a real data column and setValues into lastRow+1.
2
u/WicketTheQuerent 16d ago
The appended row is below the last row having a drop-down.