Google表格导出为PDF脚本-为什么导出隐藏行? [英] Google Sheets Export to PDF Script - Why is it exporting hidden rows?
问题描述
我有一个脚本，可以将单个Google工作表另存为给定文件夹中的pdf文档.
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.
是否知道这是由于最近对Google Sheets api进行了更改而导致的，我有什么办法可以恢复到旧功能?
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);
}
功能在6月17日至19日之间发生了变化，这可能表明api发生了变化，但我找不到任何证据表明发生了这种变化.
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.
我以前将此内容发布到Stack Exchange上的Web应用程序站点，但是由于我看到Google Apps团队建议使用Stackoverflow进行查询，因此已将其删除并重新发布到此处.
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:
您必须知道createFile()是一个异步请求-就像所有Google API调用一样.这意味着，如果文件较大，则创建可能需要更长的时间，您的unhideRow()方法可能会在文件创建之前执行.
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.
类似地，如果在隐藏所有行之前执行调用，这将导致同样的问题.解决方案在于使用SpreadsheetApp.flush();函数，该函数确保仅在上一个函数完成后才能执行该函数.在folder.createFile(response.getBlob().setName(cleanName));之前和之后插入SpreadsheetApp.flush();可以解决您的问题.
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.
这篇关于Google表格导出为PDF脚本-为什么导出隐藏行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
