Trying to Email Multiple PDF Blobs in Sheets Javascript

The issue is with how you’re handling the pdf array and attachments. Try the code below and see if it fixes the issue:

function printMultiple() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  var lastRow = ss.getRange("AN:AN").getValues().filter(String).length;
  var targetRange = ss.getSheetByName("Show 3 Mileage Form").getRange(1,40,lastRow,1);
  var workerList = String(targetRange.getValues()).replaceAll(", ","+++").split(",");

  var printAllBox = ui.alert("PRINT ALL MILEAGE LOGS", "Attempting to generate pdfs for everybody who drove this week.\n\nThis might take a while, so don't touch anything.",ui.ButtonSet.OK_CANCEL);

  if (printAllBox === ui.Button.OK) {
    var timer = 0;
    var counter = 0;
    // Initialize the array outside the loop
    var pdfBlobs = [];

    for (var i = 0; i < workerList.length; i++) {
      workerList[i] = workerList[i].replaceAll("+++",", ");
      ss.getRange("E2").setValue(workerList[i]);
  
      if (ss.getRange("PRINT!I10").getValue() == "") {
        continue;  // Skip empty entries
      }
              
      var sheet;
      var email = Session.getEffectiveUser().getEmail();
      var subject = "Multiple Mileage Logs";
      var body = "Mileage logs generated."

      // Look to see if it's a two pager or not
      if (ss.getRange("PRINT!G1").getValue() == "PAGE 1 of 1") {
        sheet = ss.getSheetByName("PRINT");
      } else {
        sheet = ss.getSheetByName("PRINT - 2 PAGE");
      }

      // show the print sheet
      sheet.showSheet();

      const fileName = ss.getRange("PRINT!J2").getValue() + ".pdf";
              
      // Base URL
      var exportURL = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

      var url_ext = 'exportFormat=pdf&format=pdf'
        + '&size=letter'
        + '&portrait=false'
        + '&fith=true&'
        + "&gridlines=false" 
        + "&top_margin=0.25" 
        + "&bottom_margin=0.25" 
        + "&left_margin=0.5" 
        + "&right_margin=0.5" 
        + '&sheetnames=false&printtitle=false'
        + '&pagenumbers=false&gridlines=false'
        + '&fzr=false'
        + '&gid=';

      var token = ScriptApp.getOAuthToken();

      // Convert your specific sheet to blob
      var response = UrlFetchApp.fetch(exportURL + url_ext + sheet.getSheetId(), {
        headers: { 'Authorization': 'Bearer ' + token }
      });

      //  And then hide the printsheet
      sheet.hideSheet();

      // Convert the response to a blob and store in our array
      var pdf = response.getBlob().setName(fileName);
      // Add the PDF blob to our array
      pdfBlobs.push(pdf);

      // Check to see if they have the right folders in place, and if not, create them
      var folder1 = "gripPDFs"
      var folder2 = ss.getRange("PRINT!C5").getValue()
      var folder3 = "mileage logs"
      var folder4 = ss.getRange("PRINT!K2").getValue()

      /* Find the first level folder, create if the folder does not exist */
      var folders = DriveApp.getFoldersByName(folder1);
      var firstLevelFolder = (folders.hasNext()) ? folders.next() : DriveApp.createFolder(folder1);
                                    
      /* Layer2 */
      folders = DriveApp.getFoldersByName(folder2);
      var secondlevelFolder = (folders.hasNext()) ? folders.next() : firstLevelFolder.createFolder(folder2); 

      /* Layer3 */
      folders = DriveApp.getFoldersByName(folder3);
      var thirdlevelFolder = (folders.hasNext()) ? folders.next() : secondlevelFolder.createFolder(folder3); 

      /* Layer4 */
      folders = DriveApp.getFoldersByName(folder4);
      var finalfolder = (folders.hasNext()) ? folders.next() : thirdlevelFolder.createFolder(folder4); 

      // Save to Drive
      finalfolder.createFile(pdf);

      counter = (counter + 1);
      timer = timer + 1;
      if (timer > 3) { 
        // Sleep to appease the Google Lag Police
        SpreadsheetApp.flush();
        Utilities.sleep(5000);
        SpreadsheetApp.flush();
        timer = 0;
      }
    }

    // Send email with all PDFs attached
    GmailApp.sendEmail(email, subject, body, {
      attachments: pdfBlobs  // Pass the array of blobs directly
    });

    var pdfResults = ui.alert("PROCESS COMPLETE", 
      counter + " total logs printed. The files have been emailed to you and can be found under " +
      folder1 + "/" + folder2 + "/" + folder3 + "/" + folder4 + " in your Google Drive.",
      ui.ButtonSet.OK);
  }
}

I moved the pdfBlobs array initialisation outside the loop. Also pushed each pdf blob to the array using pdfBlobs.push(pdf) and removed the square brackets when passing the attachments array to GmailApp.sendEmail(). I added a continue statement to skip empty entries and cleaned up some of the code formatting structure.

The issue was that you were creating a new array each time through the loop and also trying to attach the array wrapped in another array ([pdfCluster])

What the code will do now is:

• Create all pdfs
• Store them in a single array
• Save them to drive
• Send a single email with all pdfs attached.

Try this and see if it works.

UPDATE:

