Long time reader first time asker:
I was asked to fix a Google Script that compiles data from tabs to create a PDF sans a few tabs. Either way, the script throws an exception error when we try to run it.
Drive and Docs service status is set to "ON for everyone" I'm an editor for the file and created the dev project
function onOpen() {
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('Export OR Proposal Sheets', 'generateProposalOR')
.addItem('Export WA Proposal Sheets', 'generateProposalWA')
.addItem('Export LAAS Sheets', 'generateLAASPdf')
.addToUi()
}
function generateProposalOR() {
/*
Author: http://haw.productions
Date Created: October 2019
Updated by Amaya Maya February 2023
*/
var sourceSpreadsheet = SpreadsheetApp.getActive();
var pdfName = sourceSpreadsheet.getName();
var sheetName1 = "Presentation Cover";
var sheetName2 = "Table of Contents";
var sheetName3 = "OR 1. Who We Are";
// var sheetName5 = "2. Why LED Lights";
var sheetName6 = "3. Comparison";
var sheetName7 = "4. What We Found";
var sheetName9 = "5. How We Can Help";
var sheetName10 = "6. Project Estimate";
var sheetName11 = "7. Cash Flow Solution";
// var sheetName12 = "8. Application";
var sheetName14 = "9. Accounting Summary";
var sheetName15 = "10. Work Order";
// var sheetName16 = "11. Follow Up Plan";
// var sheetName17 = "Demos";
var sourceSheet1 = sourceSpreadsheet.getSheetByName(sheetName1);
var sourceSheet2 = sourceSpreadsheet.getSheetByName(sheetName2);
var sourceSheet3 = sourceSpreadsheet.getSheetByName(sheetName3);
// var sourceSheet5 = sourceSpreadsheet.getSheetByName(sheetName5);
var sourceSheet6 = sourceSpreadsheet.getSheetByName(sheetName6);
var sourceSheet7 = sourceSpreadsheet.getSheetByName(sheetName7);
var sourceSheet9 = sourceSpreadsheet.getSheetByName(sheetName9);
var sourceSheet10 = sourceSpreadsheet.getSheetByName(sheetName10);
var sourceSheet11 = sourceSpreadsheet.getSheetByName(sheetName11);
// var sourceSheet12 = sourceSpreadsheet.getSheetByName(sheetName12);
var sourceSheet14 = sourceSpreadsheet.getSheetByName(sheetName14);
var sourceSheet15 = sourceSpreadsheet.getSheetByName(sheetName15);
// var sourceSheet16 = sourceSpreadsheet.getSheetByName(sheetName16);
// var sourceSheet17 = sourceSpreadsheet.getSheetByName(sheetName17);
var folder = DriveApp.getFolderById('1lzqXrqD9qUC9P2sqgnvkjQIieRylN7Yv'); //DriveApp.getRootFolder();
//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
var destSheet1 = destSpreadsheet.getSheetByName(sheetName1);
var destSheet2 = destSpreadsheet.getSheetByName(sheetName2);
var destSheet3 = destSpreadsheet.getSheetByName(sheetName3);
// var destSheet5 = destSpreadsheet.getSheetByName(sheetName5);
var destSheet6 = destSpreadsheet.getSheetByName(sheetName6);
var destSheet7 = destSpreadsheet.getSheetByName(sheetName7);
var destSheet9 = destSpreadsheet.getSheetByName(sheetName9);
var destSheet10 = destSpreadsheet.getSheetByName(sheetName10);
var destSheet11 = destSpreadsheet.getSheetByName(sheetName11);
// var destSheet12 = destSpreadsheet.getSheetByName(sheetName12);
var destSheet14 = destSpreadsheet.getSheetByName(sheetName14);
var destSheet15 = destSpreadsheet.getSheetByName(sheetName15);
// var destSheet16 = destSpreadsheet.getSheetByName(sheetName16);
// var destSheet17 = destSpreadsheet.getSheetByName(sheetName17);
//repace cell values with text (to avoid broken references)
var sourceRange1 = sourceSheet1.getRange(1,1,sourceSheet1.getMaxRows(),sourceSheet1.getMaxColumns());
var sourceRange2 = sourceSheet2.getRange(1,1,sourceSheet2.getMaxRows(),sourceSheet2.getMaxColumns());
var sourceRange3 = sourceSheet3.getRange(1,1,sourceSheet3.getMaxRows(),sourceSheet3.getMaxColumns());
var sourceRange5 = sourceSheet5.getRange(1,1,sourceSheet5.getMaxRows(),sourceSheet5.getMaxColumns());
var sourceRange6 = sourceSheet6.getRange(1,1,sourceSheet6.getMaxRows(),sourceSheet6.getMaxColumns());
var sourceRange7 = sourceSheet7.getRange(1,1,sourceSheet7.getMaxRows(),sourceSheet7.getMaxColumns());
var sourceRange9 = sourceSheet9.getRange(1,1,sourceSheet9.getMaxRows(),sourceSheet9.getMaxColumns());
var sourceRange10 = sourceSheet10.getRange(1,1,sourceSheet10.getMaxRows(),sourceSheet10.getMaxColumns());
var sourceRange11 = sourceSheet11.getRange(1,1,sourceSheet11.getMaxRows(),sourceSheet11.getMaxColumns());
var sourceRange12 = sourceSheet12.getRange(1,1,sourceSheet12.getMaxRows(),sourceSheet12.getMaxColumns());
var sourceRange14 = sourceSheet14.getRange(1,1,sourceSheet14.getMaxRows(),sourceSheet14.getMaxColumns());
var sourceRange15 = sourceSheet15.getRange(1,1,sourceSheet15.getMaxRows(),sourceSheet15.getMaxColumns());
var sourceRange16 = sourceSheet16.getRange(1,1,sourceSheet16.getMaxRows(),sourceSheet16.getMaxColumns());
var sourceRange17 = sourceSheet17.getRange(1,1,sourceSheet17.getMaxRows(),sourceSheet17.getMaxColumns());
var sourcevalues1 = sourceRange1.getValues();
var sourcevalues2 = sourceRange2.getValues();
var sourcevalues3 = sourceRange3.getValues();
var sourcevalues5 = sourceRange5.getValues();
var sourcevalues6 = sourceRange6.getValues();
var sourcevalues7 = sourceRange7.getValues();
var sourcevalues9 = sourceRange9.getValues();
var sourcevalues10 = sourceRange10.getValues();
var sourcevalues11 = sourceRange11.getValues();
var sourcevalues12 = sourceRange12.getValues();
var sourcevalues14 = sourceRange14.getValues();
var sourcevalues15 = sourceRange15.getValues();
var sourcevalues16 = sourceRange16.getValues();
var sourcevalues17 = sourceRange17.getValues();
var destRange1 = destSheet1.getRange(1,1,destSheet1.getMaxRows(),destSheet1.getMaxColumns());
var destRange2 = destSheet2.getRange(1,1,destSheet2.getMaxRows(),destSheet2.getMaxColumns());
var destRange3 = destSheet3.getRange(1,1,destSheet3.getMaxRows(),destSheet3.getMaxColumns());
var destRange5 = destSheet5.getRange(1,1,destSheet5.getMaxRows(),destSheet5.getMaxColumns());
var destRange6 = destSheet6.getRange(1,1,destSheet6.getMaxRows(),destSheet6.getMaxColumns());
var destRange7 = destSheet7.getRange(1,1,destSheet7.getMaxRows(),destSheet7.getMaxColumns());
var destRange9 = destSheet9.getRange(1,1,destSheet9.getMaxRows(),destSheet9.getMaxColumns());
var destRange10 = destSheet10.getRange(1,1,destSheet10.getMaxRows(),destSheet10.getMaxColumns());
var destRange11 = destSheet11.getRange(1,1,destSheet11.getMaxRows(),destSheet11.getMaxColumns());
var destRange12 = destSheet12.getRange(1,1,destSheet12.getMaxRows(),destSheet12.getMaxColumns());
var destRange14 = destSheet14.getRange(1,1,destSheet14.getMaxRows(),destSheet14.getMaxColumns());
var destRange15 = destSheet15.getRange(1,1,destSheet15.getMaxRows(),destSheet15.getMaxColumns());
var destRange16 = destSheet16.getRange(1,1,destSheet16.getMaxRows(),destSheet16.getMaxColumns());
var destRange17 = destSheet17.getRange(1,1,destSheet17.getMaxRows(),destSheet17.getMaxColumns());
destRange1.setValues(sourcevalues1);
destRange2.setValues(sourcevalues2);
destRange3.setValues(sourcevalues3);
destRange5.setValues(sourcevalues5);
destRange6.setValues(sourcevalues6);
destRange7.setValues(sourcevalues7);
destRange9.setValues(sourcevalues9);
destRange10.setValues(sourcevalues10);
destRange11.setValues(sourcevalues11);
destRange12.setValues(sourcevalues12);
destRange14.setValues(sourcevalues14);
destRange15.setValues(sourcevalues15);
destRange16.setValues(sourcevalues16);
destRange17.setValues(sourcevalues17);
//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName1 &&
sheets[i].getSheetName() != sheetName2 &&
sheets[i].getSheetName() != sheetName3 &&
// sheets[i].getSheetName() != sheetName5 &&
sheets[i].getSheetName() != sheetName6 &&
sheets[i].getSheetName() != sheetName7 &&
sheets[i].getSheetName() != sheetName9 &&
sheets[i].getSheetName() != sheetName10 &&
sheets[i].getSheetName() != sheetName11 &&
// sheets[i].getSheetName() != sheetName12 &&
sheets[i].getSheetName() != sheetName14 &&
sheets[i].getSheetName() != sheetName15){
// sheets[i].getSheetName() != sheetName16 &&
// sheets[i].getSheetName() != sheetName17){
destSpreadsheet.deleteSheet(sheets[i]);
}
}
//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);
// Display a modal dialog box with custom HtmlService content.
const htmlOutput = HtmlService
.createHtmlOutput('<p style="font-family:arial;font-weight:bold">Click to open <a href="' + newFile.getUrl() + '" target="_blank">' + pdfName + '</a></p>')
.setWidth(300)
.setHeight(80)
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}
Taking a look at the error message it seems to imply that the authenticated user making the call does not have access to the folder with the ID "1lzqXrqD9qUC9P2sqgnvkjQIieRylN7Yv"
Could you please try to access https://drive.google.com/drive/folders/1lzqXrqD9qUC9P2sqgnvkjQIieRylN7Yv and see if you actually can access it? of not, maybe asking the owner to share it with you would work.
One thing to keep in mind is that Drive items need to be explicitly shared to allow users to access them which means that even if you are a super admin you won't have access to the folder unless it's shared with you.
Hope it helps!