Apps Script (Google Sheets) to count Rows and Cells in Workbook

Google Sheets Show & Tell
1

This is an apps script that creates a new sheet in your Tiller file.

Column A: Name of the tab / sheet
Column B: count of rows in the sheet, including blanks
Column C: count of cells in the sheet (rows x columns) including blanks.

It’s handy if you get an error saying that the Update to your Tiller sheet failed because it would have exceeded the max number of cells allowed in a Google sheet (10 million).

This happened to me this morning. The script showed that the combined number of cells in my Tiller file was about 1.3 million so well under the 10 million limit. Switching to a copy of the Tiller file fixed the problem but this apps script also showed me which of my 43 tabs were unnecessarily large.

If your Tiller file is large, it will take a minute or two to run. It adds a new sheet named, “Sheet Details” with row count and cell count for each sheet in your workbook.

This script worked for me with no errors but play it safe by using it on a copy of your Tiller file.

function listSheetDetails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var data = [];

  // Loop through all sheets and push their name, row count, and cell count into the data array
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var name = sheet.getName();
    var rows = sheet.getLastRow();
    var columns = sheet.getLastColumn();
    var cells = rows * columns; // Calculate the total number of cells
    data.push([name, rows, cells]);
  }

  // Create a new sheet for the output
  var outputSheet = ss.getSheetByName("Sheet Details");
  if (!outputSheet) {
    outputSheet = ss.insertSheet("Sheet Details");
  } else {
    outputSheet.clear(); // Clear the sheet if it already exists
  }

  // Set headers
  outputSheet.appendRow(["Sheet Name", "Number of Rows", "Number of Cells"]);

  // Write the data to the new sheet
  outputSheet.getRange(2, 1, data.length, 3).setValues(data);
}

// Run this function
listSheetDetails();
1 Like
2

worked great for me, thank you!
identified an old hidden sheet copy that had a lot of rows
and that “Categories by Month” generates a huge number of rows in its sheet too (675k!)

3

I’m not sure what I am doing wrong here, but I am getting the following error when I try to run this script:

TypeError: Cannot read properties of null (reading ‘getSheets’)

Any advice?

4

That error would be generated if the script wasn’t attached to a spreadsheet.

I just ran the script again on my Tiller workbook and it worked without throwing the error. The code is shown below.

Suggestion: From a copy of your Tiller spreadsheet or any spreadsheet with several tabs, use Extensions > Apps Script to create a new apps script file with the .gs type (not html) and replace the default code with the script below.

Save. It should save without errors. Then Run. It will probably ask you to grant permissions. After you grant permissions, Run it again and – cross your fingers. It takes 10 - 30 seconds for the script to complete. When it’s finished, you should see a new tab in your spreadsheet named, ‘Sheet Row Counts’.

Hope this helps.

// 2024-02-08 Outputs count of rows in each sheet to a new sheet named 'Sheet Row Counts'

function listSheetRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var data = [];

  // Loop through all sheets and push their name and row count into the data array
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var name = sheet.getName();
    var rows = sheet.getLastRow();
    data.push([name, rows]);
  }

  // Create a new sheet for the output
  var outputSheet = ss.getSheetByName("Sheet Row Counts");
  if (!outputSheet) {
    outputSheet = ss.insertSheet("Sheet Row Counts");
  } else {
    outputSheet.clear(); // Clear the sheet if it already exists
  }

  // Set headers
  outputSheet.appendRow(["Sheet Name", "Number of Rows"]);

  // Write the data to the new sheet
  outputSheet.getRange(2, 1, data.length, 2).setValues(data);
}

// Run this function
listSheetRows();
1 Like