Regarding the new issue you mentioned in the comments below, I made few changes to the code which you can find below:

function printMultiple() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  var lastRow = ss.getRange("AN:AN").getValues().filter(String).length;
  var targetRange = ss.getSheetByName("Show 3 Mileage Form").getRange(1,40,lastRow,1);
  var workerList = String(targetRange.getValues()).replaceAll(", ","+++").split(",");

  var printAllBox = ui.alert("PRINT ALL MILEAGE LOGS", "Attempting to generate pdfs for everybody who drove this week.\n\nThis might take a while, so don't touch anything.",ui.ButtonSet.OK_CANCEL);

  if (printAllBox === ui.Button.OK) {
    var timer = 0;
    var counter = 0;
    // Initialize the array outside the loop
    var pdfAttachments = [];

    for (var i = 0; i < workerList.length; i++) {
      workerList[i] = workerList[i].replaceAll("+++",", ");
      ss.getRange("E2").setValue(workerList[i]);
  
      if (ss.getRange("PRINT!I10").getValue() == "") {
        continue;  // Skip empty entries
      }
              
      var sheet;
      var email = Session.getEffectiveUser().getEmail();
      var subject = "Multiple Mileage Logs";
      var body = "Mileage logs generated."

      // Look to see if it's a two pager or not
      if (ss.getRange("PRINT!G1").getValue() == "PAGE 1 of 1") {
        sheet = ss.getSheetByName("PRINT");
      } else {
        sheet = ss.getSheetByName("PRINT - 2 PAGE");
      }

      // show the print sheet
      sheet.showSheet();

      const fileName = ss.getRange("PRINT!J2").getValue() + ".pdf";
              
      // Base URL
      var exportURL = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

      var url_ext = 'exportFormat=pdf&format=pdf'
        + '&size=letter'
        + '&portrait=false'
        + '&fith=true&'
        + "&gridlines=false" 
        + "&top_margin=0.25" 
        + "&bottom_margin=0.25" 
        + "&left_margin=0.5" 
        + "&right_margin=0.5" 
        + '&sheetnames=false&printtitle=false'
        + '&pagenumbers=false&gridlines=false'
        + '&fzr=false'
        + '&gid=';

      var token = ScriptApp.getOAuthToken();

      // Convert your specific sheet to blob
      var response = UrlFetchApp.fetch(exportURL + url_ext + sheet.getSheetId(), {
        headers: { 'Authorization': 'Bearer ' + token }
      });

      //  And then hide the printsheet
      sheet.hideSheet();

      // Convert the response to a blob
      var pdf = response.getBlob().setName(fileName);

      // Create attachment object with all necessary properties
      var attachmentObj = {
        fileName: fileName,
        content: pdf.getBytes(),
        mimeType: "application/pdf"
      };

      // Add the attachment object to our array
      pdfAttachments.push(attachmentObj);

      // Check to see if they have the right folders in place, and if not, create them
      var folder1 = "gripPDFs"
      var folder2 = ss.getRange("PRINT!C5").getValue()
      var folder3 = "mileage logs"
      var folder4 = ss.getRange("PRINT!K2").getValue()

      /* Find the first level folder, create if the folder does not exist */
      var folders = DriveApp.getFoldersByName(folder1);
      var firstLevelFolder = (folders.hasNext()) ? folders.next() : DriveApp.createFolder(folder1);
                                    
      /* Layer2 */
      folders = DriveApp.getFoldersByName(folder2);
      var secondlevelFolder = (folders.hasNext()) ? folders.next() : firstLevelFolder.createFolder(folder2); 

      /* Layer3 */
      folders = DriveApp.getFoldersByName(folder3);
      var thirdlevelFolder = (folders.hasNext()) ? folders.next() : secondlevelFolder.createFolder(folder3); 

      /* Layer4 */
      folders = DriveApp.getFoldersByName(folder4);
      var finalfolder = (folders.hasNext()) ? folders.next() : thirdlevelFolder.createFolder(folder4); 

      // Save to Drive
      finalfolder.createFile(pdf);

      counter = (counter + 1);
      timer = timer + 1;
      if (timer > 3) { 
        // Sleep to appease the Google Lag Police
        SpreadsheetApp.flush();
        Utilities.sleep(5000);
        SpreadsheetApp.flush();
        timer = 0;
      }
    }

    // Send email with all PDFs attached using the attachment objects
    GmailApp.sendEmail(email, subject, body, {
      attachments: pdfAttachments
    });

    var pdfResults = ui.alert("PROCESS COMPLETE", 
      counter + " total logs printed. The files have been emailed to you and can be found under " +
      folder1 + "/" + folder2 + "/" + folder3 + "/" + folder4 + " in your Google Drive.",
      ui.ButtonSet.OK);
  }
}

Changes I made are:

• I created and used pdfAttachments instead of pdfBlobs

• For each pdf, I now create fileName which is the name of the pdf file; content which is the pdf bytes using pdf.getBytes(); and mimeType which is set to “application/pdf”

• Push this attachment object to pdfAttachments

• Pass pdfAttachments to GmailApp.sendEmail()

This approach should preserve the attachment properties you were using in your single attachment method, while still allowing multiple attachments. The fileName, content, and mimeType properties should now be consistent with the previous implementation.

This should hopefully resolve the attachment issue.