I am using GAS to export several sheets of a Google spreadsheet to PDFs. Occasionally, the PDF exports a file with the following message:
This file might be unavailable right now due to heavy traffic.
I've tried using sleep() between exports to slow down the processing, but I still get the error on a number of the PDFs. Luckily they are relatively easy to spot because they are always 15kb in size, but I'd like to find a solution rather than a workaround.
function updateModules() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var template = ss.getSheetByName("-Template");
var questionTemplate = template.getRange(4,2,1,5);
var urlOptions = {muteHttpExceptions: true};
// make array of concept names
var data = ss.getSheetByName('-ACT English Answers').getRange(2,6,2000).getValues();
concepts = [];
concepts.push(data[0]);
var m = 0;
while (m < data.length) {
if (concepts.join().indexOf(data[m].join()) == -1) {
concepts.push(data[m]);
};
m++;
}
concepts.sort();
// loop through questions per sheet
var n = 0;
while (n < concepts.length) {
// Use for individual sheets with errors.
//concepts = [['Dashes'],['Usage'],['Main ideas'],['Independent/Dependent clauses']];
var name = concepts[n][0];
// make sheet with formatting and copy template for any new concepts
if (!ss.getSheetByName(name)) {
ss.insertSheet(name);
Logger.log("New sheet: " + name)
}
var sheet = ss.getSheetByName(name);
var sheetDestination = sheet.getRange(1,1,4,6);
var testCodeRange = sheet.getRange('A1:A');
var copyTemplate = template.getRange(1,1,4,6);
var sheet = ss.getSheetByName(name);
var columnBWidth = template.getColumnWidth(2);
var columnCWidth = template.getColumnWidth(3);
var columnEWidth = template.getColumnWidth(5);
// reset sheet, copy top 4 rows of template with column widths, set concept name in A1
sheet.getRange('A5:J').clear();
sheet.showColumns(1);
sheet.unhideRow(testCodeRange);
testCodeRange.setFontSize(9);
copyTemplate.copyTo(sheetDestination);
copyTemplate.copyTo(sheetDestination,SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS,false);
sheet.getRange('A1').setValue(name);
sheet.setRowHeight(3,77);
sheet.setHiddenGridlines(true);
sheet.getRange('A4:F').setBorder(false,false,false,false,false,true,"black",SpreadsheetApp.BorderStyle.SOLID);
Logger.log(name);
// iterate through each concept's question codes
var values = testCodeRange.getValues(); // get all data in one call
var row = 4; // A4 contains FILTER(), which lists question codes for given concept
while ( values[row-1] && values[row-1][0] != "" ) {
// get image dimensions
var question = values[row-1][0];
var passageUrl = "https://www.openpathtutoring.com/static/img/concepts/language/passages/" + question + ".jpg";
var questionUrl = "https://www.openpathtutoring.com/static/img/concepts/language/questions/" + question + ".jpg";
var passageImg = UrlFetchApp.fetch(passageUrl, urlOptions);
var questionImg = UrlFetchApp.fetch(questionUrl, urlOptions);
var passageBlob = passageImg.getBlob();
var questionBlob = questionImg.getBlob();
var passageSize = ImgApp.getSize(passageBlob);
var questionSize = ImgApp.getSize(questionBlob);
var questionHeight = questionSize.height / questionSize.width * columnEWidth + 20;
// for 2-column images, set passage height based on 2-column width; merge cells
if (passageSize.width > 1111) {
var passageHeight = passageSize.height / passageSize.width * (columnBWidth + columnCWidth) + 20;
}
else {
passageHeight = passageSize.height / passageSize.width * columnBWidth + 20;
}
// set rowHeight to the greater of passageHeight and questionHeight
if (passageHeight >= questionHeight) {
var rowHeight = passageHeight;
}
else {
var rowHeight = questionHeight;
};
// set rowHeight, copy image formulas from template, merge cells if needed
sheet.setRowHeight(row, rowHeight);
questionTemplate.copyTo(sheet.getRange(row,2,1,5));
if (passageSize.width > 1111) {
sheet.getRange(row,2,1,2).mergeAcross();
}
row++;
}
// unhide relevant rows; hide column A+F and gridlines; set horizontal borders
//sheet.hideRows(2,sheet.getMaxRows()-1);
sheet.hideRow(sheet.getRange(row,1,sheet.getMaxRows()-row+1));
sheet.hideColumns(1)
sheet.hideColumns(6);
SpreadsheetApp.flush();
//* save worksheets
savePdf(ss, name, "_" + name, "1qZHgCGCDjkbKwPOTHxo4sg1YuWtYxQva", 0.25);
Utilities.sleep(3000);
//*/
// show answer key after exporting PDFs
sheet.showColumns(1);
sheet.showColumns(6);
sheet.getRange('A1:A').setVerticalAlignment("middle");
//sheet.getRange('F1:F').setVerticalAlignment("middle").setHorizontalAlignment('center');
//* Create answer keys
SpreadsheetApp.flush();
savePdf(ss, name, "_" + name + " (Answers)", "1rdzP-okNaqG7d_03aBV3GNngjHeLARdO", 2.2);
Utilities.sleep(3000);
//*/
n++;
}
sortSheets();
}
function savePdf(spreadsheetId, sheetName, pdfName, pdfFolderId, bottomMargin) {
var sheetId = spreadsheetId.getSheetByName(sheetName).getSheetId();
var url_base = spreadsheetId.getUrl().replace(/edit$/,'');
var url_ext = 'export?exportFormat=pdf&format=pdf'
+ '&gid=' + sheetId
// following parameters are optional...
+ '&size=A4' // paper size: legal / letter / A4
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&top_margin=0.25'
+ '&bottom_margin=' + bottomMargin
+ '&left_margin=0.375'
+ '&right_margin=0.375'
+ '&sheetnames=false' //
+ '&printtitle=false'
+ '&pagenumbers=false' //hide optional headers and footers
+ '&gridlines=false' // hide gridlines
+ '&fzr=false'; // false = do not repeat row headers (frozen rows) on each page
var url_options = {headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),},muteHttpExceptions: true};
var response = UrlFetchApp.fetch(url_base + url_ext, url_options);
var blob = response.getBlob().getAs('application/pdf').setName(pdfName + '.pdf');
var folder = DriveApp.getFolderById(pdfFolderId);
folder.createFile(blob);
}
