CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user15724106
user15724106

Reputation:

Specifying range until last column in Google AppScript

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

Upvotes: 2

Views: 75

Answers (1)

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 2

Related Questions