Google Docs PDF Export Adds Extra Blank Pages at the End

Question

I use google spreadsheet to create invoices and send them by email in PDF format, it's been working great, my spreadsheets are yearly base, so every new year I create another one, all my spreadsheet from 2021 are still working fine, but all the new ones I've created for 2022 when I try to print or export PDFs, extra blank pages are beeing added to the end of the document. The number of blank pages are dependent on the number of blank lines on the sheet, but I didn't have this problem before, before even when the sheet has 1000 lines, if the invoice has only 1 page the PDF/Print will only generate 1 page, now if the sheet has 1000 lines it generates 19 blank pages.

Anyone with same problem or a solution?

Link for example: Spreadsheet link

Try to print or download the page and you will see the blank pages generated.

function savePDF() {
    const ssh = SpreadsheetApp.getActiveSpreadsheet();
    const invoice = ssh.getSheetByName('invoice');

const request = {
    "method": "GET",
    "headers":{"Authorization": "Bearer 
    "+ScriptApp.getOAuthToken()},    
 }

 const key = ssh.getId();
 const bogus = DriveApp.getRootFolder();
 const fetch='https://docs.google.com/spreadsheets/d/'
 + key
 +'/export?format=pdf&gid=' 
 + invoice.getSheetId() 
 + 
'&size=letter&portrait=true
&printtitle=false
&pagenum=CENTER
&sheetnames=false
&gridlines=false
&top_margin=0.25
&bottom_margin=0.50&left_margin=0.25
&right_margin=0.25';

const name = "invoice.pdf";
let pdf = UrlFetchApp.fetch(fetch, request);
pdf = pdf.getBlob().setName(name);

const fold = "Folder ID goes here";
const folder = DriveApp.getFolderById(fold);
const file = folder.createFile(pdf);
}

How do you export the sheets, via script or `file -> download -> pdf -> export` option? — NightEye, Jan 07 '22 at 18:35
Via script, but I notice the same problem when printing the sheet, blank pages are beeing generated at the end. I remember having this same problem about 5 years ago, but it was fixed, now is back. — Leo, Jan 07 '22 at 19:25
Answer below is the quickest way to fix the issue. Or you could always hide the blank rows first before printing. — NightEye, Jan 08 '22 at 12:07

NightEye · Accepted Answer · 2022-01-07T20:57:03.970

1

The issue is that it prints the whole sheet by default. So it includes all those blank lines, thus adding blank sheets.

What you can do as a workaround is to CTRL + A in the sheet, then export using Selected cells

Export:

As you can see, total number of pages is only 1.

Script:

I'm not sure why it just doesn't work right now, but there might have been some changes and defaulted to export the whole sheet. To bypass this issue, you will need to add the actual range to the link. See modifications below:

Modifications:

Get the A1 notation of the data range:

  const ssh = SpreadsheetApp.getActiveSpreadsheet();
  const invoice = ssh.getSheetByName('invoice');
  // add this line
  const range = invoice.getDataRange().getA1Notation();

Append the range into the link:

  const fetch = 'https://docs.google.com/spreadsheets/d/' + key
    + '/export?format=pdf&gid=' + invoice.getSheetId()
    + '&size=letter'
    + '&portrait=true'
    + '&printtitle=false'
    + '&pagenum=CENTER'
    + '&sheetnames=false'
    + '&gridlines=false'
    + '&top_margin=0.25'
    + '&bottom_margin=0.50'
    + '&left_margin=0.25'
    + '&right_margin=0.25'
    // add range to link
    + '&range=' + range;

Output:

edited Jan 07 '22 at 20:57

answered Jan 07 '22 at 19:45

NightEye

10,634
2
5
24

I have added the code I'm using, but it's not a problem with the code, I've been using this for 5 years and it always worked. My spreadsheets from 2021 are still working fine with the same script, but if I duplicated them, this problem occurs, so I believe it's an erros on google's side, not on my script. Maybe some update. – Leo Jan 07 '22 at 20:30
I remember having this same problem back in 2016, and it was fixed, but before the fix, I had to change the script to get the last row with data when exporting, but now I have hundreds of spreadsheets with the same script, making it very hard to fix this way – Leo Jan 07 '22 at 20:45
I have revised my answer and included a minimal change to your script. There might have been some changes that forces the sheet to be exported to include the whole sheet so the solution above will help you bypass that issue. – NightEye Jan 07 '22 at 20:58
@Leo, I recommend that if you use the same script with multiple sheets, do it via library. There is a question posted here that has multiple solutions to it. See [this link](https://stackoverflow.com/q/46575644/17842569) – NightEye Jan 07 '22 at 21:03
@Leo [When someone answers](https://stackoverflow.com/help/someone-answers) – NightEye Jan 07 '22 at 23:14
1

Thanks @Octavia Sima, your answer fixed the problem, I also started to using a library as you recomended and it saved me a lot of work. – Leo Jan 10 '22 at 19:28
You are welcome @Leo, that would save you a lot of time from now on when another issue will come up. Good luck – NightEye Jan 10 '22 at 19:29

Google Docs PDF Export Adds Extra Blank Pages at the End

1 Answers1

Export:

Script:

Modifications:

Output: