Getting 2 variables in a For loop-CodePudding

I am trying to get data from multiple google sheets that have different number of rows and put it into a single array. I was trying to use the built in getLastRow method on a for that was already getting each of the sheets. is there a way to look at the last row of the sheet that the for is working on?

My code

    function getData(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheets();
const data = [];
for (i = 0; i<ws.length; i  ){
  data.push(ws[i].getRange(1,1,ws[i].getLastRow()).getValues());
}

Logger.log(data)

}

When i run this code i get the following error

Exception: The number of rows in the range must be at least 1.

I have tried to put the last row in its own Array which it does however the getRange won't recognise it as a number.

function getData(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheets();
const data = [];
const lr = [];
for (i = 0; i<ws.length; i  ){
  lr.push(ws[i].getLastRow());
  data.push(ws[i].getRange(1,1,lr[i]).getValues());
}

Logger.log(data)

}

If i select a specific element in the array the code will run but doesn't work as it will only return rows to that value.

function getData(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheets();
const data = [];
const lr = [];
for (i = 0; i<ws.length; i  ){
  lr.push(ws[i].getLastRow());
  data.push(ws[i].getRange(1,1,lr[0]).getValues());
}

Logger.log(data)

}

CodePudding user response：

Issue:

Some of your sheets are totally empty and therefore ws[i].getLastRow() returns 0.
But you can't pass 0 in any of the arguments of the getRange(row, column, numRows) function and hence you are getting this error.

Solution:

Check if there is at least one row with content in the sheet:

function getData(){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ws = ss.getSheets();
    const data = [];
    for (i = 0; i<ws.length; i  ){
      lrow = ws[i].getLastRow();
      //check if there is at least one row with content in the sheet
      if(lrow>0){
        data.push(ws[i].getRange(1,1,lrow).getValues());
      }
    }
  Logger.log(data);
}

CodePudding user response：

I believe it's because of some of your sheets have no data. You can skip these sheets:

function getData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheets();
  const data = [];
  const lr = [];
  for (i = 0; i < ws.length; i  ) {
    lr.push(ws[i].getLastRow());
    if (lr[i] > 0) {
      data.push(ws[i].getRange(1, 1, lr[i]).getValues());}
    else {
      data.push([]); // empty sheet
    }
  }

  Logger.log(data)
}