Fixed range width with dynamic height operated by last row of specific column

Question

function PrintWork() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A1:K"+lrow);

  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

I have a script to print into PDF and I want to set the printing range to the last row of the range but says"ReferenceError: lrow is not defined" What I would like to achieve , that the width to be set as given but the height to adjusted automatically by the last row of the specific column.

Answer 1

Try this:

function PrintWork() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A1:K" + sheet.getLastRow());

You could try it this way:

  var range = sheet.getRange("A1:K" + getColumnHeight(1,sheet,ss);




function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()) { rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}