1

I have the below code which exports a sheet to a pdf from a Google Sheet, however it seems to add multiple pages of empty rows at the bottom. You can see the commented out code at the top where I attempted to delete empty rows before exporting, but that didn't work either. Any ideas..?

function exportFl() {

var sheetName = "FL" 
//var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
//var allsheets = ss.getSheets();
//for (var s in allsheets){
//var sheet=allsheets[s]
//var maxRows = ss.getMaxRows(); 
//var lastRow = ss.getLastRow();
//if (maxRows-lastRow != 0){
//ss.deleteRows(lastRow+1, maxRows-lastRow);}

var value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange('E7').getValue();
var folderID = "1x0tBqOP07e2XIzXHgvYqAaBiu7Zd7DfV"; // Folder id to save in a folder.

var timeZone = Session.getScriptTimeZone();
date = Utilities.formatDate(new Date(), timeZone, "YYMMdd");

var pdfName = value+"_FSL Conformance_"+ date;

var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var folder = DriveApp.getFolderById(folderID);

//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}
}

var destSheet = destSpreadsheet.getSheets()[0];
//repace cell values with text (to avoid broken references) 
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(sourcevalues);

//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}
Jon Bowles
  • 119
  • 6
  • I thought that in your script, the data range is exported as the PDF data. Actually, when I tested your script, I couldn't replicate your situation of `it seems to add multiple pages of empty rows at the bottom.`. So in order to correctly understand about your current issue, can you provide the sample Spreadsheet for replicating `it seems to add multiple pages of empty rows at the bottom.`? – Tanaike May 18 '21 at 00:15
  • Sure,its here: https://docs.google.com/spreadsheets/d/1MncKFtE45b-E6FWUVmEjw3TaIMvaPBSCtg74t30B7Vc/edit?usp=sharing Thanks – Jon Bowles May 18 '21 at 01:00
  • Thank you for replying and providing the sample Spreadsheet. From your sample Spreadsheet, I could correctly replicate your situation. So, I proposed a modification point as an answer. Could you please confirm it? If that was not the direction you expect, I apologize. – Tanaike May 18 '21 at 01:33
  • Thankyou Tanaike! I used option 1 works perfectly. Cheers – Jon Bowles May 18 '21 at 02:10
  • Thank you for replying. I'm glad your issue was resolved. By your additional information, I could correctly understand your current situation. Thank you, too. – Tanaike May 18 '21 at 05:07

2 Answers2

1

How about the following modification?

Pattern 1:

In this pattern, all rows except for the data range of the exporting sheet are deleted. Please modify your script as follows.

From:

//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

To:

//save to pdf

// --- I added below script.
var r = destSheet.getDataRange();
var startRow = r.getNumRows() + 1;
var number = destSheet.getMaxRows() - startRow + 1;
if (number > 0) destSheet.deleteRows(startRow, number);
SpreadsheetApp.flush(); // This might not be required to be used.
// ---

var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

Pattern 2:

In this pattern, from this thread, the specific range of the sheet is directly exported using the query parameters. Please modify your script as follows.

From:

//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);

To:

//save to pdf

// --- I added and modified below script.
var dr = destSheet.getDataRange();
var url = `https://docs.google.com/spreadsheets/d/${destSpreadsheet.getId()}/export?format=pdf&gid=${destSheet.getSheetId()}&r1=0&c1=0&r2=${dr.getNumRows()}&c2=${dr.getNumColumns()}`;
var theBlob = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}}).getBlob();
// ---

var newFile = folder.createFile(theBlob);

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
0

Using .getMaxRows and .getMaxColumns is the issue. Use .getLastRow() and .getLastColumn() to generate your final page. .getMaxColumns/Rows pulls the total count, regardless of content, meaning it will grab empty space too.

See - https://gist.github.com/78f9867a691e549c9c70

Steve Decker
  • 1