I've been playing with the code some more and it runs all the way through but I get the error Utilities Write is not a function.
function exportSheetAsPDF() {
// Get the active spreadsheet
var ss = SpreadsheetApp.getActiveSheet();
// Check if the sheet name is "Export Sheet"
if (ss.getName() !== "Export Sheet") {
Logger.log("Warning: This script is intended for the 'Export Sheet' sheet.");
}
// Get the sheet containing cell references (adjust sheet name)
var refSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Financial Input");
// Check if the sheet exists
if (!refSheet) {
Logger.log("Error: Sheet named 'Financial Input' not found.");
return;
}
// Get cell references from the reference sheet (adjust cell references)
var fileNameCell = refSheet.getRange('I4');
var sharedDriveCell = refSheet.getRange('I2');
var folderPathCell = refSheet.getRange('I3');
// Get values from cells and handle invalid characters in filename
var fileName = fileNameCell.getValue().replace(/[\\/:*?"<>|]/g, '_') + ".pdf";
var sharedDrive = sharedDriveCell.getValue();
var folderPath = folderPathCell.getValue();
// Get the sheet with the content to export as PDF
var exportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Export Sheet");
// Check if the sheet exists
if (!exportSheet) {
Logger.log("Error: Sheet named 'Export Sheet' not found.");
return;
}
// Get the sheet range (adjust if needed)
var range = exportSheet.getRange(1, 1, exportSheet.getLastRow(), exportSheet.getLastColumn()); // Replace with your desired range
// Build the full file path
var fullPath = sharedDrive + "/" + folderPath + "/" + fileName;
// Create the temporary blob for the PDF content
var pdfBlob = Utilities.newBlob(range.getValues().join('\n'), "application/pdf").setName(fileName);
// Get the subfolder name
var subfolderName = fullPath.split('/')[1];
// Check for folders with the name
var folders = DriveApp.getFoldersByName(subfolderName);
// If there are folders, use the first one
var folder;
if (folders.hasNext()) {
folder = folders.next();
} else {
// Handle the case where no folders are found
Logger.log("Warning: No folder named '" + subfolderName + "' found. Creating folder...");
folder = DriveApp.createFolder(subfolderName);
}
// Check if Utilities object is available (already logged)
Logger.log("Utilities object: ", Utilities);
// Write the PDF content to the folder using Utilities.write
try {
Utilities.write(pdfBlob, MimeType.PDF, fileName, folder);
Logger.log("PDF saved successfully!");
} catch (error) {
Logger.log("Error saving PDF: " + error.message);
}
}
LinkedIn
Twitter
