Print range from Google Sheet

[David A Brandt Klug]

Hi Guys,

Trying to create a script to assign to a "button" that prints a fixed range (A2:CB60) in Google Sheet.

I found the script, but modified the range which doesn't seem to work. The rest of the script seems to work when running "PrintSelectedRange". A window opens but showing the whole sheet.

Anyone who knows why the range is not taken into account?

var PRINT_OPTIONS = {

  'size': 7,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B

  'fzr': false,            // repeat row headers

  'portrait': true,        // false=landscape

  'fitw': true,            // fit window or actual size

  'gridlines': false,      // show gridlines

  'printtitle': false,

  'sheetnames': false,

  'pagenum': 'UNDEFINED',  // CENTER = show page numbers / UNDEFINED = do not show

  'attachment': false

}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

function onOpen(e) {

  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print selected range', 'printSelectedRange').addToUi();

}

function printSelectedRange() {

  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var range = sheet.getActiveRange();

  var gid = sheet.getSheetId();

  var printRange = objectToQueryString({

    'a2': range.getColumn() - 1,

    'an2': range.getRow() - 1,

    'a60': range.getColumn() + range.getWidth() - 1,

    'an60': range.getRow() + range.getHeight() - 1

  });

  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;

  var htmlTemplate = HtmlService.createTemplateFromFile('js');

  htmlTemplate.url = url;

  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');

}

function objectToQueryString(obj) {

  return Object.keys(obj).map(function(key) {

    return Utilities.formatString('&%s=%s', key, obj[key]);

  }).join('');

}

[David A Brandt Klug]

Please guys... I'm totally lost here :/

Best regards

David

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6da00fe1-ab9d-4a76-b52e-d12c07de1d95n%40googlegroups.com.

[Clark Lind]

The URL is different and not the normal ss url. 

Try adding: 
var ssID =  ss.getId();  //just the id  as the key

Then build the url with this info:

var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="

 + ssID +  "&gid="+ sheetgid + "&portrait=true&fitw=true&exportFormat=pdf"; //all your options, etc. 

note: you don't have to include "false" values; by default, they are false. So your print options could be trimmed to:

var PRINT_OPTIONS = {

  'size': 7,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B

  'portrait': true,        // false=landscape

  'fitw': true,            // fit window or actual size

}

To ensure it works, simply manually create the url (https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=SPREADSHEETID&gid=SHEETID&size=7&portrait=true&fitw=true&exportFormat=pdf) 
and paste it into the address bar and see if the result is what you want. (replace highlighted text with appropriate values from your Spreadsheet).

[Clark Lind]

Oh, and include the printrange...  I forgot to include that in the example.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/Tp2v09GIIiI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7dbdd413-18f6-4135-acd2-94803cdb983fn%40googlegroups.com.

[David A Brandt Klug]

Hi Clark,

Thank you so much for your help, it works almost like it should :D
Only problem is the print range, somehow it's not taken into account even when I add it to the URL.

Is the range format correct?:

".../export?size=7&fzr=false&portrait=true&fitw=false&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&a2=7&an2=14&a60=12&an60=15&gid=477679320&portrait=true&fitw=true&exportFormat=pdf"

Best regards

David

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHgJr-1rJC0%2B%2BBLFCGNoF%2B%2BiUg%2BDpHticsKGuMYfRcLocj3C5Q%40mail.gmail.com.

[David A Brandt Klug]

Okay,

It all comes down to the print range - somehow I cannot make it skip the first row?

  var printRange = objectToQueryString({

    'a2': range.getColumn(),

    'an2': range.getRow(),

    'a60': range.getColumn() + range.getWidth(),

    'an60': range.getRow() + range.getHeight()

  });

Med venlig hilsen / Best regards
				David A Brandt Klug Partner | Adm. Direktør | Arkitekt M: 50 83 00 00  |  E: d...@hansenhuse.dk
				H.C. Ørsteds Vej 18, 6000 Kolding T: 75 55 16 57  |  www.hansenhuse.dk

Informationen i denne e-mail er fortrolig og kan være lovmæssigt/ophavsretsligt beskyttet. Hvis du ikke er den tiltænkte modtager, er det forbudt at bruge og videregive informationen i denne e-mail. Evt. priser er angivet i danske kroner (DKK) inkl. 25% moms, medmindre andet er specifikt angivet.  Der tages forbehold for mails afsendt i ufærdig tilstand eller med fejl.

