Google Sheets Export to PDF Script - Why is it exporting hidden rows?

Question

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.

Answer 1

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.