What I want: a Google Sheet that generates pdfs, saves the PDFs to your GDrive, and then sends you a single email with all of the pdfs attached.
What I've been able to do:
The (disgusting hobbyist) code I have loops through the creation of the pdfs just fine, but is gets hung up either when I try to save/push them to an array, or when I try to attach the array to the email.
The (probably) offending lines of code have been called out with a line of
======================================s
// ======================================== //
// //
// GENERATE MULTIPLE LOGS AT ONCE //
// //
// ======================================== //
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;
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() == ""){ }
else {
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' // export as pdf / csv / xls / xlsx
+ '&size=letter' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&fith=true&' // fit to page width, false for actual size CHANGED TO HEIGHT
+ "&gridlines=false"
+ "&top_margin=0.25"
+ "&bottom_margin=0.25"
+ "&left_margin=0.5"
+ "&right_margin=0.5"
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&gid='; // the sheet's Id
var token = ScriptApp.getOAuthToken();
//make an empty array to hold your fetched blobs
var pdf;
var pdfCluster = [];
// 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
pdf = response.getBlob().setName(fileName);
// =======================================================================================
// I originally tried using the variable "pdf" as the array
// and figured that maybe having a completely separate
// variable might help
pdfCluster.push(pdf);
// I also tried going the pdfCluster[i] = pdf route
// =======================================================================================
// Check to see if they have the right folders in place, and if not, create them
var folder1 = "gripPDFs" // gripPDFs
var folder2 = ss.getRange("PRINT!C5").getValue() // Job Name
var folder3 = "mileage logs" // mileage logs
var folder4 = ss.getRange("PRINT!K2").getValue() // date
/* 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);
// And create that shit
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;
}
}
}
//And mail it!
GmailApp.sendEmail(email, subject, body, {
// =======================================================================================
attachments: [pdfCluster]
// =======================================================================================
});
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);
}
}
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:
Try this and see if it works.
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.