[Clark Lind]

Here is the format to use:

r1=Start Row number - 1 row 1 would be 0 , row 15 would be 14

c1=Start Column number - 1 column 1 would be 0, column 8 would be 7

r2=End Row number

c2=End Column number

So in the printrange, try changing your variable names to match r1, c1, r2, c2:

var printRange = objectToQueryString({

    'r1': range.getRow() - 1,

    'c1`': range.getColumn() - 1,

    'r2': range.getRow() + range.getHeight() - 1,

    'c2': range.getColumn() + range.getWidth() - 1

  }); 

To be sure, try:       :   ".../export?size=7&fzr=false&portrait=true&fitw=false&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&r1=14&c1=7&r2=15&c2=12&gid=477679320&portrait=true&fitw=true&exportFormat=pdf"

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAMJM9FTS7J8nXzy%3DpVsNzNNx1F413s-JeCNEsWaxQz449mx4rg%40mail.gmail.com.

[Maayke Cruijsen]

Hello Clark,

I get the point where you say I have to change the variable names to match r1, c1, r2 and c2.

But where or how should I do this?

As I'm not completely familiar with coding etc. but more of a copycat, I copy paste a lot of stuff together and try to get it to work ;)

Here's my situation. In my case the data is printed on two pages, but I want it to print just on one page. I think it doesn't set the print range correctly and therefore stretches the data out when it doesn't fit the normal page ranges.

When there's less data on the page, everything works fine.

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange('B2:I60').activate();

var PRINT_OPTIONS = {

  'size': 8,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B

  'fzr': false,            // repeat row headers

  'portrait': false,        // false=landscape

  'fitw': true,            // fit window or actual size

  'gridlines': false,      // show gridlines

  'printtitle': false,

  'sheetnames': false,

  'pagenum': 'UNDEFINED',  // CENTER = show page numbers / UNDEFINED = do not show

  'attachment': false

}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

function onOpen(e) {

  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print selected range', 'printSelectedRange').addToUi();

}

function printSelectedRange() {

  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var range = sheet.getActiveRange();

  var gid = sheet.getSheetId();

  var printRange = objectToQueryString({

    'c1': range.getColumn() - 1,

    'r1': range.getRow() - 1,

    'c2': range.getColumn() + range.getWidth() - 1,

    'r2': range.getRow() + range.getHeight() - 1

  });

  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;

  var htmlTemplate = HtmlService.createTemplateFromFile('js');

  htmlTemplate.url = url;

  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');

}

function objectToQueryString(obj) {

  return Object.keys(obj).map(function(key) {

    return Utilities.formatString('&%s=%s', key, obj[key]);

  }).join('');

}

Regards,

Sjoerd

Op maandag 25 oktober 2021 om 19:55:06 UTC+2 schreef cwl...@gmail.com:

[Clark Lind]

Hello Sjoerd, 

I assume you are using this reference. I have always wondered if FITW actually means Fit Window, and thought it might actually mean Fit Width, which would match the print options when manually printing a sheet. You could experiment and see if you get an error by changing FITW to FITP  (Fit Page). 

Ok.. after a quick google, it seems Amit has already provided an answer (as referenced by someone else here).  You can use:   '&scale=4'  
 where  

1 = Normal 100%,

2 = Fit to width

3 = Fit to height

4 = Fit to Page

I hope that helps!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/66f72063-0c63-47e8-b09e-6c505807e760n%40googlegroups.com.

[Eduardo Ramirez]

Hey Guys how you doing ?

Nice topic. I was completly lost before reding these texts.

But I have another question. If I want to print the range direct to my printer, how can I do that? 


I suppose I have to adjust this variable: 

var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;

what do you think ?

[Chéri-Lynn Roodman]

I would also like to know how I can print directly to a printer and not to PDF.

[Jonathan Butler]

What type of printer do you use?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0206074a-fdf6-4057-9a62-1bc4a51eca7dn%40googlegroups.com.

[Kevin -]

You don't have to build the range with getCol, getRow, etc. Put the range in A1 notation (range="A1:Z20",  range=ss.getActiveRange().getA1Notation(), etc.) then set the print range like this: '&range=range'