Find sheet size using Google Sheets API

I believe your goal and your current situation as follows.

• You want to retrieve all values from the specific sheet in Google Spreadsheet.
• You want to retrieve the total number of cells from the specific sheet in Google Spreadsheet.
• You want to achieve this using googleapis for PHP.
• You have already been able to get values from Google Spreadsheet using Sheets API.

Modification points:

• In order to retrieve the all values from the specific sheet in Google Spreadsheet, it is not required to use the a1Notation of range. In this case, you can retrieve all values using the sheet name as the range.
• In order to retrieve the total number of cells from the specific sheet in Google Spreadsheet, I think that gridProperties of each sheet retrieving with spreadsheets.get method can be used. The object of gridProperties has the properties of rowCount and columnCount. I thought that these properties can be used for calculating the total number of cells.

When above points are reflected to your script, it becomes as follows.

Modified script 1:

In this modification, all values are retrieved from the specific sheet. Your script is modified, it becomes as follows.

$service = new Google_Service_Sheets($client);
// would really like to calculate range
$range = 'Sheet1'; // Modified
$response = $service->spreadsheets_values->get($spreadsheetId, $range);

Modified script 2:

In this modification, the total number of cells is retrieved from the specific sheet. Your script is modified, it becomes as follows. In this case, the method of spreadsheets.get is used.

$service = new Google_Service_Sheets($client);
// would really like to calculate range
$range = 'Sheet1';
$response = $service->spreadsheets->get($spreadsheetId, ["ranges" => [$range], "fields" => "sheets(properties(gridProperties(columnCount,rowCount)))"]);
$gridProperties = $response[0] -> getProperties() -> getGridProperties();
$rowCount = $gridProperties -> getRowCount();
$columnCount = $gridProperties -> getColumnCount();
$totalNumberOfCells = $rowCount * $columnCount;
print($totalNumberOfCells);

• As the value of fields, I used sheets(properties(gridProperties(columnCount,rowCount))).

• If you want to retrieve the total number of cells from all sheets in a Google Spreadsheet, you can also the following script.

    $spreadsheetId = "###";  // Please set the Spreadsheet ID.
    $response = $service->spreadsheets->get($spreadsheetId, ["fields" => "sheets(properties(gridProperties(columnCount,rowCount)))"]);
    $totalNumberOfCells = 0;
    foreach ($response as $i => $sheet) {
        $gridProperties = $sheet -> getProperties() -> getGridProperties();
        $rowCount = $gridProperties -> getRowCount();
        $columnCount = $gridProperties -> getColumnCount();
        $totalNumberOfCells += $rowCount * $columnCount;
    }
    print($totalNumberOfCells);
  

References:

• Method: spreadsheets.values.get
• Method: spreadsheets.get

If you are concerned about approaching the 5Mil cell limit, this sheet sizer should work for you. It uses some script and HTML.

const MAX_SHEET_CELLS = 5000000;

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Extras')
    .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 / 5000000) * 100).toFixed(2) + '% of your 5 million cell limit.';

  // pass results back to sidebar
  return output;

}

<!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" 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>
  <script>
    function getSheetSize() {
      //google.script.run.auditSheet();
      //google.script.run.withSuccessHandler(displayResults).auditSheet();
      google.script.run.withSuccessHandler(displayResults).auditAllSheets();

    }

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

</html>

I have this in a sample play sheet you can make a copy of if you are interested

https://docs.google.com/spreadsheets/d/1qbLOjTdzISICTKyUp_jK6gZbQCt-OwtDYYy3HNJygeE/edit#gid=1181136581