Printing from a range-CodePudding

I had assistance in getting the framework of a custom print button up and running, however I can't figure out how to set it to print from a set range of cells each time (A1:C3 in this case). I've attached both of the functions that are used in my process, any ideas of why the range is not being limited to the specified ranges and prints the entire sheet instead?

Below is the separate html that is required for the print to run

<script>
  window.open('<?=url?>', '_blank', 'width=800, height=600');
  google.script.host.close();
</script>

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 printSelectedRange() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'a1': range.getColumn() - 1,
    'c1': range.getRow() - 1,
    'a3': range.getColumn()   range.getWidth() - 1,
    'c3': 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('');
}

CodePudding user response：

I believe your goal is as follows.

You always want to use the range of "A1:C3" by modifying your showing script.
You want to know the reason of why the range is not being limited to the specified ranges and prints the entire sheet instead?.

Modification points:

In your script, printRange is as follows. I thought that you might have wanted to control the export range using this script. var printRange = objectToQueryString({ 'a1': range.getColumn() - 1, 'c1': range.getRow() - 1, 'a3': range.getColumn() range.getWidth() - 1, 'c3': range.getRow() range.getHeight() - 1 });
- In this case, the keys are "a1", "c1", "a3", "c3". When Andrew Roberts's gist is used, the keys are required to be "r1", "c1", "r2", "c2". Ref
- I thought that this might be the reason of your issue of why the range is not being limited to the specified ranges and prints the entire sheet instead?.

When these points are reflected in your script, how about the following modification?

From:

var range = sheet.getActiveRange();

var gid = sheet.getSheetId();
var printRange = objectToQueryString({
  'a1': range.getColumn() - 1,
  'c1': range.getRow() - 1,
  'a3': range.getColumn()   range.getWidth() - 1,
  'c3': range.getRow()   range.getHeight() - 1
});

To:

// var range = sheet.getActiveRange();

var gid = sheet.getSheetId();
var printRange = objectToQueryString({
  'c1': 0,
  'r1': 0,
  'c2': 3,
  'r2': 3,
});

By this modification, the range of "A1:C3" is always used.

If you want to use the selected range with var range = sheet.getActiveRange(), please modify as follows.

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