CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user11802360
user11802360

Reputation:

Strange return range result in Google Sheets / App Scripts

I have simple sheet with data and code:

enter image description here

function test() {
var SpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var Sheet = SpreadSheet.getSheets()[0];
var frozenRows = Sheet.getFrozenRows(); 2.0
var lastRow = Sheet.getLastRow(); // 3.0
var lastColumn = Sheet.getLastColumn(); // 3.0
var headerRange = Sheet.getRange(1, 1, frozenRows, 1); // A1:A2
var dataRange = Sheet.getRange(1 + frozenRows, 1, lastRow, lastColumn); // A3:C5
var Range = Sheet.getDataRange(); // A1:C3

I don't understand why I get return value A3:C5 and not A3:C3 from:

var dataRange = Sheet.getRange(1 + frozenRows, 1, lastRow, lastColumn);

Logger.log(dataRange.getA1Notation());

Thank you.

Upvotes: 1

Views: 67

Answers (1)

ziganotschka
ziganotschka

Reputation: 26806

The getRange() method has the syntax getRange(row, column, numRows, numColumns)

Thus, getRange(3, 1, 3, 3) will result in a range that

  • starts in column 1 row 3 (A3)
  • ist 3 columns wide
  • and 3 rows long
  • So: A3:C:5
  • To retrieve the range A3:C3 instead you need to query: getRange(3, 1, 1, 3)
  • In other words:
var startRow = (1 + frozenRows);
var rowLength = (lastRow - startRow + 1);
var startColumn = 1;
var columnLength = (lastColumn - startColumn + 1);
var dataRange = Sheet.getRange(startRow, startColumn, rowLength, columnLength);

Upvotes: 1

Related Questions