Count the maximum number of rows in another google sheets

user_19099453 0
Viewed 143

I am trying to count the maximum number of rows in other google sheets. Actually, Each spreadsheet has a number of tabs and I want the maximum row number of all tabs under each spreadsheet.

I find that IMPORTRANGE function can get the data from other spreadsheet with URL, however, this function can only read the data with sheet name. To get the sheet name list, I think the function below might be useful:

function sheetnames() { 
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
return out
}

But this function only works in the sheet it is in and to write this function in every single sheet is obviously impossible, so here is my question:

Is it possible to improve the sheetnames()function to get the sheet names from other sheets? (with URL? I'm not sure) Or How can I realize it with other methods?

Thanks a lot for your patience!

google-apps-script google-sheets
3 Answers

For a Single Sheet:

function myFunction() {
  
  return SpreadsheetApp.openByUrl(`SPREADSHEET_URL`)
                       .getSheets()
                       .map(i => [i.getSheetName(), i.getLastRow()])
                       // To view results:
                       //.forEach(i => Logger.log(i))

}

Output:

[
  [Sheet1, 10],
  [Sheet2, 15],
  [Sheet3, 20]
]

For Multiple Sheets:

function myFunction() {
  
  const spreadsheetUrlList = [
    `...`,
    `...`,
    `...`
  ]

  return spreadsheetUrlList.map(i => {
    const spreadsheet = SpreadsheetApp.openByUrl(i)
    return {
      SpreadsheetName: spreadsheet.getName(),
      Sheets: [
        ...spreadsheet.getSheets()
                      .map(i => [i.getSheetName(), i.getLastRow()])
      ]
    }
  })
  // To view results:
  //.forEach(i => i.Sheets.forEach(item => Logger.log(`(${i.SpreadsheetName}) "${item[0]}": ${item[1]}`)))

}

Output:

[
  {
    SpreadsheetName: `MySpreadsheet`,
    Sheets: [
      ['Sheet1', 50],
      ['Sheet2', 40]
    ]
  },
  {
    SpreadsheetName: `MySpreadsheet2`,
    Sheets: [
      ['Sheet1', 30],
      ['Sheet2', 70]
    ]
  },
...
]

Let me know if you have any questions or would like different output formats!

user_190435340

If you have the url of a spreadsheet, this function will return the maximum number of rows of all sheets of the said spreadsheet.

function myFunction() {
  var spreadsheet = SpreadsheetApp.openByUrl("URL GOES HERE");
  var sheets = spreadsheet.getSheets();

  let currentMaximum = 0;
  for (var i = 0; i < sheets.length; i++){
    var numberOfRows = sheets[i].getMaxRows();
    
    if (numberOfRows > currentMaximum){
      currentMaximum = numberOfRows;
    }
  }

  return currentMaximum;
}

It makes use of the getSheets() function to fetch an array containing all the sheets of the spreadsheet, it then loops through all those sheets, uses the getMaxRows() function to get the number of rows in each spreadsheet and finally updates the currentMaximum variable if a new maximum of rows is found.

user_176758620

Max rows for spreadsheet

function maxrows(id=SpreadsheetApp.getActive().getId()) {
  Logger.log(SpreadsheetApp.openById(id).getSheets().map(sh => sh.getMaxRows()).sort((a,b) => b - a)[0]);
}

Execution log
12:03:34 PM Notice  Execution started
12:03:34 PM Info    4994.0
12:03:35 PM Notice  Execution completed
user_72150910
Related