1

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.

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

6 Comments

6:54:37 AM Error Exception: Conversion from application/vnd.openxmlformats-officedocument.wordprocessingml.document to application/pdf failed. (anonymous) @ Email Pitch Fees Out.gs:46 PitchFees2026 @ Email Pitch Fees Out.gs:15
I appreciate the response. The error you've provided shows that a Microsoft Word (.docx) file is trying to be changed into a PDF but cannot do so. These types of files can't be converted directly, but converting them to a Google Document should work.
The easiest way is to copy the file's contents into a new Google Document and use its ID in the script. However, if you want to do it programmatically, check out the question DriveApp conversion from DocX to PDF fails.
Thank you. One more random question please. Would you by any chance know if it is possible to change some of the body or subject to Bold and Red and maybe the font size? It would be an handy option if it is possible to allow things to stand out. Sorry if this is a daft question.
Yes, it is possible to change some of the body of an email. However, the subject cannot be formatted. Format the body text of a gmail using Google Apps Script and Formatting Specific Portions of a Gmail Email Created with Apps Script are some of the related questions to what you'd like to do.
If you encounter an issue that's unrelated to the questions, please post a new question about it afterwards so that it'll be one question per post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.