Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The best workaround I have found so far is:


but ideally I would like to be able to simply write, e.g.:


So, does anyone know: is there a syntax for specifying a whole sheet as a range?

share|improve this question

migrated from superuser.com May 12 '13 at 17:05

This question came from our site for computer enthusiasts and power users.

up vote 7 down vote accepted

I've created a small Google Apps Script (GAS) snippet, to do the work for you.


function sheetRange(targetName,int) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var asName = ss.getActiveSheet().getSheetName();
  var tgSheet = ss.getSheetByName(targetName);
  var output;

  if(targetName == asName) {
    output = "Error: target sheet is active sheet !!";
  } else {
    switch(int) {
      case 1: 
        output = tgSheet.getDataRange().getValues();
      case 2:
        output = tgSheet.getSheetValues(1, 1, tgSheet.getMaxRows(),  
        output = "Choose int to be 1 or 2 !!";
  return output;

In the spreadsheet menu, select Tools>Script editor, and add the code. Make sure to press the bug button:
enter image description here



Use the int option as explained under remarks.


I've created an example file you you: Sheet as Range


In this case, there are two ways to determine a range in a spreadsheet via GAS:

  1. int=1; Via the getDataRange method. This will retrieve a range, in which the last column is the one that has data in it. The same accounts for the number of rows. This is usually the most straightforward route. See second sheet in example file.
  2. int=2; Via the getSheetValues method. This will retrieve a "WYSIWYG" range. See third sheet in example file.
  3. Refresh rates of these types of custom function aren't immediate, so please be patient. It may take several hours for data to be refreshed.


share|improve this answer

I don't know a specific one, but I think you can use some formulae if you don't know the number of rows/columns:


Here, Sheet is your sheet name, and @ is an arbitrary string. If that sheet you're selecting has @ in one cell, it won't work. You can replace it by another character if ever the sheet contains such a cell.

The COUNITF here counts the number of rows in column A:A not containing @, which should be all if there are no cells containing it, and INDIRECT transforms Sheet!1:### (where ### is the number of rows) into an actual selected range.

share|improve this answer
I appreciate the effort, but as far as I can tell, your suggestion yields no advantages over the workaround I mentioned in my question, but has the disadvantages of being more verbose and requiring the user to input a character that won't ever be present in the sheet. – sampablokuper May 28 '13 at 16:10
@sampablokuper About it being verbose, I don't think I can find a solution for that, as for the second part, I overlooked something which I have now added to my answer. Also, as far as I can see, the advantage it has over your current workaround is that you don't need to know how many rows or columns the sheet has. But yes, that's as far as it gets :( Perhaps one can make up a function from a VBA script. – Jerry May 28 '13 at 16:54
Thanks, but my current workaround does not require one to know how many rows the sheet has, nor how many columns it has as long as it has <676 columns. – sampablokuper May 29 '13 at 19:56

You can use A:Z or A:AB or A:XX (with XX being the last column of your page):

Screenshot of using this to apply conditional formatting to rows based on a single cell's value

share|improve this answer
Ist dies: "Denk Mal Neu"? How does this answer the question? – Jacob Jan Tuinstra Sep 18 '15 at 10:10
Everybody here knows how to specify a range. This wasn't the question. – ᴡᴏʀᴅs Sep 18 '15 at 11:22

Google Sheets doesn't have a range syntax for whole sheet range.

The following formula will return the range address of the whole sheet named Sheet1


In order to use it as reference, put it inside of INDIRECT. The following formula will return an array of all the values in Sheet1.

share|improve this answer

Assuming you know the number of rows, you can name the entire sheet as "worksheet_name":


  1. Select the entire worksheet
  2. Click 'Data' -> 'Named and Protected Ranges'
  3. Type "worksheet_name" to name the selection & click Done.

Now every time you use "worksheet_name" in a function, it will reference the entire worksheet.

share|improve this answer
Your method will result in a "snapshot" range. Adding both columns or rows will not change the named range !! – Jacob Jan Tuinstra Aug 12 '13 at 20:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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