Is there a way to reference an entire spreadsheet with a cell reference in Google Sheets?

Say I wanted to find out how many cells in Sheet1 were non empty. In sheet two I'd want to do something like

=COUNTA(Sheet1!<magic reference>)

I've tried some common sense things like just using a colon e.g. Sheet1!:, but that doesn't work.

Alternatively you can do something like Sheet1!A:ZZZ or Sheet1!1:1000 but obviously this misses an "infinite" number of rows or columns.

I haven't been able to find any documentation on the language of cell referencing.

  • Does A:A and 1:1 have blank cells between the first and last cells with data? – Rubén Jun 26 '15 at 16:33

One alternative is to use a custom function with the getDataRange() method.

From: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

Note: The below quoted code isn't a complete custom function. To learn about custom functions go to the reference at the end.


Returns a Range corresponding to the dimensions in which data is present. This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This represents ALL the data
 var range = sheet.getDataRange();
 var values = range.getValues();

 // This logs the spreadsheet in CSV format with a trailing comma
 for (var i = 0; i < values.length; i++) {
   var row = "";
   for (var j = 0; j < values[i].length; j++) {
     if (values[i][j]) {
       row = row + values[i][j];
     row = row + ",";


Range — a range consisting of all the data in the spreadsheet

Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help

  • I had never really looked at custom functions before. This is certainly on the right track, however, you're proposing I write a custom function for every small task I wish to do, which isn't really what I wanted. The other issue with this approach is that google sheets caches results based on the parameters passed in, since this ostensibly takes no arguments, it gets computed once and never updates. There are Simple Triggers, but those don't solve the problem. Finally, getSheets()[0] only returns the first sheet. It'd probably be better to do getActiveSheet(), but that's questionable. – Erik Jun 26 '15 at 5:42
  • @branks: No, I'm not proposing to use a custom function for every small task. What I'm proposing is to use a custom function to get the "magic reference". By the other hand the example code in the quotation isn't a complete custom function. The quotation is ther because you said that you didn't found any documentation. I will update my answer a bit later to make my approach while writing the answer clear. – Rubén Jun 26 '15 at 11:25
  • Right, that's what I tried to do, a kin to the question this is now marked as a duplicate of (which it is). However, the caching make it not update, so it doesn't really behave like a reference to the entire sheet, more like a snapshot. As far as I can tell, there are no ways to change the way google sheets caches this information. – Erik Jun 26 '15 at 17:39
  • @branks If you decide to post a new question, explain what are your needs regarding the recalculation of the "magic reference". – Rubén Jun 26 '15 at 19:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.