Configure the Google Apps Script to receive and store messages from your website.
3.1 Open Apps Script Editor
- In your Google Sheet, click Extensions → Apps Script
- A new tab will open with the Apps Script editor
- Delete any existing code in the editor
3.2 Paste the Script
Copy and paste this entire code into the Apps Script editor. It includes both doPost (receives new messages from your site) and doGet (lets your dashboard read messages back) - both are required:
// ============================================
// GOOGLE APPS SCRIPT - READ MESSAGES
// ============================================
// Add this function to your existing Apps Script
// This allows the dashboard to read messages
function doGet(e) {
try {
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
// If no data, return empty array
if (lastRow <= 1) {
return ContentService
.createTextOutput(JSON.stringify({ messages: [] }))
.setMimeType(ContentService.MimeType.JSON);
}
// Get all data (skip header row)
const range = sheet.getRange(2, 1, lastRow - 1, 3);
const values = range.getValues();
// Transform to JSON
const messages = values.map(row => ({
timestamp: row[0] ? new Date(row[0]).toISOString() : new Date().toISOString(),
message: row[1] || "",
sessionId: row[2] || "unknown"
}));
return ContentService
.createTextOutput(JSON.stringify({ messages: messages }))
.setMimeType(ContentService.MimeType.JSON);
} catch (error) {
Logger.log("Error in doGet: " + error.toString());
return ContentService
.createTextOutput(JSON.stringify({
messages: [],
error: error.toString()
}))
.setMimeType(ContentService.MimeType.JSON);
}
}
// Keep your existing doPost function below
function doPost(e) {
try {
const data = JSON.parse(e.postData.contents);
const sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getLastRow() === 0) {
sheet.appendRow(["Timestamp", "Message", "Session ID"]);
const headerRange = sheet.getRange(1, 1, 1, 3);
headerRange.setFontWeight("bold");
headerRange.setBackground("#f0ede5");
headerRange.setHorizontalAlignment("center");
sheet.setColumnWidth(1, 180);
sheet.setColumnWidth(2, 400);
sheet.setColumnWidth(3, 200);
}
const row = [
data.timestamp || new Date().toISOString(),
data.message || "",
data.sessionId || "N/A"
];
sheet.appendRow(row);
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow, 2).setWrap(true);
if (lastRow % 2 === 0) {
sheet.getRange(lastRow, 1, 1, 3).setBackground("#faf8f3");
}
return ContentService
.createTextOutput(JSON.stringify({ ok: true }))
.setMimeType(ContentService.MimeType.JSON);
} catch (error) {
Logger.log("Error in doPost: " + error.toString());
return ContentService
.createTextOutput(JSON.stringify({
ok: false,
error: error.toString()
}))
.setMimeType(ContentService.MimeType.JSON);
}
}
⚠️ Don't skip doGet: without it, your dashboard will fail to load any messages, even though sending messages will still work fine.
3.3 Deploy as Web App
- Click the 💾 Save icon (or Ctrl+S / Cmd+S)
- Click Deploy → New deployment
- Click the gear icon ⚙️ next to "Select type"
- Choose Web app
- Configure settings:
- Description: "Urochithi message receiver" (or anything)
- Execute as: Select "Me"
- Who has access: Select "Anyone"
- Click Deploy
- You may need to authorize the script - click Authorize access
- Choose your Google account and click Allow
⚠️ CRITICAL: Copy the Web app URL that appears after deployment.
It looks like: https://script.google.com/macros/s/ABC.../exec
You'll need this URL in Step 6!