Recevoir une notification quand un formulaire de lead google ads genère un lead et stocker dans Google Sheets

google_ads_lead_form_to_sheet en

/**
 * SCRIPT : GOOGLE ADS LEAD FORM VERS SHEETS + EMAIL
 * Auteur : Gemini & [Vincent Hsu/Suisseo]
 * Version : 3.2 EN 
 * 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 = "your-email@example.com"; // Separate multiple emails with commas
  var SHEET_NAME = "LEADS";
  // ------------------------
  // SECURITY CONFIGURATION
  // Leave empty ("") to disable security (The Key will be used as Form Name only).
  // If set (e.g. "MySecret123"), the Key in Google Ads must be: Form Name|MySecret123
  var EXPECTED_SECRET = ""; 
  // ------------------------

  // LOCK SERVICE: Prevents race conditions if 2 leads arrive at once
  var lock = LockService.getScriptLock();
  // Wait up to 30 seconds for other processes to finish
  if (!lock.tryLock(30000)) {
    return ContentService.createTextOutput("Server Busy").setMimeType(ContentService.MimeType.TEXT);
  }

  try {
    if (!e || !e.postData) return ContentService.createTextOutput("No Data");
    var data = JSON.parse(e.postData.contents);
    
    // --- SECURITY & FORM NAME PARSING ---
    var rawKey = data.google_key || "";
    var formName = "Unknown Form";
    var providedSecret = "";

    // Step A: Parse the input (Split by pipe "|")
    if (rawKey.indexOf("|") > -1) {
      var parts = rawKey.split("|");
      formName = parts[0].trim(); // "Devis Piscine"
      providedSecret = parts[1].trim(); // "MySecret123"
    } else {
      // No pipe found, the whole string is the name
      formName = rawKey.trim() || "Unknown Form";
    }

    // Step B: Check Security (Only if EXPECTED_SECRET is set)
    if (EXPECTED_SECRET !== "") {
      if (providedSecret !== EXPECTED_SECRET) {
        console.warn("Security Alert: Invalid Secret received. Form: " + formName);
        return ContentService.createTextOutput("Access Denied / Invalid Secret")
                             .setMimeType(ContentService.MimeType.TEXT);
      }
    }

    // ------------------------------------

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(SHEET_NAME);
    
    // Auto-create tab if missing
    if (!sheet) {
      sheet = ss.insertSheet(SHEET_NAME);
    }
    
    // If tab is empty, initialize base headers
    if (sheet.getLastColumn() === 0) {
      // Final Order: Date | Form Name | (Questions...) | GCLID | Campaign | Raw Data
      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);
    }

    // --- 2. DATA PROCESSING ---
    var incomingData = {};
    var rawDetails = []; 
    var htmlList = "<ul>";
    var phoneNumber = ""; 

    // storage for specific phone types
    var p_user = "";   // Priority 1: PHONE_NUMBER
    var p_work = "";   // Priority 2: WORK_PHONE
    var p_other = "";  // Priority 3: Regex fallback

    // 2a. Process Form Questions
    if (data.user_column_data) {
      data.user_column_data.forEach(function(item) {
        var headerName = item.column_name || item.column_id; 
        var value = item.string_value;
        var colId = item.column_id;
        
        incomingData[headerName] = value;
        
        // Build Email list
        htmlList += "<li style='margin-bottom: 5px;'><strong>" + headerName + ":</strong> " + value + "</li>";
        rawDetails.push(headerName + ": " + value);

        // Detect phone number for the Call Button
        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;

    // 2b. Technical Data (Merged here)
    incomingData["Date"] = new Date();
    incomingData["Form Name"] = formName; // The unique column
    incomingData["GCLID"] = data.gcl_id || "N/A";
    incomingData["Campaign ID"] = data.campaign_id || "N/A";
    incomingData["Raw Data"] = rawDetails.join(" | ");

    // --- 3. DYNAMIC COLUMN UPDATE (Auto-Adaptive) ---
    // Check if new questions need new columns
    updateHeadersIfNeeded(sheet, Object.keys(incomingData));

    // --- 4. WRITE TO SHEET ---
    // Read current headers to map data correctly
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    var newRow = headers.map(function(header) {
      return incomingData[header] || ""; // Leave empty if no data for this column
    });
    newRow = newRow.map(sanitizeForSheet);
    sheet.appendRow(newRow);
    SpreadsheetApp.flush();

    // --- 5. SEND EMAIL (Pro Design) ---
    var subject = (data.is_test ? "⚠️ [TEST] " : "🚀 ") + "New Lead: " + formName;
    
    // Conditional Call Button
    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) {}
    return ContentService.createTextOutput("Error").setMimeType(ContentService.MimeType.TEXT);
  } finally {
    // This runs whether the script succeeds OR fails.
    lock.releaseLock();
  }
}

// --- UTILITY FUNCTIONS ---
function sanitizeForSheet(value) {
  if (typeof value !== "string") return value;

  // Normalize weird Unicode whitespace
  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 keys that don't have a column yet
  incomingKeys.forEach(function(k) { 
    if (currentHeaders.indexOf(k) === -1) missing.push(k); 
  });

  if (missing.length > 0) {
    // Insert new questions BEFORE the GCLID to keep structure clean
    var insertIdx = currentHeaders.indexOf("GCLID");
    
    if (insertIdx === -1) {
      // If no GCLID, add to the end
      insertIdx = lastCol + 1;
      sheet.getRange(1, insertIdx, 1, missing.length).setValues([missing]).setFontWeight("bold");
    } else {
      // If GCLID exists, insert before (Index is 1-based)
      insertIdx = insertIdx + 1; 
      sheet.insertColumnsBefore(insertIdx, missing.length);
      sheet.getRange(1, insertIdx, 1, missing.length).setValues([missing]).setFontWeight("bold").setBackground("#EFEFEF");
    }
  }
}