Balance confirmation bulk email to be sent google sheet - Stack Overflow

admin管理员组

文章数量:1390749

Would require the below email to be sent to the vendors, I had written a code but there is an error which is coming ? Need help

I need the data to run from the excel and get the doc and keep sending to different vendors

function sendBalanceConfirmationLetters() {
  // --- Configuration ---
  const sheetName = "VendorBalances"; // Name of your Google Sheet
  const subject = "Balance Confirmation Request";
  const senderEmail = Session.getActiveUser().getEmail(); // Your email address
  const templateDocId = "1ElTC5N5yeHWLkyH2C5c0bbjWR-sLA9q2Rn6vOPX3azM"; // Replace with your Google Doc template ID
  const sentLogSheetName = "SentLog"; // Sheet to record sent emails

  // --- Get Data from Google Sheet ---
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0]; // Assuming first row is headers

  // --- Find Column Indexes ---
  const vendorNameCol = headers.indexOf("Vendor Name");
  const vendorEmailCol = headers.indexOf("Vendor Email");
  const balanceCol = headers.indexOf("Balance");

  if (vendorNameCol === -1 || vendorEmailCol === -1 || balanceCol === -1) {
    Logger.log("Missing required columns in sheet.");
    return;
  }

  // --- Get or Create Sent Log Sheet ---
  let sentLogSheet = ss.getSheetByName(sentLogSheetName);
  if (!sentLogSheet) {
    sentLogSheet = ss.insertSheet(sentLogSheetName);
    sentLogSheet.appendRow(["Timestamp", "Vendor Name", "Vendor Email", "Balance", "Status"]);
  }

  // --- Process Each Vendor ---
  for (let i = 1; i < data.length; i++) { // Start from 1 to skip headers
    const vendorName = data[i][vendorNameCol];
    const vendorEmail = data[i][vendorEmailCol];
    const balance = data[i][balanceCol];

    if (!vendorEmail || !vendorName || balance === undefined || balance === "") {
      Logger.log(`Skipping row ${i + 1} due to missing data.`);
      continue;
    }

    try {
      // --- Create Letter from Template ---
      const doc = DocumentApp.openById(templateDocId).makeCopy();
      const body = doc.getBody();

      // --- Replace Placeholders ---
      body.replaceText("{{Vendor Name}}", vendorName);
      body.replaceText("{{Balance}}", balance.toFixed(2)); // Format balance to 2 decimal places

      // --- Convert to PDF ---
      const pdfBlob = doc.getAs("application/pdf");
      doc.removeFromFolder(DriveApp.getFileById(doc.getId()).getParents().next());//removes the temp file copy
      DriveApp.getFileById(doc.getId()).setTrashed(true);//trash the temp file copy

      // --- Send Email ---
      MailApp.sendEmail({
        to: vendorEmail,
        subject: subject,
        body: `Dear ${vendorName},\nPlease find attached the balance confirmation letter.`,
        attachments: [pdfBlob],
        name: "Balance Confirmation",
      });

      // --- Record Sent Log ---
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, "Sent"]);

      Logger.log(`Email sent to ${vendorEmail} (${vendorName}).`);

    } catch (e) {
      Logger.log(`Error sending email to ${vendorEmail} (${vendorName}): ${e}`);
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, `Error: ${e}`]);
    }
  }

  Logger.log("Balance confirmation process complete.");
}

Doc File
google sheet

Would require the below email to be sent to the vendors, I had written a code but there is an error which is coming ? Need help

I need the data to run from the excel and get the doc and keep sending to different vendors

function sendBalanceConfirmationLetters() {
  // --- Configuration ---
  const sheetName = "VendorBalances"; // Name of your Google Sheet
  const subject = "Balance Confirmation Request";
  const senderEmail = Session.getActiveUser().getEmail(); // Your email address
  const templateDocId = "1ElTC5N5yeHWLkyH2C5c0bbjWR-sLA9q2Rn6vOPX3azM"; // Replace with your Google Doc template ID
  const sentLogSheetName = "SentLog"; // Sheet to record sent emails

  // --- Get Data from Google Sheet ---
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0]; // Assuming first row is headers

  // --- Find Column Indexes ---
  const vendorNameCol = headers.indexOf("Vendor Name");
  const vendorEmailCol = headers.indexOf("Vendor Email");
  const balanceCol = headers.indexOf("Balance");

  if (vendorNameCol === -1 || vendorEmailCol === -1 || balanceCol === -1) {
    Logger.log("Missing required columns in sheet.");
    return;
  }

  // --- Get or Create Sent Log Sheet ---
  let sentLogSheet = ss.getSheetByName(sentLogSheetName);
  if (!sentLogSheet) {
    sentLogSheet = ss.insertSheet(sentLogSheetName);
    sentLogSheet.appendRow(["Timestamp", "Vendor Name", "Vendor Email", "Balance", "Status"]);
  }

  // --- Process Each Vendor ---
  for (let i = 1; i < data.length; i++) { // Start from 1 to skip headers
    const vendorName = data[i][vendorNameCol];
    const vendorEmail = data[i][vendorEmailCol];
    const balance = data[i][balanceCol];

    if (!vendorEmail || !vendorName || balance === undefined || balance === "") {
      Logger.log(`Skipping row ${i + 1} due to missing data.`);
      continue;
    }

    try {
      // --- Create Letter from Template ---
      const doc = DocumentApp.openById(templateDocId).makeCopy();
      const body = doc.getBody();

      // --- Replace Placeholders ---
      body.replaceText("{{Vendor Name}}", vendorName);
      body.replaceText("{{Balance}}", balance.toFixed(2)); // Format balance to 2 decimal places

      // --- Convert to PDF ---
      const pdfBlob = doc.getAs("application/pdf");
      doc.removeFromFolder(DriveApp.getFileById(doc.getId()).getParents().next());//removes the temp file copy
      DriveApp.getFileById(doc.getId()).setTrashed(true);//trash the temp file copy

      // --- Send Email ---
      MailApp.sendEmail({
        to: vendorEmail,
        subject: subject,
        body: `Dear ${vendorName},\nPlease find attached the balance confirmation letter.`,
        attachments: [pdfBlob],
        name: "Balance Confirmation",
      });

      // --- Record Sent Log ---
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, "Sent"]);

      Logger.log(`Email sent to ${vendorEmail} (${vendorName}).`);

    } catch (e) {
      Logger.log(`Error sending email to ${vendorEmail} (${vendorName}): ${e}`);
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, `Error: ${e}`]);
    }
  }

  Logger.log("Balance confirmation process complete.");
}

