google-apps-scriptpdfgoogle-drive-apiattachmentemail-attachments

Adding a drive doc to an existing AppScript email as an attachment


I have been working on a code for days now, and it's working fine. I am now on the final leg and wish to add another attachment from the drive with a docID.

Below is my current code that works fine and does various things.

const SHEETID = '1rx1lCYKdhi8dhivoYpUO6EIHb2TWTpiMVduK7M-L2A4';
const DOCID = '1sRZqPCkuATT9tQDZlJDp-DicP6saBpZoAXVvKWXT_XM';
const FOLDERID = '1wsyrUM29A1LIiKCjsJE7olKb0ycG2_M5';

function PitchFees2026() {
  const sheet = SpreadsheetApp.openById(SHEETID).getSheetByName('2026 Fees');
  const temp = DriveApp.getFileById(DOCID);
  const folder = DriveApp.getFolderById(FOLDERID);

  const data = sheet.getDataRange().getValues();
  const rows = data.slice(1);
  rows.forEach((row, index) => {
    const file = temp.makeCopy(folder);
    const doc = DocumentApp.openById(file.getId());
    const body = doc.getBody();
    data[0].forEach((heading, i) => {
      const header1 = heading.toUpperCase();
      body.replaceText('{NAME}', row[1]);
      body.replaceText('{PITCH}', row[0]);
      body.replaceText('{AMOUNT}', row[3]);
      body.replaceText('{FIRST}', row[4]);
      body.replaceText('{SECOND}', row[5]);
      body.replaceText('{REF}', row[10]);
      body.replaceText('{BNAME}', row[7]);
      body.replaceText('{CODE}', row[8]);
      body.replaceText('{NUMBER}', row[9]);
      body.replaceText('{TERMS}', row[6]);
    })
    doc.setName(row[10]);
    const blob = doc.getAs(MimeType.PDF);
    doc.saveAndClose();

    const pdf = folder.createFile(blob).setName(row[10] + '.pdf');

    const email = row[2];
    const subject = row[10];
    const messageBody = "This message (including any attachments) is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination, or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. \n \nIf you received this in error, please delete the material from your computer and contact the sender. \n\nPlease consider the environment before printing this e-mail.";


    MailApp.sendEmail({
      to: email,
      subject: subject,
      body: messageBody,
      attachments: [blob.getAs(MimeType.PDF)]
    });

    Logger.log(row);
    file.setTrashed(true);
  })

}

What I want to add is

    var file = DriveApp.getFileById("1vGvLVP2RV1krxnj8Mt6hMiFHVBoIdbFG");
    attachments.push(file.getAs(MimeType.PDF));

So I was trying to change the bottom of my main script to...

    var attachments = []
    var file = DriveApp.getFileById("1vGvLVP2RV1krxnj8Mt6hMiFHVBoIdbFG");
    attachments.push(file.getAs(MimeType.PDF));


    MailApp.sendEmail({
      to: email,
      subject: subject,
      body: messageBody,
      attachments: [blob.getAs(MimeType.PDF)]
    });

    Logger.log(row);
    file.setTrashed(true);
  })

}

I have been on this for days, so I'm probably not seeing something obvious now. I have already looked at various other questions/answers.


Solution

  • The documentation for Advanced parameters explains that attachments are an array of files to send with the email. To add another attachment from the drive with a docID,

    Change:

        var attachments = []
        var file = DriveApp.getFileById("1vGvLVP2RV1krxnj8Mt6hMiFHVBoIdbFG");
        attachments.push(file.getAs(MimeType.PDF));
    
    
        MailApp.sendEmail({
          to: email,
          subject: subject,
          body: messageBody,
          attachments: [blob.getAs(MimeType.PDF)]
        });
    
        Logger.log(row);
        file.setTrashed(true);
      })
    

    To:

        var docFile = DriveApp.getFileById("1vGvLVP2RV1krxnj8Mt6hMiFHVBoIdbFG").getAs(MimeType.PDF);
    
        MailApp.sendEmail({
          to: email,
          subject: subject,
          body: messageBody,
          attachments: [blob.getAs(MimeType.PDF), docFile]
        });
    
        Logger.log(row);
        file.setTrashed(true);
      })
    

    Here's a modified version of your script that should achieve what you'd like:

    const SHEETID = '1rx1lCYKdhi8dhivoYpUO6EIHb2TWTpiMVduK7M-L2A4';
    const DOCID = '1sRZqPCkuATT9tQDZlJDp-DicP6saBpZoAXVvKWXT_XM';
    const FOLDERID = '1wsyrUM29A1LIiKCjsJE7olKb0ycG2_M5';
    
    // Renamed to "pitchFees2026"
    function pitchFees2026() {
      const sheet = SpreadsheetApp.openById(SHEETID).getSheetByName('2026 Fees');
      const temp = DriveApp.getFileById(DOCID);
      const folder = DriveApp.getFolderById(FOLDERID);
    
      // Declared the second attachment outside of the loop
      const docFile = DriveApp.getFileById("1vGvLVP2RV1krxnj8Mt6hMiFHVBoIdbFG").getAs(MimeType.PDF);
      const data = sheet.getDataRange().getValues();
      const rows = data.slice(1);
      rows.forEach((row) => {
        const file = temp.makeCopy(folder);
        const doc = DocumentApp.openById(file.getId());
        const body = doc.getBody();
        // Key-value pair of the data to be replaced
        const r = {
          "{NAME}": row[1],
          "{PITCH}": row[0],
          "{AMOUNT}": row[3],
          "{FIRST}": row[4],
          "{SECOND}": row[5],
          "{REF}": row[10],
          "{BNAME}": row[7],
          "{CODE}": row[8],
          "{NUMBER}": row[9],
          "{TERMS}": row[6]
        }
        // Handles all text replacements in a single loop
        Object.entries(r).forEach(([s, r]) => {
          body.replaceText(s, r);
        });
        doc.setName(row[10]);
        doc.saveAndClose();
        const blob = doc.getAs(MimeType.PDF);
    
        folder.createFile(blob).setName(row[10] + '.pdf');
    
        const email = row[2];
        const subject = row[10];
        const messageBody = "This message (including any attachments) is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination, or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. \n \nIf you received this in error, please delete the material from your computer and contact the sender. \n\nPlease consider the environment before printing this e-mail.";
    
        MailApp.sendEmail({
          to: email,
          subject: subject,
          body: messageBody,
          // Removed ".getAs(MimeType.PDF)" and added "docFile"
          attachments: [blob, docFile]
        });
    
        Logger.log(row);
        file.setTrashed(true);
      })
    }
    

    REFERENCES