Automate Elementor Form Submissions: Save Entries to Google Sheets with This Script

/**
 * Google app-script to utilise Elementor Pro From webhook.
 * Updated version with strict field filtering
 */

// Change to true to enable email notifications
let emailNotification = false;
let emailAddress = "Change_to_your_Email";

// DO NOT EDIT THESE NEXT PARAMS
let isNewSheet = false;
let postedData = [];

// System fields and properties to exclude
const SYSTEM_FIELDS = {
  // Control properties
  'e_gs_exclude': true,
  'e_gs_order': true,
  'e_gs_SheetName': true,
 
  // Elementor system fields
  'remote_ip': true,
  'page_url': true,
  'user_agent': true,
  'form_name': true,
  'referrer': true,
  'submitted_by': true,
  'submitted_on': true,
  'form_id': true,
  'Remote IP': true,
  'Page URL': true,
  'User Agent': true,
  'Powered by' : true,
  'heure_js' :true
};

function doGet(e) {
  return HtmlService.createHtmlOutput("Webhook URL active");
}

function doPost(e) {
  let params = JSON.stringify(e.parameter);
  params = JSON.parse(params);
  postedData = filterSystemFields(params);
  insertToSheet(postedData);
  return HtmlService.createHtmlOutput("Data received and filtered");
}

// New function to filter out system fields before processing
const filterSystemFields = (data) => {
  const filtered = {};
  Object.keys(data).forEach(key => {
    // Convert key to lowercase for case-insensitive comparison
    const lowerKey = key.toLowerCase();
    if (!Object.keys(SYSTEM_FIELDS).some(field =>
      lowerKey.includes(field.toLowerCase()) ||
      field.toLowerCase().includes(lowerKey)
    )) {
      filtered[key] = data[key];
    }
  });
  return filtered;
}

const flattenObject = (ob) => {
  let toReturn = {};
  for (let i in ob) {
    if (!ob.hasOwnProperty(i)) continue;
    
    // Skip system fields during flattening
    if (SYSTEM_FIELDS[i.toLowerCase()]) continue;
    
    if ((typeof ob[i]) !== 'object') {
      toReturn[i] = ob[i];
      continue;
    }
    
    let flatObject = flattenObject(ob[i]);
    for (let x in flatObject) {
      if (!flatObject.hasOwnProperty(x)) continue;
      toReturn[i + '.' + x] = flatObject[x];
    }
  }
  return toReturn;
}

const getHeaders = (formSheet, keys) => {
  let headers = [];
 
  if (!isNewSheet) {
    headers = formSheet.getRange(1, 1, 1, formSheet.getLastColumn()).getValues()[0];
  }
 
  // Filter out system fields from existing headers
  headers = headers.filter(header => !isSystemField(header));
 
  // Add new headers that aren't system fields
  const newHeaders = keys.filter(h => !headers.includes(h) && !isSystemField(h));
  headers = [...headers, ...newHeaders];
 
  return headers;
};

const isSystemField = (field) => {
  const lowerField = field.toLowerCase();
  return Object.keys(SYSTEM_FIELDS).some(sys =>
    lowerField.includes(sys.toLowerCase()) ||
    sys.toLowerCase().includes(lowerField)
  );
}

const getValues = (headers, flat) => {
  return headers.map(h => flat[h] || '');
}

const insertRowData = (sheet, row, values, bold = false) => {
  const currentRow = sheet.getRange(row, 1, 1, values.length);
  currentRow.setValues([values])
    .setFontWeight(bold ? "bold" : "normal")
    .setHorizontalAlignment("center");
}

const setHeaders = (sheet, values) => insertRowData(sheet, 1, values, true);

const setValues = (sheet, values) => {
  const lastRow = Math.max(sheet.getLastRow(), 1);
  sheet.insertRowAfter(lastRow);
  insertRowData(sheet, lastRow + 1, values);
}

const getFormSheet = () => {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = "Trajet Bon Details";
 
  if (activeSheet.getSheetByName(sheetName) == null) {
    const formSheet = activeSheet.insertSheet();
    formSheet.setName(sheetName);
    isNewSheet = true;
  }
  return activeSheet.getSheetByName(sheetName);
}

const insertToSheet = (data) => {
  const flat = flattenObject(data);
  const keys = Object.keys(flat);
  const formSheet = getFormSheet();
  const headers = getHeaders(formSheet, keys);
  const values = getValues(headers, flat);

  setHeaders(formSheet, headers);
  setValues(formSheet, values);

  if (emailNotification) {
    sendNotification(getSheetURL());
  }
}

const getSheetURL = () => SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getUrl();

const sendNotification = (url) => {
  MailApp.sendEmail(
    emailAddress,
    "New Form Submission Received",
    `A new form submission has been received and saved to: ${url}`,
    {
      name: 'Automatic Emailer Script'
    }
  );
};