Specifying range until last column in Google AppScript

Question

I have done a coding a Google AppScript but somehow it is not working. My coding is to specify until the the last column. I have attached the coding here. Can anyone help me with it?

  var lastRow = displaySheet.getLastColumn();
  var paidMonthValues = displaySheet.getRange("G":lastRow).getValues();

Answer 1

I believe your goal as follows.

• From getRange("G":lastRow), you want to retrieve the values from the column "G" to the last column for all rows of data range.

In this case, how about the following modification?

Pattern 1:

In this pattern, the method of getRange(row, column, numRows, numColumns) is used.

From:

var paidMonthValues = displaySheet.getRange("G":lastRow).getValues();

To:

var paidMonthValues = displaySheet.getRange(1, 7, displaySheet.getLastRow(), displaySheet.getLastColumn() - 6).getValues();

Pattern 2:

In this pattern, A1Notation is used.

Sample script:

// Ref: https://stackoverflow.com/a/21231012
const columnToLetter = column => {
  let temp,
    letter = "";
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
};

var displaySheet = SpreadsheetApp.getActiveSheet();
var lastColumn = displaySheet.getLastColumn();
var paidMonthValues = displaySheet.getRange("G:" +  columnToLetter(lastColumn)).getValues();

• When you want to retrieve the value of data range, please modify the last line as follows.

    var paidMonthValues = displaySheet.getRange(`G1:${columnToLetter(lastColumn)}${displaySheet.getLastRow()}`).getValues();
  

References:

• getRange(row, column, numRows, numColumns)
• getRange(a1Notation)