0

I am trying to create a button that, when clicked, prompts the user to print a specific sheet of information.

The script successfully saves the document as a PDF. It also manages to open the document in the print GUI to send it to the printer. However, the page that needs to print is blank, only showing the URL at the bottom, and excludes all the cells and formatting, etc. If I copy the URL from the bottom of the page and paste it into my internet search engine, the correct sheet opens up and displays properly with all the cells and formatting... Here is an example of what its doing

function exportRangeAsPdf(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Job Specifications");
  const range = sheet.getRange("A1:E27");

  const url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?         format=pdf" + "&gid=" + sheet.getSheetId() + "&range=" + range.getA1Notation();

  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: "Bearer " + token
    },
    muteHttpExceptions: true
    });

  const blob = response.getBlob().setName("Printed_Range.pdf");
  DriveApp.createFile(blob);

  const htmlOutput = HtmlService.createHtmlOutput('<script>window.print();      google.script.host.close();</script>')
      .setWidth(10)
      .setHeight(10);
       SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Print');

    }

All I need is for the contents of the sheet found at the correct URL to actually display on the page, so that it can be printed on a physical printer.

Improve this question
3
  • 2
    This question is similar to: Any way to use Google Apps Script to print a Google Sheets worksheet?. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. Commented Aug 7 at 12:13
  • I did have a look at this question. My issue is not so much getting Apps Script to print the document. In my code above, the document is opening to print correctly and saving to a pdf correctly. My issue is that the print document is not displaying properly. Perhaps I have a simple error in the .createHtmlOutput. Commented Aug 7 at 12:45
  • 1
    You are using UrlFetchApp to print the range as PDF and opening a modal dialog to execute window.print(). The modal is blank, though, and only displays the URL in the footer. if you can correct it, it might work Commented Aug 7 at 12:53

1 Answer 1

Reset to default
0

I saw the problem you're having with printing a range from Google Sheets with Apps Script, and here's what's wrong — and how to make it right.

You are employing UrlFetchApp to print the range as PDF and opening a modal dialog to execute window.print(). The modal is blank, though, and only displays the URL in the footer.

window.print() executes on an empty HTML page — it does not contain the PDF content.

So, nothing prints.

Instead of attempting to print within the modal, open the exported PDF URL in a new browser tab, where it can completely load and get printed by the user.

Here's the right script for you:

function exportRangeAsPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Job Specifications");
  const range = sheet.getRange("A1:E27");

  const url = "https://docs.google.com/spreadsheets/d/" + ss.getId()
    + "/export?format=pdf"
    + "&gid=" + sheet.getSheetId()
    + "&range=" + range.getA1Notation()
    + "&portrait=true&fitw=true&sheetnames=false&printtitle=false&pagenum=UNDEFINED"
    + "&gridlines=false&fzr=false";

  const html = HtmlService.createHtmlOutput(
    `<html><body>
      <script>
        window.open(${JSON.stringify(url)}, '_blank'); 
        google.script.host.close();  
      </script>
    </body></html>`
  );

  SpreadsheetApp.getUi().showModalDialog(html, "Print Preview");
}
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Forgive my ignorance, as I am trying to put this together for work, with very limited knowledge of script. The PDF briefly opens in a new tab, before it closes the tab and downloads the pdf. What can I do to prevent the pdf from automatically downloading? I would rather it stay open in the browser tab so that it can be printed from there.
You can go to chrome://settings/content/pdfDocuments and turn OFF Download PDFs instead of opening them in Chrome. you cannot force all browsers to open PDFs in view mode instead of downloading.
Ahh okay. My chrome is already set to "Open PDF's in Chrome" rather than "Download PDF". So I thought the issue might be code driven in this particular case. Strange that it downloads anyway, given the current default setting.
Instead of opening the export PDF link directly (which downloads), we wrap it using Google Docs : const view = "docs.google.com/viewer?url=" + encodeURIComponent(PDF_URL); Then open it like this: window.open(view, "_blank"); it forces the PDF to open in the browser viewer, so the user can print it
Could I please bother you to help me with what that code will look like? I have been struggling to get it to execute.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.