LILO
Code.gs
/**
* @OnlyCurrentDoc
*/
// --- Configuration ---
var SHEET_NAME = "LILO"; // Target sheet tab name
var HEADERS = ["Date", "Logout", "Login", "Logout Reason", "Remarks", "Duration"]; // Header row
// Timezone for formatting dates/times in the sheet. Adjust if needed.
// Uses the sheet's setting if available, otherwise defaults to Eastern Time.
var TIMEZONE = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone() || "America/New_York";
// --- End Configuration ---
// Function that runs when the web app URL is accessed
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Index')
.setTitle('LILO Logger')
.addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
// Function to get the dropdown options from your screenshot
function getDropdownOptions() {
return [
"Bio Break", "BREAK 1", "BREAK 2", "BREAK 3", "Coaching/RME listening",
"CORP EVENT", "LUNCH", "Other Not Plotted (Specify in REMARKS)", "Other Plotted",
"PGMT", "PREP", "SYSTEM/Tech-related incident",
"SYSTEM/Tech-related incident (Not plotted)", "TEAM MEETING"
];
}
// Function to get the target sheet, creating it and the header if necessary
function getSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet(); // Gets the sheet this script is bound to
let sheet = ss.getSheetByName(SHEET_NAME);
if (!sheet) {
sheet = ss.insertSheet(SHEET_NAME);
Logger.log("Created sheet: " + SHEET_NAME);
sheet.appendRow(HEADERS);
sheet.setFrozenRows(1);
// Optional: Adjust column widths if needed
// sheet.setColumnWidth(1, 100); // Example: Set Date column width
Logger.log("Added header row to sheet: " + SHEET_NAME);
} else if (sheet.getLastRow() === 0) {
// If sheet exists but is empty, add header
sheet.appendRow(HEADERS);
sheet.setFrozenRows(1);
Logger.log("Sheet existed but was empty. Added header row.");
}
// Optional: Add more robust check to ensure headers match if sheet already has data
return sheet;
}
// Function to log data received from the HTML form
function logData(formData) {
try {
var sheet = getSheet(); // Get the target sheet in the active spreadsheet
// Get data from the form object
var dateStr = formData.logDate;
var logoutTimeStr = formData.logoutTime;
var loginTimeStr = formData.loginTime;
var reason = formData.logoutReason;
var remarks = formData.remarks;
// --- Data Validation ---
if (!logoutTimeStr || !loginTimeStr || !dateStr || !reason) {
throw new Error("Date, Logout Time, Login Time, and Reason are required.");
}
// --- Duration Calculation ---
var dateBase = new Date(dateStr);
// Use UTC calculations for duration math to avoid DST/timezone shifts affecting the difference
var logoutDateTimeUTC = new Date(Date.UTC(dateBase.getFullYear(), dateBase.getMonth(), dateBase.getDate(), parseInt(logoutTimeStr.split(':')[0]), parseInt(logoutTimeStr.split(':')[1])));
var loginDateTimeUTC = new Date(Date.UTC(dateBase.getFullYear(), dateBase.getMonth(), dateBase.getDate(), parseInt(loginTimeStr.split(':')[0]), parseInt(loginTimeStr.split(':')[1])));
// Handle crossing midnight
if (loginDateTimeUTC.getTime() <= logoutDateTimeUTC.getTime()) {
loginDateTimeUTC.setUTCDate(loginDateTimeUTC.getUTCDate() + 1); // Add 24 hours in UTC
}
var durationMillis = loginDateTimeUTC.getTime() - logoutDateTimeUTC.getTime();
var totalMinutes = Math.round(durationMillis / (1000 * 60));
var hours = Math.floor(totalMinutes / 60);
var minutes = totalMinutes % 60;
// Format duration as text HH:MM - Sheets will interpret this correctly as duration
var durationFormatted = Utilities.formatString('%02d:%02d', hours, minutes);
// --- Formatting for Sheet ---
// Create Date objects based on local time strings for formatting display values
var displayLogoutDateTime = new Date(dateStr + ' ' + logoutTimeStr);
var displayLoginDateTime = new Date(dateStr + ' ' + loginTimeStr);
// Adjust day for display if login time crossed midnight, relative to the logout date
if (loginDateTimeUTC.getUTCDate() !== logoutDateTimeUTC.getUTCDate()) {
displayLoginDateTime.setDate(displayLoginDateTime.getDate() + 1);
}
// Format using the determined TIMEZONE
var formattedDate = Utilities.formatDate(new Date(dateStr), TIMEZONE, 'MM/dd/yyyy');
var formattedLogoutTime = Utilities.formatDate(displayLogoutDateTime, TIMEZONE, 'hh:mm a');
var formattedLoginTime = Utilities.formatDate(displayLoginDateTime, TIMEZONE, 'hh:mm a');
// Append the data row
sheet.appendRow([
formattedDate,
formattedLogoutTime,
formattedLoginTime,
reason,
remarks || "", // Ensure remarks is not null/undefined
durationFormatted // Append calculated duration string
]);
return { status: "success", message: "Data logged successfully!" };
} catch (e) {
Logger.log("Error in logData: " + e.toString() + "\nStack: " + e.stack);
return { status: "error", message: "Error: " + e.message };
}
}
=====================================================
.html
=====================================================
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<style>
body { font-family: sans-serif; padding: 15px; max-width: 500px; margin: 10px auto;} /* Center content */
h3 { text-align: center; color: #444; }
label { display: block; margin-top: 12px; margin-bottom: 3px; font-weight: bold; color: #555;}
input[type="date"], input[type="time"], select, textarea {
width: 100%; /* Full width */
padding: 10px;
margin-top: 2px;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box; /* Include padding in width */
font-size: 1rem;
}
textarea { resize: vertical; min-height: 80px; }
button {
background-color: #007BFF; /* Blue */
color: white;
padding: 12px 20px;
border: none;
border-radius: 4px;
cursor: pointer;
margin-top: 20px;
width: 100%; /* Full width */
font-size: 1.1rem;
}
button:hover { background-color: #0056b3; }
#status { margin-top: 15px; font-weight: bold; text-align: center; padding: 10px; border-radius: 4px;}
.success { color: #155724; background-color: #d4edda; border: 1px solid #c3e6cb;}
.error { color: #721c24; background-color: #f8d7da; border: 1px solid #f5c6cb;}
/* Simple form layout */
form div { margin-bottom: 10px; }
</style>
</head>
<body>
<h3>LILO Logger</h3>
<form id="liloForm">
<div>
<label for="logDate">Date:</label>
<input type="date" id="logDate" name="logDate" required>
</div>
<div>
<label for="logoutTime">Logout Time:</label>
<input type="time" id="logoutTime" name="logoutTime" required>
</div>
<div>
<label for="loginTime">Login Time:</label>
<input type="time" id="loginTime" name="loginTime" required>
</div>
<div>
<label for="logoutReason">Logout Reason:</label>
<select id="logoutReason" name="logoutReason" required>
<option value="" disabled selected>Loading reasons...</option>
</select>
</div>
<div>
<label for="remarks">Remarks (if applicable):</label>
<textarea id="remarks" name="remarks"></textarea>
</div>
<button type="submit" id="submitButton">Log Entry</button>
</form>
<div id="status"></div>
<script>
// Set default date to today
document.getElementById('logDate').valueAsDate = new Date();
// Function to populate the dropdown menu
function populateDropdown(options) {
var select = document.getElementById('logoutReason');
select.innerHTML = '<option value="" disabled selected>-- Select Reason --</option>';
options.forEach(function(option) {
var opt = document.createElement('option');
opt.value = option;
opt.innerHTML = option;
select.appendChild(opt);
});
}
// Function to handle form submission
function handleFormSubmit(event) {
event.preventDefault();
var statusDiv = document.getElementById('status');
var submitButton = document.getElementById('submitButton');
statusDiv.textContent = "Logging...";
statusDiv.className = "";
submitButton.disabled = true; // Disable button
// Pass the form object directly to the server-side function
google.script.run
.withSuccessHandler(handleSubmitSuccess)
.withFailureHandler(handleSubmitFailure)
.logData(this);
}
// Function on successful submission
function handleSubmitSuccess(response) {
var statusDiv = document.getElementById('status');
var submitButton = document.getElementById('submitButton');
submitButton.disabled = false; // Re-enable button
if (response && response.status === "success") {
statusDiv.textContent = response.message || 'Log successful!';
statusDiv.className = "success";
document.getElementById('liloForm').reset(); // Clear form
document.getElementById('logDate').valueAsDate = new Date(); // Reset date
// Clear status message after 5 seconds
setTimeout(function(){ statusDiv.textContent = ''; statusDiv.className = ''; }, 5000);
} else {
// Handle cases where server function returns success but indicates an error logically
handleSubmitFailure(response || { message: "Unknown success response format."});
}
}
// Function on failed submission
function handleSubmitFailure(error) {
var statusDiv = document.getElementById('status');
var submitButton = document.getElementById('submitButton');
submitButton.disabled = false; // Re-enable button
statusDiv.textContent = 'Error: ' + (error && error.message ? error.message : 'Script execution failed.');
statusDiv.className = "error";
console.error("Form Submission Error: ", error);
}
// Add form submission listener
document.getElementById('liloForm').addEventListener('submit', handleFormSubmit);
// Fetch dropdown options when the page loads
google.script.run.withSuccessHandler(populateDropdown).getDropdownOptions();
</script>
</body>
</html>
=====================================================
Call Details Capturing
Code.gs
/**
* @OnlyCurrentDoc
*/
const SHEET_NAME = "Call Logs";
const HEADER_ROW = ["Date", "Account ID", "Contact/Sage ID", "Call Details", "Timestamp"];
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Index')
.setTitle('Call Log & Search')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.DEFAULT);
}
function getSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(SHEET_NAME);
if (!sheet) {
sheet = ss.insertSheet(SHEET_NAME);
if (sheet.getLastRow() === 0) {
sheet.appendRow(HEADER_ROW);
sheet.setFrozenRows(1);
sheet.setColumnWidths(1, HEADER_ROW.length, 150);
sheet.setColumnWidth(4, 300);
}
}
if (sheet.getLastRow() === 0 || sheet.getRange(1, 1, 1, HEADER_ROW.length).getValues()[0].join("") === "") {
sheet.getRange(1, 1, 1, HEADER_ROW.length).setValues([HEADER_ROW]);
if(sheet.getFrozenRows() < 1) sheet.setFrozenRows(1);
}
return sheet;
}
function submitData(formData) {
try {
if (!formData.logDate || !formData.accountId || !formData.callDetails) {
return { status: "error", message: "Date, Account ID, and Call Details are required." };
}
if (!/^\d+$/.test(formData.accountId)) {
return { status: "error", message: "Account ID must contain only numbers." };
}
const sheet = getSheet();
const timestamp = new Date();
// Store date as YYYY-MM-DD string
const logDateString = formData.logDate;
sheet.appendRow([
logDateString, // Store as String
formData.accountId.trim(),
formData.contactSageId.trim(),
formData.callDetails.trim(),
timestamp // Timestamp remains a Date object
]);
const successReturn = { status: "success", message: "Data submitted successfully!" };
Logger.log("Returning from submitData: " + JSON.stringify(successReturn));
return successReturn;
} catch (error) {
Logger.log("Error caught in submitData: " + error + "\nStack:\n" + error.stack);
const errorReturn = { status: "error", message: "An error occurred: " + error.message };
Logger.log("Returning from submitData (catch block): " + JSON.stringify(errorReturn));
return errorReturn;
}
}
// --- UPDATED searchData (Handles Date object or String from Sheet) ---
function searchData(searchCriteria) {
let returnValue;
let headers = HEADER_ROW;
let sheet;
try {
sheet = getSheet();
try {
headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
} catch(e) {
Logger.log("Warning: Could not get headers from sheet, using default. Error: " + e);
headers = [...HEADER_ROW];
}
const data = sheet.getLastRow() > 1 ? sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues() : [];
if (data.length === 0) {
returnValue = { status: "success", message: "No data found in the sheet to search.", results: [headers] };
Logger.log("Returning from searchData (no data): " + JSON.stringify(returnValue));
return returnValue;
}
// Get search date string directly from input (YYYY-MM-DD)
const searchDateStr = searchCriteria.searchDate ? String(searchCriteria.searchDate).trim() : "";
const searchAccountIdStr = searchCriteria.searchAccountId ? String(searchCriteria.searchAccountId).trim() : "";
const searchContactSageIdStr = searchCriteria.searchContactSageId ? String(searchCriteria.searchContactSageId).trim().toLowerCase() : "";
const searchKeyword = searchCriteria.searchKeyword ? String(searchCriteria.searchKeyword).trim() : "";
if (!searchDateStr && !searchAccountIdStr && !searchContactSageIdStr && !searchKeyword) {
returnValue = { status: "error", message: "Please enter at least one search criterion.", results: [headers] };
Logger.log("Returning from searchData (no criteria): " + JSON.stringify(returnValue));
return returnValue;
}
const keywordRegex = searchKeyword ? new RegExp("\\b" + escapeRegExp(searchKeyword) + "\\b", "i") : null;
// --- Filter Data (Handle Date Object OR String from Sheet) ---
const results = data.filter(row => {
let match = true;
if (row.length < headers.length) {
Logger.log("Warning: Skipping filter for row with fewer columns than headers: " + JSON.stringify(row));
return false;
}
// --- NEW: Handle Date comparison robustly ---
let rowDateFormattedStr = ""; // YYYY-MM-DD format from sheet data
const sheetCellValue = row[0]; // Value from Date column
if (sheetCellValue instanceof Date) {
// If it's a Date object, format it to YYYY-MM-DD
try {
const year = sheetCellValue.getFullYear();
const month = String(sheetCellValue.getMonth() + 1).padStart(2, '0'); // +1 because months are 0-indexed
const day = String(sheetCellValue.getDate()).padStart(2, '0');
rowDateFormattedStr = `${year}-${month}-${day}`;
} catch(e) {
Logger.log("Error formatting date object from sheet: " + e + " | Value: " + sheetCellValue);
rowDateFormattedStr = String(sheetCellValue); // Fallback to basic string conversion
}
} else {
// Otherwise, assume it's already a string (or number) and use it directly
rowDateFormattedStr = String(sheetCellValue || "").trim();
}
// --- END NEW Date handling ---
const rowAccountId = String(row[1] || "").trim();
const rowContactSageId = String(row[2] || "").trim().toLowerCase();
const rowCallDetails = String(row[3] || "").trim();
// Compare the consistently formatted sheet date string with the search date string
if (searchDateStr && rowDateFormattedStr !== searchDateStr) match = false;
// Rest of the conditions
if (match && searchAccountIdStr && rowAccountId !== searchAccountIdStr) match = false;
if (match && searchContactSageIdStr && !rowContactSageId.includes(searchContactSageIdStr)) match = false;
if (match && keywordRegex && !keywordRegex.test(rowCallDetails)) match = false;
return match;
});
// --- Pre-process results for return (Convert only Timestamp to String) ---
const processedResults = [headers];
const timestampIndex = headers.indexOf("Timestamp");
results.forEach(row => {
if (row.length < headers.length) {
Logger.log("Warning: Skipping processing for row with fewer columns than headers: " + JSON.stringify(row));
return;
}
const newRow = [...row];
// Date column (row[0]) should now be handled correctly as string by the filtering logic,
// but ensure it's a string before sending back just in case.
newRow[0] = String(newRow[0] || "");
if (timestampIndex !== -1 && newRow[timestampIndex] instanceof Date) {
try { newRow[timestampIndex] = newRow[timestampIndex].toLocaleString(); }
catch (e) {
Logger.log("Warning: Could not format timestamp in row: " + JSON.stringify(row) + " Error: " + e);
newRow[timestampIndex] = String(newRow[timestampIndex]);
}
} else if (timestampIndex !== -1) {
newRow[timestampIndex] = String(newRow[timestampIndex] || "");
}
for(let i=0; i < newRow.length; i++){
if (i === 0 || i === timestampIndex) continue; // Already handled/ensured string
if (typeof newRow[i] === 'object' && newRow[i] !== null) {
Logger.log(`Warning: Converting complex object/Date in cell [${processedResults.length}, ${i}] to string.`);
try { newRow[i] = newRow[i].toLocaleString ? newRow[i].toLocaleString() : JSON.stringify(newRow[i]); }
catch (e) { newRow[i] = JSON.stringify(newRow[i]); }
} else if (newRow[i] === null || typeof newRow[i] === 'undefined') { newRow[i] = ""; }
else { newRow[i] = String(newRow[i]); }
}
processedResults.push(newRow);
});
returnValue = { status: "success", message: `Found ${results.length} matching records.`, results: processedResults };
// Updated log message slightly for clarity
Logger.log("Returning from searchData (success/filtered/processed/date-handled): " + JSON.stringify(returnValue));
return returnValue;
} catch (error) {
Logger.log("Error caught in searchData: " + error + "\nStack:\n" + error.stack);
let headersOnError = [...HEADER_ROW];
try {
if (sheet) { headersOnError = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; }
} catch(e) { Logger.log("Could not retrieve headers within catch block."); }
returnValue = { status: "error", message: "Search failed: " + error.message, results: [headersOnError] };
Logger.log("Returning from searchData (catch block): " + JSON.stringify(returnValue));
return returnValue;
}
}
function escapeRegExp(string) {
return string.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
}
==========================================================
.html
==========================================================
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body { font-family: sans-serif; margin: 20px; background-color: #f8f9fa; }
h1, h2 { color: #444; border-bottom: 1px solid #ccc; padding-bottom: 5px;}
label { display: block; margin-top: 10px; font-weight: bold; color: #555;}
input[type="text"], input[type="number"], input[type="date"], textarea {
width: 95%; /* Responsive width */
max-width: 400px; /* Max width */
padding: 8px;
margin-top: 5px;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box; /* Include padding in width */
}
textarea { height: 80px; resize: vertical; }
button {
padding: 10px 15px;
margin-top: 15px;
background-color: #4CAF50; /* Green */
color: white;
border: none;
border-radius: 4px;
cursor: pointer;
font-size: 1em;
transition: background-color 0.2s;
}
button:hover { background-color: #45a049; }
button:disabled { background-color: #cccccc; cursor: not-allowed; }
#statusMessage, #searchStatus { margin-top: 15px; padding: 10px; border-radius: 4px; min-height: 1.2em; /* Prevent layout shift */ }
.success { background-color: #d4edda; color: #155724; border: 1px solid #c3e6cb; }
.error { background-color: #f8d7da; color: #721c24; border: 1px solid #f5c6cb; }
#searchResults { margin-top: 20px; }
table { border-collapse: collapse; width: 100%; margin-top: 10px; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; word-wrap: break-word; /* Wrap long text */}
th { background-color: #e9ecef; color: #333; }
tr:nth-child(even) { background-color: #f2f2f2; }
.form-section {
background-color: #ffffff;
padding: 20px;
border-radius: 8px;
box-shadow: 0 2px 4px rgba(0,0,0,0.1);
margin-bottom: 30px;
}
.button-group button { margin-right: 10px; }
</style>
</head>
<body>
<h1>Call Log Entry</h1>
<div class="form-section">
<form id="callLogForm">
<label for="logDate">Date:</label>
<input type="date" id="logDate" name="logDate" required>
<label for="accountId">Account ID (Numbers only):</label>
<input type="text" id="accountId" name="accountId" pattern="\d+" title="Please enter numbers only." required>
<label for="contactSageId">Contact/Sage ID (Alphanumeric):</label>
<input type="text" id="contactSageId" name="contactSageId">
<label for="callDetails">Call Details:</label>
<textarea id="callDetails" name="callDetails" required></textarea>
<div class="button-group">
<button type="button" id="submitButton" onclick="submitForm()">Submit Call Log</button>
</div>
<div id="statusMessage"></div>
</form>
</div>
<h2>Search Call Logs</h2>
<div class="form-section">
<form id="searchForm">
<label for="searchDate">Date:</label>
<input type="date" id="searchDate" name="searchDate">
<label for="searchAccountId">Account ID:</label>
<input type="text" id="searchAccountId" name="searchAccountId">
<label for="searchContactSageId">Contact/Sage ID:</label>
<input type="text" id="searchContactSageId" name="searchContactSageId">
<label for="searchKeyword">Call Details Keyword (Whole Word):</label>
<input type="text" id="searchKeyword" name="searchKeyword">
<div class="button-group">
<button type="button" id="searchButton" onclick="searchLogs()">Search</button>
<button type="button" onclick="clearSearch()" style="background-color: #6c757d;">Clear</button>
</div>
<div id="searchStatus"></div>
</form>
<div id="searchResults">
</div>
</div>
<script>
// Set default date to today on load (Keep this functionality)
document.addEventListener('DOMContentLoaded', (event) => {
setToday();
});
// Keep setToday function to initialize date field, even without the button
function setToday() {
const today = new Date();
const year = today.getFullYear();
const month = String(today.getMonth() + 1).padStart(2, '0');
const day = String(today.getDate()).padStart(2, '0');
document.getElementById('logDate').value = `${year}-${month}-${day}`;
}
function submitForm() {
const form = document.getElementById('callLogForm');
const submitButton = document.getElementById('submitButton');
const statusDiv = document.getElementById('statusMessage');
const accountIdInput = document.getElementById('accountId');
if (!/^\d+$/.test(accountIdInput.value)) {
setStatus(statusDiv, 'error', 'Account ID must contain only numbers.');
accountIdInput.focus(); return;
}
if (!form.checkValidity()) {
setStatus(statusDiv, 'error', 'Please fill out all required fields correctly.');
form.reportValidity(); return;
}
const formData = {
logDate: document.getElementById('logDate').value,
accountId: document.getElementById('accountId').value,
contactSageId: document.getElementById('contactSageId').value,
callDetails: document.getElementById('callDetails').value
};
submitButton.disabled = true; submitButton.textContent = 'Submitting...';
setStatus(statusDiv, '', '');
google.script.run.withSuccessHandler(onSubmitSuccess).withFailureHandler(onFailure).submitData(formData);
}
function onSubmitSuccess(response) {
const submitButton = document.getElementById('submitButton');
const statusDiv = document.getElementById('statusMessage');
if (!response) {
console.error("onSubmitSuccess received null/undefined response");
setStatus(statusDiv, 'error', 'Invalid response received after submit.');
} else {
setStatus(statusDiv, response.status, response.message);
if (response.status === 'success') {
document.getElementById('callLogForm').reset(); setToday(); // Reset date to today after submit
}
}
submitButton.disabled = false; submitButton.textContent = 'Submit Call Log';
}
function searchLogs() {
const searchButton = document.getElementById('searchButton');
const statusDiv = document.getElementById('searchStatus');
const resultsDiv = document.getElementById('searchResults');
const searchCriteria = {
searchDate: document.getElementById('searchDate').value,
searchAccountId: document.getElementById('searchAccountId').value,
searchContactSageId: document.getElementById('searchContactSageId').value,
searchKeyword: document.getElementById('searchKeyword').value
};
if (!searchCriteria.searchDate && !searchCriteria.searchAccountId && !searchCriteria.searchContactSageId && !searchCriteria.searchKeyword) {
setStatus(statusDiv, 'error', 'Please enter at least one search criterion.');
resultsDiv.innerHTML = ''; return;
}
searchButton.disabled = true; searchButton.textContent = 'Searching...';
setStatus(statusDiv, '', ''); resultsDiv.innerHTML = '';
google.script.run.withSuccessHandler(onSearchSuccess).withFailureHandler(onFailure).searchData(searchCriteria);
}
function onSearchSuccess(response) {
console.log("Raw response received in onSearchSuccess:", response);
const searchButton = document.getElementById('searchButton');
const statusDiv = document.getElementById('searchStatus');
const resultsDiv = document.getElementById('searchResults');
if (!response) {
console.error("onSearchSuccess received a null or undefined response.");
setStatus(statusDiv, 'error', 'Received an invalid response from the server. Please check Apps Script logs.');
resultsDiv.innerHTML = ''; searchButton.disabled = false; searchButton.textContent = 'Search'; return;
}
setStatus(statusDiv, response.status, response.message);
if (response.status === 'success' && response.results && response.results.length > 1) {
displayResults(response.results);
} else {
if (response.status === 'success' && response.message){
resultsDiv.innerHTML = `<p>${response.message}</p>`;
} else if (response.status !== 'success' && response.message){
resultsDiv.innerHTML = `<p>Error: ${response.message}</p>`;
}
else { resultsDiv.innerHTML = '<p>No matching records found.</p>'; }
}
searchButton.disabled = false; searchButton.textContent = 'Search';
}
function displayResults(data) {
const resultsDiv = document.getElementById('searchResults');
resultsDiv.innerHTML = '';
if (!data || data.length <= 1) {
resultsDiv.innerHTML = '<p>No matching records found.</p>'; return;
}
const table = document.createElement('table');
const thead = table.createTHead();
const tbody = table.createTBody();
const headerRow = thead.insertRow();
data[0].forEach(headerText => {
const th = document.createElement('th');
th.textContent = headerText; headerRow.appendChild(th);
});
for (let i = 1; i < data.length; i++) {
const dataRow = tbody.insertRow();
data[i].forEach((cellData, index) => {
const cell = dataRow.insertCell();
cell.textContent = cellData !== null && cellData !== undefined ? cellData : "";
});
}
resultsDiv.appendChild(table);
}
// clearSearch function remains the same - clears form, results, status
function clearSearch() {
document.getElementById('searchForm').reset();
document.getElementById('searchResults').innerHTML = '';
document.getElementById('searchStatus').innerHTML = '';
document.getElementById('searchStatus').className = '';
}
function onFailure(error) {
console.error('Client-side script execution failed: ', error);
const statusDiv = document.getElementById('statusMessage');
const searchStatusDiv = document.getElementById('searchStatus');
const msg = 'Client-side error occurred: ' + (error.message || error) + ' (Check browser console F12 for details)';
if (document.getElementById('searchButton').disabled) { setStatus(searchStatusDiv, 'error', msg); }
else if (document.getElementById('submitButton').disabled) { setStatus(statusDiv, 'error', msg); }
else { setStatus(searchStatusDiv, 'error', msg); }
document.getElementById('submitButton').disabled = false; document.getElementById('submitButton').textContent = 'Submit Call Log';
document.getElementById('searchButton').disabled = false; document.getElementById('searchButton').textContent = 'Search';
}
function setStatus(element, type, message) {
element.className = '';
element.textContent = message || '';
if (type === 'success') element.classList.add('success');
else if (type === 'error') element.classList.add('error');
}
</script>
</body>
</html>
==========================================================
NPS Goal Calculator
Code.gs
====================================================
/**
* Serves the HTML for the web app.
* This function runs when the web app URL is accessed.
*
* @param {Object} e The event parameter for a web app doGet request.
* @return {HtmlOutput} The HTML output for the web application.
*/
function doGet(e) {
// Log access for debugging (optional)
console.log("Web app accessed. Parameters: " + JSON.stringify(e.parameter));
// Serve the main UI file
return HtmlService.createHtmlOutputFromFile('CalculatorUI')
.setTitle('NPS Goal Calculator Web App') // Sets browser tab title
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.DEFAULT); // Standard security setting
}
/**
* Calculates the Net Promoter Score.
* (Identical calculation function)
*
* @param {number} promoters The number of promoters.
* @param {number} detractors The number of detractors.
* @param {number} passives The number of passives (neutrals).
* @return {number|string} The calculated NPS or an error message.
*/
function calculateCurrentNPS(promoters, detractors, passives) {
promoters = Number(promoters) || 0;
detractors = Number(detractors) || 0;
passives = Number(passives) || 0;
const total = promoters + detractors + passives;
if (total === 0) {
return 'N/A (No responses)';
}
const nps = ((promoters - detractors) / total) * 100;
console.log(`Calculated NPS: ${nps.toFixed(2)} for P:${promoters}, D:${detractors}, N:${passives}`); // Log calculation
return nps.toFixed(2);
}
/**
* Calculates the additional promoters needed to reach a target NPS.
* (Identical calculation function)
*
* @param {number} currentPromoters Current number of promoters.
* @param {number} currentDetractors Current number of detractors.
* @param {number} currentPassives Current number of passives.
* @param {number} targetNPS The desired NPS goal.
* @return {number|string} The number of additional promoters needed or a message.
*/
function calculateNeededPromoters(currentPromoters, currentDetractors, currentPassives, targetNPS) {
currentPromoters = Number(currentPromoters) || 0;
currentDetractors = Number(currentDetractors) || 0;
currentPassives = Number(currentPassives) || 0;
targetNPS = Number(targetNPS);
console.log(`Calculating needed for P:${currentPromoters}, D:${currentDetractors}, N:${currentPassives}, Goal:${targetNPS}`); // Log input
if (isNaN(targetNPS) || targetNPS < -100 || targetNPS > 100) {
console.error("Invalid Target NPS:", targetNPS);
return "Invalid Target NPS (must be -100 to 100)";
}
const currentNPSResult = calculateCurrentNPS(currentPromoters, currentDetractors, currentPassives);
if (currentNPSResult === 'N/A (No responses)') {
if (targetNPS > 0) return 1;
if (targetNPS <= 0) return 0;
return 0;
}
const currentNPSValue = parseFloat(currentNPSResult);
if (!isNaN(currentNPSValue) && currentNPSValue >= targetNPS) {
console.log("Goal already met.");
return 0;
}
if (targetNPS === 100) {
const result = (currentDetractors > 0) ? "Impossible (Goal 100 requires 0 detractors)" : 0;
console.log("Goal is 100. Result:", result);
return result;
}
const currentTotal = currentPromoters + currentDetractors + currentPassives;
const numerator = (targetNPS * currentTotal) + (100 * currentDetractors) - (100 * currentPromoters);
const denominator = 100 - targetNPS;
if (denominator <= 0) {
console.error("Denominator error. Target NPS:", targetNPS);
return "Calculation Error (Target NPS must be < 100)";
}
const neededRaw = numerator / denominator;
const needed = Math.max(0, Math.ceil(neededRaw));
console.log(`Calculation: Numerator=${numerator}, Denominator=${denominator}, NeededRaw=${neededRaw}, Needed=${needed}`); // Log calculation details
return needed;
}
// IMPORTANT: Remove any onOpen() function if it exists, as it's not needed
// for a standalone web app and only applies when the spreadsheet is opened.
=============================================
CalculatorUI.html
==============================================
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/mini.css/3.0.1/mini-default.min.css">
<style>
/* Center content and add padding */
body {
padding: 20px;
max-width: 500px; /* Adjusted max-width slightly */
margin: 20px auto;
font-family: sans-serif; /* Use a common sans-serif font */
}
fieldset {
margin-bottom: 20px; /* Add more space between fieldsets */
border: 1px solid #ccc; /* Add subtle border */
padding: 15px; /* Add padding inside fieldset */
}
legend {
font-size: 1.1em;
font-weight: bold;
margin-bottom: 15px; /* Space below legend */
padding: 0 5px; /* Add slight horizontal padding */
}
/* Style for each row containing a label and input */
.input-row {
margin-bottom: 15px; /* Space below each label/input pair */
}
/* Ensure labels take full width and have space below */
label {
display: block; /* Force label onto its own line */
margin-bottom: 5px; /* Space between label and input */
font-weight: bold;
}
/* Make input fields take full width */
input[type="number"] {
width: 100%;
padding: 8px;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box; /* Include padding/border in width */
}
/* Results area styling */
#results {
margin-top: 25px;
padding: 15px;
border: 1px solid #e0e0e0;
border-radius: 4px;
background-color: #f9f9f9;
}
#results h3 {
margin-top: 0; /* Remove default top margin for heading inside results */
margin-bottom: 15px;
border-bottom: 1px solid #eee; /* Add a separator line */
padding-bottom: 10px;
}
.result-value { font-weight: bold; color: #0288D1; font-size: 1.1em; }
.error { color: #D32F2F; font-weight: bold; margin-top: 10px; }
/* Loader animation */
.loader { display: none; border: 4px solid #f3f3f3; border-top: 4px solid #0288D1; border-radius: 50%; width: 20px; height: 20px; animation: spin 1s linear infinite; vertical-align: middle; }
@keyframes spin { 0% { transform: rotate(0deg); } 100% { transform: rotate(360deg); } }
button .loader { margin-left: 8px;}
button:disabled { cursor: not-allowed; opacity: 0.7; }
/* Primary button style from mini.css (optional, but nice) */
button.primary {
background-color: #0288D1; /* Match result color */
color: white;
}
button.primary:hover {
background-color: #0277BD;
}
</style>
</head>
<body>
<h1>NPS Goal Calculator</h1>
<p>Calculate current NPS and find out how many more promoters you need to reach a target score.</p>
<form id="npsForm" onsubmit="event.preventDefault(); calculate();">
<fieldset>
<legend>Enter Survey Results</legend>
<div class="input-row">
<label for="promoters">Promoters:</label>
<input type="number" id="promoters" min="0" value="1" required>
</div>
<div class="input-row">
<label for="detractors">Detractors:</label>
<input type="number" id="detractors" min="0" value="5" required>
</div>
<div class="input-row">
<label for="passives">Passives (Neutrals):</label>
<input type="number" id="passives" min="0" value="1" required>
</div>
</fieldset>
<fieldset>
<legend>Set Goal</legend>
<div class="input-row">
<label for="goal">NPS Goal (-100 to 100):</label>
<input type="number" id="goal" min="-100" max="100" value="50" required>
</div>
</fieldset>
<button type="submit" id="calcButton" class="primary">
Calculate
<span id="loader" class="loader"></span>
</button>
</form>
<div id="results">
<h3>Results:</h3>
<p>Current NPS: <span id="currentNPS" class="result-value">N/A</span></p>
<p>Additional Promoters Needed for Goal <span id="goalDisplay" class="result-value">50</span>: <span id="neededPromoters" class="result-value">N/A</span></p>
<p id="errorMessage" class="error"></p>
</div>
<script>
// --- JavaScript logic remains the same ---
// (Make sure the JS functions from the previous answer are here)
const form = document.getElementById('npsForm');
const calcButton = document.getElementById('calcButton');
const loader = document.getElementById('loader');
const currentNpsEl = document.getElementById('currentNPS');
const neededPromotersEl = document.getElementById('neededPromoters');
const errorMessageEl = document.getElementById('errorMessage');
const goalDisplayEl = document.getElementById('goalDisplay');
const goalInput = document.getElementById('goal');
function showLoader(show) {
loader.style.display = show ? 'inline-block' : 'none';
calcButton.disabled = show;
}
function clearResults() {
currentNpsEl.textContent = 'N/A';
neededPromotersEl.textContent = 'N/A';
errorMessageEl.textContent = '';
goalDisplayEl.textContent = goalInput.value || 'N/A'; // Update from input field
}
function calculate() {
if (!form.checkValidity()) {
form.reportValidity();
return;
}
clearResults();
showLoader(true);
const promoters = document.getElementById('promoters').value;
const detractors = document.getElementById('detractors').value;
const passives = document.getElementById('passives').value;
const goal = goalInput.value;
goalDisplayEl.textContent = goal || 'N/A';
google.script.run
.withSuccessHandler(function(npsResult) {
currentNpsEl.textContent = npsResult;
if (typeof npsResult === 'string' && (npsResult.startsWith('Error') || npsResult.startsWith('Invalid'))) {
showError({ message: npsResult });
showLoader(false);
return;
}
google.script.run
.withSuccessHandler(function(neededResult) {
neededPromotersEl.textContent = neededResult;
showLoader(false);
})
.withFailureHandler(showError)
.calculateNeededPromoters(promoters, detractors, passives, goal);
})
.withFailureHandler(showError)
.calculateCurrentNPS(promoters, detractors, passives);
}
function showError(error) {
const message = (error && error.message) ? error.message : String(error);
errorMessageEl.textContent = "Error: " + message;
console.error("Server Error:", error);
showLoader(false);
}
goalInput.addEventListener('input', () => {
goalDisplayEl.textContent = goalInput.value || 'N/A';
});
</script>
</body>
</html>
=====================================================
Task Tool/Others
Code.gs
// Global constant for the Sheet Name
const SHEET_NAME = "Links/Details"; // Ensure this matches your sheet name exactly
const TASK_COLUMN = 1; // Column A (Task Name)
const LINK_COLUMN = 2; // Column B (Link)
const DETAILS_COLUMN = 3; // Column C (Detail)
const DETAIL2_COLUMN = 4; // Column D (Detail 2)
const DETAIL3_COLUMN = 5; // Column E (Detail 3)
/**
* Serves the HTML page for the web app.
* This function runs when someone visits the web app URL.
* @param {object} e The event parameter (not used here but required by doGet).
* @return {HtmlOutput} The HTML page to be served.
*/
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Index') // Serves the Index.html file
.setTitle('Task Lookup Tool'); // Sets the browser tab title
}
/**
* Gets the list of tasks from the spreadsheet's task column.
* This function is called by the frontend JavaScript.
* @return {string[]} An array of task names (strings). Returns empty array on error.
*/
function getTasks() {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(SHEET_NAME);
if (!sheet) {
// Log error if sheet is not found and throw an error
Logger.log(`Error: Sheet "${SHEET_NAME}" not found.`);
throw new Error(`Sheet "${SHEET_NAME}" not found.`);
}
// Get data starting from row 2 (assuming row 1 is header) down to the last row with content
// Fetches only the first column (Task names)
const range = sheet.getRange(2, TASK_COLUMN, sheet.getLastRow() - 1, 1);
const values = range.getValues(); // Gets values as a 2D array [[task1], [task2], ...]
// Flatten the 2D array into a 1D array and filter out any empty rows/cells
const tasks = values.map(row => row[0]).filter(task => task && task.toString().trim() !== "");
Logger.log(`Tasks fetched successfully: ${tasks.length} tasks found.`);
return tasks;
} catch (error) {
// Log any errors encountered during the process
Logger.log(`Error in getTasks: ${error.message} \nStack: ${error.stack}`);
return []; // Return empty array to the frontend on error
}
}
/**
* Gets the link and all details (Detail, Detail 2, Detail 3) for a specific task.
* This function is called by the frontend JavaScript when a task is selected.
* @param {string} selectedTask The name of the task selected in the dropdown.
* @return {object|null} An object containing {link: '...', details: '...', details2: '...', details3: '...'}
* or null if task not found or an error occurs.
*/
function getTaskDetails(selectedTask) {
// Return null immediately if no task name is provided
if (!selectedTask) {
Logger.log('getTaskDetails called with no selectedTask.');
return null;
}
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(SHEET_NAME);
if (!sheet) {
// Log error if sheet is not found and throw an error
Logger.log(`Error: Sheet "${SHEET_NAME}" not found.`);
throw new Error(`Sheet "${SHEET_NAME}" not found.`);
}
// Get all data from the sheet to search through
const data = sheet.getDataRange().getValues(); // [[header1, header2,...], [row1col1, row1col2,...], ...]
// Find the row matching the selected task (case-insensitive search)
// Start loop from i = 1 to skip header row (index 0)
for (let i = 1; i < data.length; i++) {
const taskInSheet = data[i][TASK_COLUMN - 1]; // Get task name from current row (adjusting for 0-based index)
// Check if taskInSheet exists and matches selectedTask (case-insensitive)
if (taskInSheet && taskInSheet.toString().trim().toLowerCase() === selectedTask.trim().toLowerCase()) {
// Task found, extract link and details, providing empty string as default if cell is empty
const link = data[i][LINK_COLUMN - 1] || '';
const details = data[i][DETAILS_COLUMN - 1] || '';
const details2 = data[i][DETAIL2_COLUMN - 1] || '';
const details3 = data[i][DETAIL3_COLUMN - 1] || '';
Logger.log(`Details found for "${selectedTask}"`);
// Return the found data as an object
return {
link: link,
details: details,
details2: details2,
details3: details3
};
}
}
// If loop completes without finding the task
Logger.log(`No details found for task: "${selectedTask}"`);
return null; // Task not found
} catch (error) {
// Log any errors encountered during the process
Logger.log(`Error in getTaskDetails for task "${selectedTask}": ${error.message} \nStack: ${error.stack}`);
return null; // Return null to the frontend on error
}
}
====================================================
Index.html
====================================================
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://fonts.googleapis.com/css2?family=Roboto:wght@400;500&display=swap" rel="stylesheet">
<style>
/* --- Base Styles --- */
body {
font-family: 'Roboto', 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
background-color: #f8f9fa; /* Light grey background */
color: #333;
margin: 0;
padding: 0;
font-size: 15px;
line-height: 1.6;
}
/* --- Container --- */
#app-container {
max-width: 700px;
margin: 20px auto;
padding: 25px 30px;
background-color: #ffffff;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
}
/* --- Header --- */
h1 {
color: #0d6efd; /* Bootstrap primary blue */
font-size: 1.8em;
margin-top: 0; /* Remove default margin */
margin-bottom: 25px;
text-align: center;
font-weight: 500;
}
/* --- Form Elements --- */
label {
display: block;
margin-bottom: 8px;
font-weight: 500;
color: #555;
}
select#taskDropdown {
width: 100%;
padding: 10px 12px;
margin-bottom: 25px;
border: 1px solid #ced4da;
border-radius: 5px;
background-color: #fff;
font-size: 1em;
box-sizing: border-box;
}
select#taskDropdown:focus {
border-color: #86b7fe;
outline: 0;
box-shadow: 0 0 0 0.2rem rgba(13, 110, 253, 0.25);
}
/* --- Content Sections --- */
.content-section {
margin-top: 20px;
padding: 15px;
border: 1px solid #e9ecef;
border-radius: 6px;
background-color: #fdfdff;
}
.details-title {
font-weight: 500;
margin-top: 0;
margin-bottom: 8px;
display: block;
color: #0d6efd;
font-size: 1.1em;
}
#linkArea span#linkDisplay {
display: block;
word-wrap: break-word;
margin-bottom: 10px;
color: #495057;
}
/* Style for the content within detail sections */
.content-section div {
font-size: 1em; /* Ensure consistent font size */
color: #343a40;
}
/* --- Button --- */
#linkButton {
display: inline-block;
margin-top: 0;
padding: 8px 18px;
background-color: #198754; /* Bootstrap success green */
color: white;
border: none;
border-radius: 5px;
cursor: pointer;
text-decoration: none;
font-size: 0.95em;
font-weight: 500;
transition: background-color 0.2s ease;
}
#linkButton:hover {
background-color: #157347; /* Darker green on hover */
}
/* --- Messages --- */
#loadingMessage, #errorMessage {
margin-top: 15px;
padding: 10px 15px;
border-radius: 5px;
text-align: center;
font-size: 0.95em;
}
#loadingMessage { color: #555; background-color: #eee; }
#errorMessage { color: #842029; background-color: #f8d7da; border: 1px solid #f5c2c7;}
/* --- Utility --- */
.hidden {
display: none;
}
</style>
</head>
<body>
<div id="app-container">
<h1>Task Lookup Tool</h1>
<label for="taskDropdown">Select a task/guide/tool:</label>
<select id="taskDropdown">
<option value="" disabled selected>Loading tasks...</option>
</select>
<div id="loadingMessage" class="hidden">Loading details... <span style="font-style: italic;">Please wait...</span></div>
<div id="errorMessage" class="hidden"></div>
<div id="linkArea" class="content-section hidden">
<span class="details-title">Link</span>
<span id="linkDisplay"></span>
<a id="linkButton" href="#" target="_blank">Open Link</a>
</div>
<div id="detailsArea" class="content-section hidden">
<span class="details-title">Detail</span>
<div id="detailsDisplay"></div>
</div>
<div id="detailsArea2" class="content-section hidden">
<span class="details-title">Detail 2</span>
<div id="detailsDisplay2"></div>
</div>
<div id="detailsArea3" class="content-section hidden">
<span class="details-title">Detail 3</span>
<div id="detailsDisplay3"></div>
</div>
</div> <script>
// --- Initialization ---
document.addEventListener('DOMContentLoaded', function() {
google.script.run
.withSuccessHandler(populateDropdown)
.withFailureHandler(showError)
.getTasks();
document.getElementById('taskDropdown').addEventListener('change', handleTaskSelection);
});
// --- Functions ---
function escapeHtml(unsafe) {
if (unsafe === null || typeof unsafe === 'undefined') return "";
return unsafe
.toString()
.replace(/&/g, "&")
.replace(/</g, "<")
.replace(/>/g, ">")
.replace(/"/g, """)
.replace(/'/g, "'");
}
function populateDropdown(tasks) {
const dropdown = document.getElementById('taskDropdown');
dropdown.innerHTML = '';
if (!tasks || tasks.length === 0) {
showError('No tasks found or error loading tasks from Sheet.');
const defaultOption = document.createElement('option');
defaultOption.value = ""; defaultOption.textContent = "No tasks available"; defaultOption.disabled = true;
dropdown.appendChild(defaultOption); return;
}
const defaultOption = document.createElement('option');
defaultOption.value = ""; defaultOption.textContent = "-- Select a Task --"; defaultOption.disabled = true; defaultOption.selected = true;
dropdown.appendChild(defaultOption);
tasks.forEach(function(task) {
const option = document.createElement('option');
option.value = task; option.textContent = task;
dropdown.appendChild(option);
});
dropdown.disabled = false;
}
function handleTaskSelection() {
const selectedTask = document.getElementById('taskDropdown').value;
const linkArea = document.getElementById('linkArea');
const detailsArea = document.getElementById('detailsArea');
const detailsArea2 = document.getElementById('detailsArea2');
const detailsArea3 = document.getElementById('detailsArea3');
const loadingMsg = document.getElementById('loadingMessage');
const errorMsg = document.getElementById('errorMessage');
linkArea.classList.add('hidden'); detailsArea.classList.add('hidden');
detailsArea2.classList.add('hidden'); detailsArea3.classList.add('hidden');
errorMsg.classList.add('hidden'); loadingMsg.classList.add('hidden');
document.getElementById('linkDisplay').textContent = '';
document.getElementById('detailsDisplay').innerHTML = '';
document.getElementById('detailsDisplay2').innerHTML = '';
document.getElementById('detailsDisplay3').innerHTML = '';
document.getElementById('linkButton').href = '#';
if (selectedTask) {
loadingMsg.classList.remove('hidden'); errorMsg.classList.add('hidden');
google.script.run .withSuccessHandler(updateDetails) .withFailureHandler(showError) .getTaskDetails(selectedTask);
}
}
function updateDetails(data) {
const linkArea = document.getElementById('linkArea');
const detailsArea = document.getElementById('detailsArea');
const detailsArea2 = document.getElementById('detailsArea2');
const detailsArea3 = document.getElementById('detailsArea3');
const linkDisplay = document.getElementById('linkDisplay');
const detailsDisplay = document.getElementById('detailsDisplay');
const detailsDisplay2 = document.getElementById('detailsDisplay2');
const detailsDisplay3 = document.getElementById('detailsDisplay3');
const linkButton = document.getElementById('linkButton');
const loadingMsg = document.getElementById('loadingMessage');
loadingMsg.classList.add('hidden');
if (data) {
let contentFound = false;
if (data.link) {
linkDisplay.textContent = data.link; linkButton.href = data.link;
linkArea.classList.remove('hidden'); contentFound = true;
} else { linkArea.classList.add('hidden'); }
if (data.details) {
detailsDisplay.innerHTML = escapeHtml(data.details).replace(/\n/g, '<br>');
detailsArea.classList.remove('hidden'); contentFound = true;
} else { detailsArea.classList.add('hidden'); }
if (data.details2) {
detailsDisplay2.innerHTML = escapeHtml(data.details2).replace(/\n/g, '<br>');
detailsArea2.classList.remove('hidden'); contentFound = true;
} else { detailsArea2.classList.add('hidden'); }
if (data.details3) {
detailsDisplay3.innerHTML = escapeHtml(data.details3).replace(/\n/g, '<br>');
detailsArea3.classList.remove('hidden'); contentFound = true;
} else { detailsArea3.classList.add('hidden'); }
if (!contentFound) { showError('No details or link found for the selected task.'); }
} else { showError('Details not found or error fetching data.'); }
}
function showError(error) {
const errorMsg = document.getElementById('errorMessage');
const loadingMsg = document.getElementById('loadingMessage');
loadingMsg.classList.add('hidden');
const message = (typeof error === 'object' && error !== null && error.message) ? error.message : error;
errorMsg.textContent = 'Error: ' + message;
errorMsg.classList.remove('hidden');
console.error('Apps Script Error:', error);
document.getElementById('linkArea').classList.add('hidden');
document.getElementById('detailsArea').classList.add('hidden');
document.getElementById('detailsArea2').classList.add('hidden');
document.getElementById('detailsArea3').classList.add('hidden');
}
</script>
</body>
</html>
