CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jon Bowles
Jon Bowles

Reputation: 129

PDF export from Google Script includes multiple empty pages

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);
}

Upvotes: 1

Views: 284

Answers (2)

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 1

Steve Decker
Steve Decker

Reputation: 1

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

Upvotes: 0

Related Questions