How to measure your Google Sheets size with Apps Script?

Google Sheets has a limit of 10 million cells, but it’s hard to know how much of this space you’ve used. Sheet Sizer will calculate the size of your Sheet and compare it to the 10 million cell limit in Google Sheets.

Add code.gs


/**
 * Add custom menu to sheet
 */
/**
 * Global Variable
 */
const MAX_SHEET_CELLS = 10000000;

function onOpen() {

    SpreadsheetApp.getUi()
        .createMenu('Sheet Sizer')
        .addItem('Open Sheet Sizer', 'showSidebar')
        .addToUi();
}

/**
 * function to show sidebar
 */
function showSidebar() {

    // create sidebar with HTML Service
    const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Sheet Sizer');

    // add sidebar to spreadsheet UI
    SpreadsheetApp.getUi().showSidebar(html);
}

/**
 * Get size data for a given sheet url
 */
function auditSheet(sheet) {

    // get spreadsheet object
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    // get sheet name
    const name = sheet.getName();

    // get current sheet dimensions
    const maxRows = sheet.getMaxRows();
    const maxCols = sheet.getMaxColumns();
    const totalCells = maxRows * maxCols;

    // put variables into object
    const sheetSize = {
        name: name,
        rows: maxRows,
        cols: maxCols,
        total: totalCells
    }

    // return object to function that called it
    return sheetSize;

}

/**
 * Audits all Sheets and passes full data back to sidebar
 */
function auditAllSheets() {

    // get spreadsheet object
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheets = ss.getSheets();

    // declare variables
    let output = '';
    let grandTotal = 0;

    // loop over sheets and get data for each
    sheets.forEach(sheet => {

        // get sheet results for the sheet
        const results = auditSheet(sheet);

        // create output string from results
        output = output + '<br><hr><br>Sheet: ' + results.name +
            '<br>Row count: ' + results.rows +
            '<br>Column count: ' + results.cols +
            '<br>Total cells: ' + results.total + '<br>';

        // add results to grand total
        grandTotal = grandTotal + results.total;

    });

    // add grand total calculation to the output string
    output = output + '<br><hr><br>' +
        'You have used ' + ((grandTotal / MAX_SHEET_CELLS) * 100).toFixed(2) + '% of your 10 million cell limit.';

    // pass results back to sidebar
    return output;

}

Add Sidebar.html


< !DOCTYPE html>
    <html>

    <head>
        <base target="_top">
        < !-- Add CSS code to format the sidebar from google stylesheet -->
            <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
            <style>
                body {
                    padding: 20px;
                }
            </style>
    </head>

    <body>
        <div class="block"><input type="button" class="create" value="Get Sheet Size" onclick="getSheetSize()" /></div>
        <div class="block"><input type="button" value="Close" onclick="google.script.host.close()" /></div>
        <div class="block">
            <div id="results"></div>
        </div><input type="button" value="Close" onclick="google.script.host.close()" /><input type="button" value="Get Sheet Size" onclick="getSheetSize()" />
        <div id="results"></div>
        <script>
            function getSheetSize() {

                google.script.run.withSuccessHandler(displayResults).auditAllSheets();


            }

            function displayResults(results) {
                // display results in sidebar
                document.getElementById("results").innerHTML = results;
            }
        </script>
    </body>

    </html>

Source: Ben Collins