I am currently integrating my values from my google sheet into my google site. I know, this would also be possible by just placing the box of the google sheet into the site but I want to improve the style by using google app script. I got following code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function submitForm() {
const name = document.getElementById('name').value;
const message = document.getElementById('message').value;
google.script.run.withSuccessHandler(function() {
document.getElementById('name').value = '';
document.getElementById('message').value = '';
displayMessages(); // Refresh the message list after submission
}).withFailureHandler(function(error) {
alert("Error submitting message: " + error.message);
}).submitMessage(name, message);
}
function displayMessages() {
google.script.run.withSuccessHandler(renderMessages).getMessages(); // Calling getMessages
}
function renderMessages(data) {
const dataList = document.getElementById('dataList');
dataList.innerHTML = ''; // Clear previous data
// Handle null or unexpected data format
if (!data || !Array.isArray(data) || data.length === 0) {
const listItem = document.createElement('li');
listItem.textContent = "No messages available.";
dataList.appendChild(listItem);
return;
}
// Process each row of data
data.forEach(function(row) {
// Ensure the row is an array and has the expected length
if (Array.isArray(row) && row.length === 3) {
const listItem = document.createElement('li');
// Format the timestamp using toLocaleString
const formattedDate = new Date(row[0]).toLocaleString(); // Format the date
listItem.textContent = `${formattedDate} - ${row[1] || 'Anonymous'}: ${row[2] || 'No message'}`; // Format the message display
dataList.appendChild(listItem);
} else {
console.error("Unexpected row format:", row); // Log unexpected row formats
}
});
}
window.onload = function() {
displayMessages(); // Load messages when the page is opened
};
</script>
</head>
<body>
<h1>Message Board</h1>
<form onsubmit="submitForm(); return false;">
<label for="name">Name:</label>
<input type="text" id="name" required>
<br>
<label for="message">Message:</label>
<textarea id="message" required></textarea>
<br>
<input type="submit" value="Submit">
</form>
<h2>Messages:</h2>
<ul id="dataList"></ul>
</body>
</html>
and following google app script:
function doGet() {
return HtmlService.createHtmlOutputFromFile('index');
}
function submitMessage(name, message) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const timestamp = new Date();
sheet.appendRow([timestamp, name, message]);
}
function getMessages() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
// Log the raw data retrieved from the sheet
Logger.log("Raw data from sheet: " + JSON.stringify(data));
if (data.length < 1) {
// If there's no data (only header), return an empty array
return [];
}
// Remove the header row
data.shift();
// Log the data after removing the header
Logger.log("Data after removing header: " + JSON.stringify(data));
Logger.log(data);
return data;
}
And I think the code.gs works fine as the output corresponds to the values of my google sheet. So my question is, where the error would be regarding the problem, that the site doesnt show the values of the site but the input works absolutely fine.
Kind regards.
I tried to adjust the html several times within the browser console and I found that the error occurs at the forEach as the data is Null but the code.gs returns a list with the correct values.
