Bulk mailer for mail campaigns

 // ── CONFIG ────────────────────────────────────────────────────────────────

const SHEET_NAME = "Contacts";

const DAILY_LIMIT = 500; // ← Change to 2000 if you have Google Workspace

const FROM_NAME = "Ravi Rajamani";

// Column indices (1-based, matching your sheet layout)

const COL_EMAIL = 1; // Column A: to_email

const COL_SUBJECT = 7; // Column G: subject

const COL_BODY = 8; // Column H: body

const COL_STATUS = 9; // Column I: Status

// ── MAIN FUNCTION ─────────────────────────────────────────────────────────

function sendCampaign() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getSheetByName(SHEET_NAME);

  if (!sheet) {

    Logger.log('ERROR: Sheet "' + SHEET_NAME + '" not found. Check the tab name.');

    return;

  }

  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {

    Logger.log("No data rows found.");

    return;

  }

  // ── READ ALL DATA IN ONE CALL (the key fix) ───────────────────────────

  // This fetches the entire sheet as a 2D array in a single API call,

  // instead of making 4 individual calls per row inside the loop.

  const numRows = lastRow - 1; // exclude header row

  const allData = sheet.getRange(2, 1, numRows, COL_STATUS).getValues();

  // allData[i] is a 0-indexed array for row (i+2) of the sheet

  // allData[i][0] = to_email, [6] = subject, [7] = body, [8] = Status

  Logger.log("Data loaded. Total rows: " + numRows);

  // ── COLLECT ROWS TO SEND ──────────────────────────────────────────────

  // Build a list of {sheetRow, email, subject, body} for unsent rows only.

  // This entire pass is pure in-memory — no Sheets API calls.

  const toSend = [];

  for (let i = 0; i < allData.length; i++) {

    const status = String(allData[i][COL_STATUS - 1]).trim();

    if (status === "Sent") continue; // already sent

    if (status.startsWith("Error:")) continue; // skip permanent errors

    const email = String(allData[i][COL_EMAIL - 1]).trim();

    const subject = String(allData[i][COL_SUBJECT - 1]).trim();

    const body = String(allData[i][COL_BODY - 1]).trim();

    if (!email || !email.includes("@")) continue; // bad address

    if (!subject || !body) continue; // incomplete row

    toSend.push({ sheetRow: i + 2, email, subject, body }); // sheetRow is 1-based

  }

  Logger.log("Unsent rows found: " + toSend.length);

  if (toSend.length === 0) {

    Logger.log("Campaign complete — all rows have been sent.");

    return;

  }

  // ── SEND EMAILS ───────────────────────────────────────────────────────

  // Batch status updates: collect changes, then write them all at once

  // at the end of the run (or every 50 sends as a safety checkpoint).

  let sentThisRun = 0;

  let errorCount = 0;

  const statusUpdates = []; // {sheetRow, value}

  const limit = Math.min(DAILY_LIMIT, toSend.length);

  for (let j = 0; j < limit; j++) {

    const { sheetRow, email, subject, body } = toSend[j];

    try {

      MailApp.sendEmail({

        to: email,

        subject: subject,

        body: body,

        name: FROM_NAME

      });

      statusUpdates.push({ sheetRow, value: "Sent" });

      sentThisRun++;

      if (sentThisRun % 50 === 0) {

        // Checkpoint write every 50 sends so progress isn't lost if we time out

        flushStatusUpdates(sheet, statusUpdates);

        Logger.log("Checkpoint: " + sentThisRun + " sent so far...");

      }

    } catch (e) {

      statusUpdates.push({ sheetRow, value: "Error: " + e.message });

      errorCount++;

      Logger.log("FAILED row " + sheetRow + " (" + email + "): " + e.message);

    }

    // 200ms pause to stay within Gmail's per-second rate limit

    Utilities.sleep(200);

  }

  // ── FINAL WRITE ───────────────────────────────────────────────────────

  // Write any remaining status updates not yet flushed

  flushStatusUpdates(sheet, statusUpdates);

  // ── SUMMARY ───────────────────────────────────────────────────────────

  Logger.log("─────────────────────────────────────");

  Logger.log("Run complete.");

  Logger.log("Sent this run: " + sentThisRun);

  Logger.log("Errors this run: " + errorCount);

  Logger.log("Remaining: " + (toSend.length - sentThisRun));

  Logger.log("─────────────────────────────────────");

}

// ── HELPER: batch-write all pending status updates ─────────────────────────

// Clears the array after writing so we don't double-write on checkpoints.

function flushStatusUpdates(sheet, updates) {

  if (updates.length === 0) return;

  for (const { sheetRow, value } of updates) {

    sheet.getRange(sheetRow, COL_STATUS).setValue(value);

  }

  SpreadsheetApp.flush(); // force Sheets to commit writes immediately

  updates.length = 0; // clear in-place so the caller's array is emptied

}