I'm hesitant to post this here as it seems like it should be an easy error to solve, but every option that I've tried doesn't result in the error resolving.
I'm working on a application in Google Sheets where people are able to log what training they've completed so they can retrospectively look back at what they've done over a period and find any holes in their knowledge. When a user clicks on the edit button next to an entry, they should be taken to an edit page (which successfully happens) and have all the details loaded against the entry load so they are able to edit them.
The problem I'm having is when an entry is clicked on to edit, the ID number of the entry is pulled through to the edit page of the application, but none of the other information is pulled through and the console gives me the error
Uncaught TypeError: Cannot read properties of null (reading 'trainingTopic')
This seems to be happening in the afterEditViewLoads function in the main.html file.
From my understanding this could be occurring because the script is running before the element in the HTML has loaded fully, but my scripts are located at the bottom of my HTML file, after everything loads (as far as I can see).
The code from my main.html file is as follows:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
<style>
.nav-link {
cursor:pointer;
}
</style>
</head>
<body>
<div class="container">
<ul class="nav nav-tabs">
<li class="nav-item">
<div class="nav-link active" id="homeLink">Home</div>
</li>
<li class="nav-item">
<div class="nav-link" id="searchLink">Search</div>
</li>
<li class="nav-item">
<div class="nav-link" id="addEntryLink">Add Entry</div>
</li>
</ul>
<div id="app"></div>
</div>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/[email protected]/dist/umd/popper.min.js" integrity="sha384-I7E8VVD/ismYTF4hNIPjVp/Zjvgyol6VFvRkX/vR+Vc4jQkC+hVqc2pM8ODewa9r" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js" integrity="sha384-0pUGZvbkm6XF6gxjEnlmuGrJXVbNuzT9qBBavbLwCsOGabYfZo0T0to5eqruptLy" crossorigin="anonymous"></script>
<script>
var data;
function loadView(options) {
var id = typeof options.id === "undefined" ? "app" : options.id;
var cb = typeof options.callback === "undefined" ? function() {} : options.callback;
google.script.run.withSuccessHandler(function(html) {
document.getElementById(id).innerHTML = html;
typeof options.params === "undefined" ? cb() : cb(options.params);
})[options.func]();
}
function setDataForSearch() {
google.script.run.withSuccessHandler(function(dataReturned) {
data = dataReturned.slice();
}).getDataForSearch();
}
function search() {
var searchInput = document.getElementById("searchInput").value.toString().toLowerCase().trim();
var searchWords = searchInput.split(/\s+/);
var searchColumns = [1,2];
var resultsArray = searchInput === "" ? [] : data.filter(function(r) {
return searchWords.every(function(word) {
return searchColumns.some(function(colIndex) {
return r[colIndex].toString().toLowerCase().indexOf(word) !== -1
});
});
});
var searchResultsBox = document.getElementById("searchResults");
var templateBox = document.getElementById("rowTemplate");
var template = templateBox.content;
searchResultsBox.innerHTML = "";
resultsArray.forEach(function(r) {
var tr = template.cloneNode(true);
var entryIDColumn = tr.querySelector(".entryID");
var trainingTopicColumn = tr.querySelector(".trainingTopic");
var trainingProviderColumn = tr.querySelector(".trainingProvider");
var deleteButton = tr.querySelector(".deleteButton");
var editButton = tr.querySelector(".editButton");
entryIDColumn.textContent = r[0];
deleteButton.dataset.entryID = r[0];
editButton.dataset.entryID = r[0];
trainingTopicColumn.textContent = r[1];
trainingProviderColumn.textContent = r[2];
searchResultsBox.appendChild(tr);
});
}
function displayConfirmationDelete(e) {
if(e.target.dataset.buttonState === "delete") {
e.target.previousElementSibling.classList.remove("d-none");
e.target.textContent = "Cancel";
e.target.dataset.buttonState = "cancel";
} else {
e.target.previousElementSibling.classList.add("d-none");
e.target.textContent = "Delete";
e.target.dataset.buttonState = "delete";
}
}
function deleteEntry(e) {
var entryID = e.target.dataset.entryID;
google.script.run.withSuccessHandler(function() {
e.target.closest(".resultBox").remove();
var ids = data.map(function(r) {return r[0].toString().toLowerCase()});
var index = ids.indexOf(entryID.toString().toLowerCase());
data.splice(index,1);
}).deleteById(entryID);
}
function afterEditViewLoads(params) {
console.log("Checking for trainingTopic element...")
var trainingTopicElement = document.getElementById("trainingTopic");
console.log(trainingTopicElement);
document.getElementById("entryID").value = params.entryID;
google.script.run.withSuccessHandler(function(entryInfo) {
document.getElementById("trainingTopic").value = entryInfo.trainingTopic;
document.getElementById("trainingProvider").value = entryInfo.trainingProvider;
document.getElementById("dateUndertaken").value = entryInfo.dateUndertaken;
document.getElementById("trainingType").value = entryInfo.trainingType;
document.getElementById("jobNumber").value = entryInfo.jobNumber;
document.getElementById("trainingPurpose").value = entryInfo.trainingPurpose;
document.getElementById("trainingOutcome").value = entryInfo.trainingOutcome;
}).getEntryById(params.entryID);
}
function loadSearchView(){
loadView({func:"loadSearchView", callback:setDataForSearch});
}
function loadAddEntryView(){
loadView({func:"loadAddEntryView"});
}
function loadEditEntryView(){
loadView({func:"loadEditEntryView"});
}
document.getElementById("searchLink").addEventListener("click",loadSearchView);
document.getElementById("addEntryLink").addEventListener("click",loadAddEntryView);
document.getElementById("homeLink").addEventListener("click",loadEditEntryView);
function inputEventHandler(e) {
if(e.target.matches("#searchInput")) {
search();
}
}
function clickEventHandler(e) {
if(e.target.matches(".deleteButton")) {
deleteEntry(e);
}
if(e.target.matches(".beforeDeleteButton")) {
displayConfirmationDelete(e);
}
if(e.target.matches(".editButton")) {
loadView({func:"loadEditEntryView",callback:afterEditViewLoads,params:{entryID:e.target.dataset.entryID}});
}
}
document.getElementById("app").addEventListener("input",inputEventHandler);
document.getElementById("app").addEventListener("click",clickEventHandler);
</script>
</body>
</html>
The code for the edit.html page where the details should be loaded is as follows:
<h1>Edit Entry</h1>
<div class="editEntryForm">
<div class="mb-3">
<label for="entryID" class="form-label"><strong>Entry ID</strong></label>
<input type="text" class="form-control" id="entryID" readonly>
</div>
<div class="mb-3">
<label for="trainingTopic" class="form-label"><strong><span style="color: red;">* </span>Training Topic</strong></label>
<div id="trainignTopcHelp" class="form-text">This is just a headline, not a full description of the training.</div>
<input type="text" class="form-control" id="trainingTopic" required>
</div>
<div class="mb-3">
<label for="trainingProvider" class="form-label"><strong><span style="color: red;">* </span>Training Provider</strong></label>
<input type="text" class="form-control" id="trainingProvider" required>
</div>
<div class="mb-3">
<label for="dateUndertaken" class="form-label"><strong><span style="color: red;">* </span>Date Undertaken</strong></label>
<input type="date" class="form-control" id="dateUndertaken" required>
</div>
<div class="mb-3">
<label for="trainingType" class="form-label"><strong><span style="color: red;">* </span>Training Type</strong></label>
<select class="form-select" id="trainingType" required>
<option selected></option>
<option value="Product representative">Product representative</option>
<option value="Informal training/staff discussions">Informal training/staff discussions</option>
<option value="Seminar/webinar">Seminar/webinar</option>
<option value="Formal training course">Formal training course</option>
<option value="Technical reading">Technical reading</option>
<option value="ComplyNZ tech meeting">ComplyNZ tech meeting</option>
</select>
</div>
<div class="mb-3">
<label for="jobNumber" class="form-label"><strong><span style="color: red;">* </span>ComplyNZ Job Number</strong></label>
<div id="jobNumberHelp" class="form-text">Was the training related to assessing a specific job or jobs? If so, record the job number(s). If not, enter N/A.</div>
<input type="text" class="form-control" id="jobNumber" required>
</div>
<div class="mb-3">
<label for="trainingPurpose" class="form-label"><strong><span style="color: red;">* </span>Training Purpose</strong></label>
<div id="trainingPurposeHelp" class="form-text">Outline what the purpose of the training was.</div>
<textarea class="form-control" id="trainingPurpose" rows="3" required></textarea>
</div>
<div class="mb-3">
<label for="trainingOutcome" class="form-label"><strong><span style="color: red;">* </span>Training Outcome</strong></label>
<div id="trainingPurposeHelp" class="form-text">In your own words, describe the outcome of the training, and how you will put it into effect.</div>
<textarea class="form-control" rows="3" id="trainingOutcome" required></textarea>
</div>
<button class="btn btn-primary">Save Entry</button>
<button class="btn btn-primary">Cancel Changes</button>
</div>
And finally, the code for my server side functions is as follows:
function getDataForSearch() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Training Log");
return ws.getRange(2,1,ws.getLastRow()-1,3).getValues();
}
function deleteById(id) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Training Log");
const entryIds = ws.getRange(2,1,ws.getLastRow()-1,1).getValues().map(r => r[0].toString().toLowerCase());
const posIndex = entryIds.indexOf(id.toString().toLowerCase());
const rowNumber = posIndex === -1 ? 0 : posIndex + 2;
ws.deleteRow(rowNumber);
}
function getEntryById(id) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Training Log");
const entryIds = ws.getRange(2,1,ws.getLastRow()-1,1).getValues().map(r => r[0].toString().toLowerCase());
const posIndex = entryIds.indexOf(id.toString().toLowerCase());
const rowNumber = posIndex === -1 ? 0 : posIndex + 2;
const entryInfo = ws.getRange(rowNumber,1,1,8).getValues()[0];
return {entryID:entryInfo[0],
trainingTopic:entryInfo[1],
trainingProvider:entryInfo[2],
dateUndertaken:entryInfo[3],
trainingType:entryInfo[4],
jobNumber:entryInfo[5],
trainingPurpose:entryInfo[6],
trainingOutcome:entryInfo[7]
}
}
Apologies for the blocks of code, but I can't seem to work out why I'm getting the error and how to fix it, so any help would be appreciated. I'll include a link to the spreadsheet/code in question in case anyone wants to look at it in situ with the rest of the code.
https://docs.google.com/spreadsheets/d/1bIX0zFoT8LASX4uo4cboQ4ePjEOlKPhCC7wkpJg0Vrc/edit?usp=sharing
Thanks in advance!
getEntryById, the cell values are retrieved by const entryInfo = ws.getRange(rowNumber,1,1,8).getValues()[0];. In the current stage, when the date object is included in the returned values from Google Apps Script to Javascript with google.script.run, {nullis returned. [Ref](https://developers.google.com/apps-script/guides/html/reference/run#myfunction...-any-server-side-function) I guessed that this might be the reason for your current issue ofUncaught TypeError: Cannot read properties of null (reading 'trainingTopic')`.getValues is modified to getDisplayValues, the values are correctly sent to the Javascript side. But, when I saw your HTML, you are using an input tag with type="date", and in your Spreadsheet, the display value of the date is 30/01/2023. In this case, when the values are loaded, no value is shown in the tag. So, in this case, it is required to also convert the date format of the date object.In order to simply remove your current issue of Uncaught TypeError: Cannot read properties of null (reading 'trainingTopic'), when the above points are reflected in your script, how about the following modification?
In this modification, a simple modification is reflected in your function getEntryById as follows.
const entryInfo = ws.getRange(rowNumber,1,1,8).getValues()[0];
const timeZone = ss.getSpreadsheetTimeZone();
const entryInfo = ws.getRange(rowNumber, 1, 1, 8).getValues()[0].map(c => c instanceof Date ? Utilities.formatDate(c, timeZone, "yyyy-MM-dd") : c);
As another modification, I thought that in your situation, TextFinder could be used for searching the inputted ID. When this is reflected in your function getEntryById, it becomes as follows.
function getEntryById(id) {
const keys = ["entryID", "trainingTopic", "trainingProvider", "dateUndertaken", "trainingType", "jobNumber", "trainingPurpose", "trainingOutcome"];
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Training Log");
const find = sheet.getRange("A2:A" + sheet.getLastRow()).createTextFinder(id.toString().toLowerCase()).findNext();
if (find) {
const timeZone = ss.getSpreadsheetTimeZone();
const obj = Object.fromEntries(find.offset(0, 0, 1, sheet.getLastColumn()).getValues()[0].map((c, i) => [keys[i], c instanceof Date ? Utilities.formatDate(c, timeZone, "yyyy-MM-dd") : c]));
return obj;
}
return Array(keys.length).fill("");
}
option tag is put with document.getElementById("trainingType").value = entryInfo.trainingType;, no value is shown. Please be careful about this.