Doc File
google sheet

• google-sheets
• google-apps-script

Share Improve this question Follow edited Mar 15 at 22:15 Wicket 38.8k99 gold badges8080 silver badges195195 bronze badges asked Mar 14 at 9:51 Vinay ChandrashekharVinay Chandrashekhar 4111 silver badge88 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Send email for Balance confirmation bulk

The first thing that I fixed is the column names instead of using "Vendor Email" use "Email ID"

The second one is for Google Docs Template handling

From

DocumentApp.openById(templateDocId).makeCopy();

To this

DriveApp.getFileById(templateDocId).makeCopy();

for explicitly avoids modifying the original template by working on a copy.

Also I modified this script.

doc.removeFromFolder(DriveApp.getFileById(doc.getId()).getParents().next());
DriveApp.getFileById(doc.getId()).setTrashed(true);

and change it into this

copiedFile.setTrashed(true);

to trash the temporary document.

I also changed the email body for the better approach

body: `Dear ${vendorName},\n\nPlease find attached your balance confirmation letter.\n\nBest regards,\n[Your Name]`,

Modified Script

function sendBalanceConfirmationLetters() {
  
  const sheetName = "VendorBalances"; 
  const sentLogSheetName = "SentLog"; 
  const templateDocId = "Google Docs Template ID";
  const subject = "Balance Confirmation Request";
 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0].map(h => h.trim()); 

  const vendorNameCol = headers.indexOf("Vendor Name");
  const vendorEmailCol = headers.indexOf("Email ID");
  const balanceCol = headers.indexOf("Balance");

  if (vendorNameCol === -1 || vendorEmailCol === -1 || balanceCol === -1) {
    Logger.log("Missing required columns in sheet.");
    return;
  }

 
  let sentLogSheet = ss.getSheetByName(sentLogSheetName);
  if (!sentLogSheet) {
    sentLogSheet = ss.insertSheet(sentLogSheetName);
    sentLogSheet.appendRow(["Timestamp", "Vendor Name", "Vendor Email", "Balance", "Status"]);
  }

 
  for (let i = 1; i < data.length; i++) {
    const vendorName = data[i][vendorNameCol];
    const vendorEmail = data[i][vendorEmailCol];
    const balance = data[i][balanceCol];

    if (!vendorEmail || !vendorName || balance === undefined || balance === "") {
      Logger.log(` Skipping row ${i + 1} due to missing data.`);
      continue;
    }

    try {
     
      const copiedFile = DriveApp.getFileById(templateDocId).makeCopy();
      const doc = DocumentApp.openById(copiedFile.getId());
      const body = doc.getBody();
      body.replaceText("{{Vendor Name}}", vendorName);
      body.replaceText("{{Balance}}", parseFloat(balance).toFixed(2));
      doc.saveAndClose();
      const pdfBlob = copiedFile.getAs("application/pdf");
      copiedFile.setTrashed(true); 


      MailApp.sendEmail({
        to: vendorEmail,
        subject: subject,
        body: `Dear ${vendorName},\n\nPlease find attached your balance confirmation letter.\n\nBest regards,\n[Your Name]`,
        attachments: [pdfBlob],
        name: "Balance Confirmation",
      });

      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, "Sent"]);
      Logger.log(`Email sent to ${vendorEmail} (${vendorName}).`);

    } catch (e) {
      Logger.log(`Error sending email to ${vendorEmail} (${vendorName}): ${e.message}`);
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, `Error: ${e.message}`]);
    }
  }

  Logger.log(" Balance confirmation process complete.");
}

Sample output

Reference

• Class DriveApp

本文标签： Balance confirmation bulk email to be sent google sheetStack Overflow