Code.gs

/**
 * Google Apps Script for HTML Form to Google Sheets
 * Author: Md Munna Islam (msmunnabd)
 * Repository: https://github.com/msmunnabd/HTML-Form-to-Google-sheets
 * 
 * INSTRUCTIONS:
 * 1. Open your Google Sheet (https://sheets.new).
 * 2. Click on "Extensions" > "Apps Script".
 * 3. Replace all existing code with this Code.gs script.
 * 4. Click "Deploy" > "New deployment".
 * 5. Select type: "Web app".
 * 6. Set Description: "Form Submission Endpoint".
 * 7. Set Execute as: "Me" (your email).
 * 8. Set Who has access: "Anyone".
 * 9. Click "Deploy", Authorize access, and copy the Web App URL!
 */

const SHEET_NAME = "Sheet1";

function doPost(e) {
  const lock = LockService.getScriptLock();
  // Wait up to 30 seconds for other processes to finish
  lock.tryLock(30000);

  try {
    const doc = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = doc.getSheetByName(SHEET_NAME);
    if (!sheet) {
      sheet = doc.getActiveSheet();
    }

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn() || 1).getValues()[0];
    
    // Auto-create standard headers if sheet is empty
    if (headers.length === 0 || headers[0] === "") {
      const defaultHeaders = ["Timestamp", "fullName", "email", "phone", "subject", "message"];
      sheet.getRange(1, 1, 1, defaultHeaders.length).setValues([defaultHeaders]);
      sheet.getRange(1, 1, 1, defaultHeaders.length).setFontWeight("bold").setBackground("#f1f5f9");
    }

    const currentHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const newRow = [];

    // Map form fields to column headers dynamically
    currentHeaders.forEach(header => {
      if (header.toLowerCase() === "timestamp" || header.toLowerCase() === "date") {
        newRow.push(new Date().toLocaleString("en-US", { timeZone: "Asia/Dhaka" }));
      } else if (e.parameter[header] !== undefined) {
        newRow.push(e.parameter[header]);
      } else {
        newRow.push("");
      }
    });

    // Check for any new custom fields passed in form that don't have columns yet
    for (const key in e.parameter) {
      if (key !== "_gotcha" && !currentHeaders.includes(key)) {
        const nextCol = sheet.getLastColumn() + 1;
        sheet.getRange(1, nextCol).setValue(key).setFontWeight("bold");
        newRow.push(e.parameter[key]);
      }
    }

    // Append the row to Google Sheet
    sheet.appendRow(newRow);

    return ContentService
      .createTextOutput(JSON.stringify({ result: "success", row: sheet.getLastRow() }))
      .setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ result: "error", error: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

function doGet(e) {
  return ContentService
    .createTextOutput(JSON.stringify({ status: "online", message: "Google Sheets Web App Endpoint is Active" }))
    .setMimeType(ContentService.MimeType.JSON);
}