My function doesn't return full data range. What is the syntax of getRange?

user_9944216 0
Viewed 41

I'm bulding a webApp w/ Google App Scripts. In my main .gs file, I'm trying to get & return the values from a data range in my connected sheet. My function below seems to only return the values for column 1 in the range when it has 4 columns.

What am I doing wrong?

function loadBoats(){

  var ss = SpreadsheetApp.openByUrl("* myspreadSheet url*");
  var ws = ss.getSheetByName("DB1.vessels");
  var boats = ws.getRange(1,1,ws.getRange("A1:D12").getDataRegion().getLastRow(),1).getValues();
  Logger.log(boats);
  return boats;

}

Picture for logs:

enter image description here

Additionally:

I don't understand the structure of the first .getRange() method w/ 4 parameters. This is the YT link I am following along w/ - Youtube tutorial on the subject

google-apps-script google-sheets
2 Answers

The range is finally A1:A12 as specified by the outter getRange.

ws.getRange("A1:D12").getDataRegion().getLastRow()
// 12

ws.getRange(1,1,ws.getRange("A1:D12").getDataRegion().getLastRow(),1)
// getRange(1,1,12,1) -> equivalent to 'A1:A12'

You should simply call ws.getRange("A1:D12").getValues();.

Reference:

getRange(a1Notation)

user_161251390

The reason why you are getting 1 column is because you put 1 in the 4th parameter of your getRange(row, column, numRows, numColumns) function. The 4th parameter determines how many columns you want to retrieve starting from the 2nd parameter.

Here is the definition of each parameter in getRange(row, column, numRows, numColumns):

  • row Integer The starting row index of the range; row indexing starts with 1.
  • column Integer The starting column index of the range; column indexing starts with 1.
  • numRows Integer The number of rows to return.
  • numColumns Integer The number of columns to return.

Example:

getRange(1,1,12,4)

  • Row starts at 1
  • Column Starts at A
  • Add 12 rows starting 1
  • Add 4 columns starting from A

The result range in A1 notation is A1:D12

Solution:

In your code, since you already knew how many rows to retrieve (A1:D12) or 12 rows and you want 4 columns, just change your code to var boats = ws.getRange(1,1,12,4).getValues();

user_146060520
Related