I have a script for saving an individual google sheet as a pdf document within a given folder.
As the contents of the sheet are dynamic and sometimes have a load of empty grid rows at the bottom, I have included a method for hiding rows without content before exporting the file.
This has worked just as required until very recently. Now the function hides the rows but the file created shows the rows as unhidden.
Any idea if this is as a result of recent change to google sheets api and is there anything I can do to revert to the old functionality?
Here is my code block that previously worked:
// API function for saving a single sheet without hiding sheets
// *******************************************************************************
function singleSheetAPIExport(){
// Get active spreadsheet URL
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Example';
var main = ss.getSheetByName(sheetName)
var folderID = '1wsxxxxxxblahblah' // Google Drive Folder ID
Logger.log('maxrows: ' + main.getMaxRows());
Logger.log('last row: ' + main.getLastRow());
// Base URL
var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());
/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/
var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
+ '&size=letter' // paper size legal / letter / A4
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true&source=labnol' // fit to page width, false for actual size
+ '&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();
// Hide All Empty Rows in the Print Sheet
var maxRows = main.getMaxRows();
var lastRow = main.getLastRow();
if (maxRows-lastRow != 0){
main.hideRows(lastRow+1, maxRows-lastRow);
}
// Get the response for your specific sheet that can later be converted to blob
var response = UrlFetchApp.fetch(url + url_ext + main.getSheetId(), {
headers: {
'Authorization': 'Bearer ' + token
}
});
// Save pdf version
var saveFolder = 'PDF';
var parentFolder = DriveApp.getFolderById(folderID);
var folder, folders = DriveApp.getFoldersByName(saveFolder);
if (folders.hasNext()) {
folder = folders.next();
} else {
folder = parentFolder.createFolder(saveFolder);
}
var name = main.getRange("B8").getValue();
var cleanName = name.replace(/([^a-zA-Z0-9() #%~-])/g, "-");
folder.createFile(response.getBlob().setName(cleanName));
// Unhide the rows again
var fullSheetRange = main.getRange(1,1,main.getMaxRows(), main.getMaxColumns());
main.unhideRow(fullSheetRange);
}
The functionality changed between June 17th and 19th which might suggest a change to the api but I have not been able to find any evidence of such a change.
I previously posted this to the Web Application site at Stack Exchange but have deleted and reposted here as I see the google apps team recommend Stackoverflow for queries.
I tested your code on the test table you provided and it worked as intended for me. However, if I create a significant amount of additional rows - indeed I can reproduce your bug of hidden rows being exported.
The problem in your code is that you create your file immediately after hiding the empty rows and unhide the rows immediately after creating the file:
You must know that createFile() is an asynchronous request - just like all Google API calls. This means that in case of a larger file, where the creation might take a longer time, your unhideRow() method might be executed before the creation of the file.
Analogously, if you execute the call before all the rows are hidden, this will cause you the same issue. The solution lies in using the SpreadsheetApp.flush(); function which assures that a function will only be executed after the previous function has finished. Inserting SpreadsheetApp.flush(); before and after folder.createFile(response.getBlob().setName(cleanName)); solves your problem.
There is another way to export a sheet to a PDF file, which indicates the desired cells for export. And you will not need to hide unnecessary rows.
function exportPDF(ssID,source,options,format){
var dt=new Date();
var d=encodeDate(dt.getFullYear(),dt.getMonth(),dt.getDate(),dt.getHours(),dt.getMinutes(),dt.getSeconds());
var pc=[null,null,null,null,null,null,null,null,null,0,
source,
10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
d,
null,null,
options,
format,
null,0,null,0];
var js = " \
<script> \
window.open('https://docs.google.com/spreadsheets/d/"+ssID+"/pdf?id="+ssID+"&a=true&pc="+JSON.stringify(pc)+"&gf=[]'); \
google.script.host.close(); \
</script> \
";
var html = HtmlService.createHtmlOutput(js)
.setHeight(10)
.setWidth(100);
SpreadsheetApp.getUi().showModalDialog(html, "Save To PDF");
}
function myExportPDF(){
var as = SpreadsheetApp.getActiveSpreadsheet();
var sheet = as.getActiveSheet();
exportPDF(as.getId(), // Идентификатор таблицы
[
[sheet.getSheetId().toString(), // Sheet ID
0, // vertical start border (from the first line)
160, // vertical end boundary (160th line inclusive)
0, // horizontal start border (from cell A)
8 // horizontal end boundary (cell H inclusive)
]
],
[
0, // Do not show notes
null,
1, // Show grid lines
0, // Do not show page numbers
0, // Do not show book title
0, // Do not show sheet title
0, // Do not show date
0, // Do not show time
1, // Repeat pinned rows
1, // Repeat pinned columns
1, // Page order down then up
1,
null,
null,
1, // Left Alignment
1 // Top Alignment
],
[
"A4", // A4 sheet format
1, // Page Orientation Vertical
2, // Align to height
1,
[
0.75, // Top margin 0.75 inch
0.75, // Bottom margin 0.75 inch
0.7, // Left margin 0.7 inch
0.7 // Right margin 0.7 inch
]
]
);
}
A detailed description is on my site here
https://kandiral.ru/googlescript/eksport_tablic_google_sheets_v_pdf_fajl.html
UPD: The code above is for downloading a PDF file. And here is a script to save to Google Drive
function exportPDFtoGDrive(ssID,filename,source,options,format){
var dt=new Date();
var d=encodeDate(dt.getFullYear(),dt.getMonth(),dt.getDate(),dt.getHours(),dt.getMinutes(),dt.getSeconds());
var pc=[null,null,null,null,null,null,null,null,null,0,
source,
10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
d,
null,null,
options,
format,
null,0,null,0];
var folder=null;
var parents = DriveApp.getFileById(ssID).getParents();
if (parents.hasNext())folder = parents.next();
else folder = DriveApp.getRootFolder();
var options = {
'method': 'post',
'payload': "a=true&pc="+JSON.stringify(pc)+"&gf=[]",
'headers': {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
'muteHttpExceptions': true
};
var theBlob = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/"+ssID+"/pdf?id="+ssID, options).getBlob();
folder.createFile(theBlob).setName(filename+".pdf");
}
function myExportPDFtoGDrive(){
var as = SpreadsheetApp.getActiveSpreadsheet();
var sheet = as.getActiveSheet();
var filename = as.getName()+" "+sheet.getName();
exportPDFtoGDrive(
as.getId(), // Идентификатор таблицы
filename, // Имя файла
[[sheet.getSheetId().toString(),0,160,0,8]],
[0,null,1,0,0,0,0,0,1,1,1,1,null,null,1,1],
["A4",1,2,1,[0.75,0.75,0.7,0.7]]
);
}
