i am trying to merge selected google sheets tabs into one pdf
manage to get one work pdf export to drive but other two gives errors or the cell which has formulas shows #REF! in pdf output
need to add other 2 sheets named “Invprttwo” & “Invprtthree”
Please Help
function savepdftodrive() {
var sheetName = "INVOICE";
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var invoicesheet = spreadsheet.getSheetByName(sheetName);
var filename = "Invoice_" + invoicesheet.getRange("H5").getValue() + ".pdf";
// Create export parameters for PDF
var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?';
var exportOptions = {
exportFormat: 'pdf',
format: 'pdf',
size: 'letter',
portrait: true,
fitw: true,
sheetnames: false,
printtitle: false,
pagenumbers: false,
gridlines: false,
fzr: false,
gid: invoicesheet.getSheetId(), // the sheet's ID
};
var params = [];
for (var key in exportOptions) {
params.push(key + '=' + exportOptions[key]);
}
var finalUrl = url + params.join('&');
// Fetch the file from the URL
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(finalUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var blob = response.getBlob().setName(filename);
// Get the parent folder
var folder = DriveApp.getFileById(spreadsheet.getId()).getParents().next();
// Check if the 'OldCopies' folder exists, if not create it
var oldCopiesFolder;
var subfolders = folder.getFoldersByName('OldCopies');
if (subfolders.hasNext()) {
oldCopiesFolder = subfolders.next();
} else {
oldCopiesFolder = folder.createFolder('OldCopies');
}
// Move existing files with the same name to the 'OldCopies' folder
var existingFiles = folder.getFilesByName(filename);
while (existingFiles.hasNext()) {
var file = existingFiles.next();
file.moveTo(oldCopiesFolder);
}
// Save the new PDF file to the folder
folder.createFile(blob);
}
