Get an email notification for each lead form extension conversion in google ads + sheets

lead form google ads notification fr

/**
 * SCRIPT: GOOGLE ADS FORMULAIRE DE LEADS E-MAIL + GOOGLE SHEETS
 * Author: Gemini & [Vincent Hsu/Suisseo]
 * Version: 3.2 (Handles Multiple Form)
 * En savoir plus et installer : https://www.suisseo.ch/blog/recevoir-un-e-mail-a-chaque-lead-issue-dun-formulaire-de-lead-google-ads-google-sheets/
 */

function doPost(e) {
  // --- 1. CONFIGURATION ---
  var EMAIL_NOTIFICATION = "your-email@example.com"; // E-mail qui recevra les leads ; si plusieurs, les séparer par des virgules
  var SHEET_NAME = "LEADS";
  // ------------------------
  // SECURITY CONFIGURATION
  // Laisser vide ("") pour désactiver la sécurité (La Clé servira uniquement de Nom du Formulaire).
  // Si défini (ex: "MonSecret123"), la Clé dans Google Ads devra être : Nom Formulaire|MonSecret123
  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);
    
    // Création automatique de l'onglet et des en-têtes (si vide)
    if (!sheet) {
      sheet = ss.insertSheet(SHEET_NAME);
    }
    
    // Si l'onglet est vide, on initialise les colonnes de base
    if (sheet.getLastColumn() === 0) {
      // Ordre définitif : Date | Nom Formulaire | (Questions...) | GCLID | Campagne | Dump
      var initialHeaders = ["Date", "Nom Formulaire", "GCLID", "ID Campagne", "Données Brutes"];
      sheet.appendRow(initialHeaders);
      sheet.getRange(1, 1, 1, initialHeaders.length).setFontWeight("bold");
      sheet.setFrozenRows(1);
    }

    // --- 2. TRAITEMENT DES DONNÉES ---
    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. Données Techniques (On fusionne tout ici)
    incomingData["Date"] = new Date();
    incomingData["Nom Formulaire"] = formName; // La colonne unique
    incomingData["GCLID"] = data.gcl_id || "N/A";
    incomingData["ID Campagne"] = data.campaign_id || "N/A";
    incomingData["Données Brutes"] = rawDetails.join(" | ");

    // --- 3. MISE À JOUR DES COLONNES (Auto-Adaptatif) ---
    // On envoie toutes les clés (Questions + Techniques) pour vérifier si les colonnes existent
    updateHeadersIfNeeded(sheet, Object.keys(incomingData));

    // --- 4. ÉCRITURE ---
    // On récupère l'ordre actuel des colonnes du Sheet (qui vient d'être mis à jour)
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // On remplit la ligne en respectant cet ordre
    var newRow = headers.map(function(header) {
      return incomingData[header] || ""; // Si vide, on laisse vide
    });
    
    newRow = newRow.map(sanitizeForSheet);
    sheet.appendRow(newRow);
    SpreadsheetApp.flush();
    
    // --- 5. EMAIL (Design Pro) ---
    var subject = (data.is_test ? "⚠️ [TEST] " : "🚀 ") + "Lead : " + formName;
    
    // Bouton d'appel conditionnel
    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;'>📞 Appeler le " + 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;'>👋 Nouveau Lead !</h2>" +
          "<p style='margin:5px 0 0 0; opacity: 0.9;'>" + formName + "</p>" +
        "</div>" +
        "<div style='padding: 20px;'>" +
          "<p>Voici les détails du prospect :</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;'>" +
            "Campagne ID : " + (data.campaign_id || "N/A") + "<br>" +
            "<a href='" + ss.getUrl() + "' style='color: #4CAF50; text-decoration: none;'>Ouvrir le fichier 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, "❌ ERREUR SCRIPT", "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();
  }
}

// --- FONCTIONS UTILITAIRES ---
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 = [];

  // On cherche quelles clés n'ont pas encore de colonne
  incomingKeys.forEach(function(k) { 
    if (currentHeaders.indexOf(k) === -1) missing.push(k); 
  });

  if (missing.length > 0) {
    // On essaie d'insérer les nouvelles questions AVANT le GCLID pour garder la structure
    var insertIdx = currentHeaders.indexOf("GCLID");
    
    if (insertIdx === -1) {
      // Si pas de GCLID, on ajoute à la fin
      insertIdx = lastCol + 1;
      sheet.getRange(1, insertIdx, 1, missing.length).setValues([missing]).setFontWeight("bold");
    } else {
      // Si GCLID existe, on insère avant (attention index base 1)
      insertIdx = insertIdx + 1; 
      sheet.insertColumnsBefore(insertIdx, missing.length);
      sheet.getRange(1, insertIdx, 1, missing.length).setValues([missing]).setFontWeight("bold").setBackground("#EFEFEF");
    }
  }
}