Last active
January 27, 2026 19:02
-
-
Save vhsu/b602c4dcd045cfd808793fb93b9845a3 to your computer and use it in GitHub Desktop.
Get an email notification for each lead form extension conversion in google ads + sheets
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * SCRIPT : GOOGLE ADS LEAD FORM VERS SHEETS + EMAIL | |
| * Auteur : Gemini & [Vincent Hsu/Suisseo] | |
| * Version : 3.5 EN (Final : Dynamic Columns + Matrix Scan + Strict Fingerprint) | |
| * Installation guide: https://www.suisseo.ch/en/blog/get-instant-email-notifications-for-google-ads-lead-forms-store-them-in-google-sheets/ | |
| */ | |
| function doPost(e) { | |
| // --- 1. CONFIGURATION --- | |
| var EMAIL_NOTIFICATION = "[email protected]"; // Email to receive leads; separate multiple with commas | |
| var SHEET_NAME = "LEADS"; | |
| // ------------------------ | |
| // SECURITY CONFIGURATION | |
| // Leave empty ("") to disable security. If defined, Key in Google Ads must be: Form Name|MySecret123 | |
| var EXPECTED_SECRET = ""; | |
| // ------------------------ | |
| // --- 2. LOCK & SYNC (CRITICAL) --- | |
| var lock = LockService.getScriptLock(); | |
| // Wait up to 30 seconds. If busy, this acts as a queue. | |
| if (!lock.tryLock(30000)) { | |
| return ContentService.createTextOutput("Server Busy").setMimeType(ContentService.MimeType.TEXT); | |
| } | |
| try { | |
| // FORCE SYNC: Ensure we see exactly what the previous lead just wrote | |
| SpreadsheetApp.flush(); | |
| if (!e || !e.postData) return ContentService.createTextOutput("No Data"); | |
| var data = JSON.parse(e.postData.contents); | |
| // --- 3. SECURITY & FORM NAME PARSING --- | |
| var rawKey = data.google_key || ""; | |
| var formName = "Unknown Form"; | |
| var providedSecret = ""; | |
| if (rawKey.indexOf("|") > -1) { | |
| var parts = rawKey.split("|"); | |
| formName = parts[0].trim(); | |
| providedSecret = parts[1].trim(); | |
| } else { | |
| formName = rawKey.trim() || "Unknown Form"; | |
| } | |
| if (EXPECTED_SECRET !== "" && providedSecret !== EXPECTED_SECRET) { | |
| console.warn("Security Alert: Invalid Secret. Form: " + formName); | |
| return ContentService.createTextOutput("Access Denied").setMimeType(ContentService.MimeType.TEXT); | |
| } | |
| // --- 4. PREPARE SHEET --- | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getSheetByName(SHEET_NAME); | |
| if (!sheet) sheet = ss.insertSheet(SHEET_NAME); | |
| // Initialize headers if empty | |
| if (sheet.getLastColumn() === 0) { | |
| // NOTE: "GCLID" is the anchor for dynamic columns. Do not rename it. | |
| var initialHeaders = ["Date", "Form Name", "GCLID", "Campaign ID", "Raw Data"]; | |
| sheet.appendRow(initialHeaders); | |
| sheet.getRange(1, 1, 1, initialHeaders.length).setFontWeight("bold"); | |
| sheet.setFrozenRows(1); | |
| } | |
| // --- 5. HARDENED ANTI-DUPLICATE CHECK --- | |
| var incomingGCLID = (data.gcl_id || "").toString().trim(); | |
| // DEFINE UNIQUE FINGERPRINT | |
| // Case 1 (Standard): Use GCLID. | |
| // Case 2 (Test/Bug): If no GCLID, use the first user answer (Email/Name) as the ID. | |
| var fingerprint = incomingGCLID; | |
| if ((!fingerprint || fingerprint === "N/A") && data.user_column_data && data.user_column_data.length > 0) { | |
| // We use the value of the first question (e.g. email) to avoid test duplicates | |
| fingerprint = (data.user_column_data[0].string_value || "").trim(); | |
| } | |
| // Only run check if we have a valid fingerprint and existing data | |
| if (fingerprint && fingerprint !== "N/A" && sheet.getLastRow() > 1) { | |
| var lastRow = sheet.getLastRow(); | |
| var checkLimit = 1000; // Look back 1000 rows | |
| var startRow = Math.max(2, lastRow - checkLimit + 1); | |
| var numRows = lastRow - startRow + 1; | |
| // Grab the entire data block (Matrix) for the recent rows | |
| var dataMatrix = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()).getValues(); | |
| // Deep scan: Check EVERY cell in the recent rows for the fingerprint | |
| var isDuplicate = dataMatrix.some(function(row) { | |
| return row.some(function(cell) { | |
| return cell.toString().trim() === fingerprint; | |
| }); | |
| }); | |
| if (isDuplicate) { | |
| console.info("Duplicate Blocked (Matrix Match): " + fingerprint); | |
| // Return Success so Google stops retrying | |
| return ContentService.createTextOutput("Duplicate Skipped").setMimeType(ContentService.MimeType.TEXT); | |
| } | |
| } | |
| // --- 6. DATA PROCESSING (DYNAMIC COLUMNS) --- | |
| var incomingData = {}; | |
| var rawDetails = []; | |
| var htmlList = "<ul>"; | |
| var phoneNumber = ""; | |
| var p_user = "", p_work = "", p_other = ""; | |
| if (data.user_column_data) { | |
| data.user_column_data.forEach(function(item) { | |
| // Google sends "column_name" (The text of the question) | |
| var headerName = item.column_name || item.column_id; | |
| var value = item.string_value; | |
| var colId = item.column_id; | |
| // Storage for column mapping | |
| incomingData[headerName] = value; | |
| // Email List construction | |
| htmlList += "<li style='margin-bottom: 5px;'><strong>" + headerName + ":</strong> " + value + "</li>"; | |
| rawDetails.push(headerName + ": " + value); | |
| // Phone detection | |
| if (colId === "PHONE_NUMBER") p_user = value; | |
| else if (colId === "WORK_PHONE") p_work = value; | |
| else if (/phone|tel|mobile|cell/i.test(headerName)) p_other = value; | |
| }); | |
| } | |
| htmlList += "</ul>"; | |
| phoneNumber = p_user || p_work || p_other; | |
| // Technical Data | |
| incomingData["Date"] = new Date(); | |
| incomingData["Form Name"] = formName; | |
| incomingData["GCLID"] = incomingGCLID || "N/A"; | |
| incomingData["Campaign ID"] = data.campaign_id || "N/A"; | |
| incomingData["Raw Data"] = rawDetails.join(" | "); | |
| // --- 7. DYNAMIC HEADERS & WRITE --- | |
| // Update columns if new questions appear | |
| updateHeadersIfNeeded(sheet, Object.keys(incomingData)); | |
| // Read headers again (in case they changed) | |
| var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; | |
| // Map data to the correct order | |
| var newRow = headers.map(function(header) { | |
| return incomingData[header] || ""; | |
| }); | |
| newRow = newRow.map(sanitizeForSheet); | |
| sheet.appendRow(newRow); | |
| // Immediate commit so the next lead sees this data | |
| SpreadsheetApp.flush(); | |
| // --- 8. SEND EMAIL --- | |
| var subject = (data.is_test ? "⚠️ [TEST] " : "🚀 ") + "New Lead: " + formName; | |
| var callAction = ""; | |
| if (phoneNumber) { | |
| callAction = "<p style='text-align: center; margin: 20px 0;'>" + | |
| "<a href='tel:" + phoneNumber + "' style='background-color:#2196F3; color:white; padding:12px 20px; text-decoration:none; border-radius:25px; font-weight:bold; font-size:16px;'>📞 Call " + phoneNumber + "</a></p>"; | |
| } | |
| var htmlBody = "<div style='font-family: Helvetica, Arial, sans-serif; max-width: 600px; margin: auto; border: 1px solid #ddd; border-radius: 8px; overflow: hidden;'>" + | |
| "<div style='background-color: #4CAF50; color: white; padding: 15px; text-align: center;'>" + | |
| "<h2 style='margin:0;'>👋 New Lead Received!</h2>" + | |
| "<p style='margin:5px 0 0 0; opacity: 0.9;'>" + formName + "</p></div>" + | |
| "<div style='padding: 20px;'><p>Here are the prospect's details:</p>" + | |
| "<div style='background-color: #f9f9f9; padding: 15px; border-radius: 5px; border-left: 4px solid #4CAF50;'>" + htmlList + "</div>" + | |
| callAction + | |
| "<hr style='border: 0; border-top: 1px solid #eee; margin: 20px 0;'>" + | |
| "<p style='font-size: 12px; color: #888; text-align: center;'>Campaign ID: " + (data.campaign_id || "N/A") + "<br>" + | |
| "<a href='" + ss.getUrl() + "' style='color: #4CAF50; text-decoration: none;'>Open Google Sheet</a></p></div></div>"; | |
| MailApp.sendEmail({ to: EMAIL_NOTIFICATION, subject: subject, htmlBody: htmlBody }); | |
| return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT); | |
| } catch (error) { | |
| console.error(error); | |
| try { | |
| MailApp.sendEmail(EMAIL_NOTIFICATION, "❌ SCRIPT ERROR", "A webhook error occurred. Check Logs."); | |
| } catch(e) {} | |
| // We return "Error" here, but Google sees 200 OK. | |
| return ContentService.createTextOutput("Error").setMimeType(ContentService.MimeType.TEXT); | |
| } finally { | |
| lock.releaseLock(); | |
| } | |
| } | |
| // --- UTILITY FUNCTIONS --- | |
| function sanitizeForSheet(value) { | |
| if (typeof value !== "string") return value; | |
| var normalized = value.replace(/[\u200B-\u200D\uFEFF\u00A0]/g, "").trimStart(); | |
| if (/^[=+\-@]/.test(normalized)) return "'" + value; | |
| return value; | |
| } | |
| function updateHeadersIfNeeded(sheet, incomingKeys) { | |
| var lastCol = sheet.getLastColumn(); | |
| if (lastCol === 0) return; | |
| var currentHeaders = sheet.getRange(1, 1, 1, lastCol).getValues()[0]; | |
| var missing = []; | |
| // Identify new keys | |
| incomingKeys.forEach(function(k) { | |
| if (currentHeaders.indexOf(k) === -1) missing.push(k); | |
| }); | |
| if (missing.length > 0) { | |
| // Find anchor column "GCLID" | |
| var insertIdx = currentHeaders.indexOf("GCLID"); | |
| if (insertIdx === -1) { | |
| // If no GCLID (abnormal structure), add to the end | |
| insertIdx = lastCol + 1; | |
| sheet.getRange(1, insertIdx, 1, missing.length).setValues([missing]).setFontWeight("bold"); | |
| } else { | |
| // If GCLID exists, insert BEFORE it to keep technical data on the right | |
| // insertIdx is 0-based, insertColumnsBefore is 1-based. | |
| var colNum = insertIdx + 1; | |
| sheet.insertColumnsBefore(colNum, missing.length); | |
| sheet.getRange(1, colNum, 1, missing.length).setValues([missing]).setFontWeight("bold").setBackground("#EFEFEF"); | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment