49

The best workaround I have found so far is:

worksheet_name!$A$1:$YY

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

worksheet_name!

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

Improve this question
0

13 Answers 13

Reset to default
14

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

Code

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();
        break;
      case 2:
        output = tgSheet.getSheetValues(1, 1, tgSheet.getMaxRows(),  
          tgSheet.getMaxColumns());
        break;
      default:
        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

Usage

=sheetRange("sheetName",int)

Use the int option as explained under remarks.

Example

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

Remarks

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.

References

Improve this answer
2
  • Why would it take several hours for this function to show refreshed data? I thought the whole point of using a function like this would be to always have the accurate range?
    – Jason Parker
    Jan 17, 2019 at 20:08
  • 1
    The solution using getSheetValues() is 20% faster than using getDataRange() with getValues(). Tested with sheet containing 38k cells.
    – Mark Witczak
    Mar 20, 2019 at 15:23
26

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

Improve this answer
4
  • 1
    Ist dies: "Denk Mal Neu"? How does this answer the question?
    – Jacob Jan Tuinstra
    Sep 18, 2015 at 10:10
  • 1
    i like this answer, all rows!
    – Dan D.
    Jan 24, 2020 at 22:50
  • 1
    requesting more cells than are available seems to work fine - at least for what I was trying to do, which was use importrange. In other words, as long as you request more columns than you have you'll get everything. Not sure if this is a problem for other functions .... Something like Maksym's answer is what I would go with, something like worksheet_name!A1:ZZZ, which should generally cover all the columns (although maybe in some cases more Z's are needed?)
    – Jimbo
    Jun 18, 2020 at 1:54
  • 1
    Jimbo, when I tried to put ZZ in (i.e., something well beyond the number of columns I could expect in the worksheet), I got an invalid range error. How did you manage to do it?
    – Vincent
    Oct 3, 2021 at 20:54
8

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

="Sheet1!"&ADDRESS(1,1,,TRUE)&":"&ADDRESS(ROWS(Sheet1!A:A),COLUMNS(Sheet1!1:1),,TRUE)

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.

=ArrayFormula(
  INDIRECT(
    "Sheet1!"
    &ADDRESS(1,1,,TRUE)
    &":"
    &ADDRESS(ROWS(Sheet1!A:A),COLUMNS(Sheet1!1:1),,TRUE)
    ,
    TRUE
  )
)
Improve this answer
2
  • How would you add this to a named range? The field where you input the range doesn't appear to accept this formula.
    – Vincent
    Oct 3, 2021 at 20:56
  • @Vincent Please post a new question.
    – Rubén ♦
    Oct 3, 2021 at 22:04
6

The best answers have already been given for those who are operating within Apps Script, but if anyone out there is working within Google Sheets proper, then here's an approach that may fit your needs:

=INDIRECT("worksheet_name!1:" & ROWS(worksheet_name!A:A))

Explanation:

  • You're essentially specifying the range via a string that's concatenated with the number of rows in worksheet_name!

Some advantages of this approach:

  • It's 'dynamic'... if you add rows to the bottom, the range will adjust accordingly
  • Lightweight - you can slap this into an importrange/query function

Disadvantages:

  • Haven't tried it in all cases so might only work for specific use cases
  • I personally have a preference for making things dynamic / clean so a custom function of sorts would be a good middle ground between appscript and this lightweight approach
Improve this answer
0
4

This is what I use:

=OFFSET(Sheet1!$A$1, 0, 0, Rows(Sheet1!$A:$A), Columns(Sheet1!$1:$1))

An advantage of using OFFSET over INDIRECT is that the reference will update if Sheet1 is renamed after this formula is entered.

Improve this answer
1
  • 4
    This is the obvious correct answer in my opinion. References as concatenated strings are a receipt for disaster.
    – mynyml
    Jan 16, 2022 at 12:39
2

This works for me:

var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var rg = sh.getName() +"!"+ sh.getDataRange().getA1Notation();

Used in a bound script, combine the sheetname with a "!" and then get the A1 notation of the data range on the chosen sheet

Improve this answer
2

To specify the entire sheet as a range:

worksheet_name!A1:ZZ

you can try it here: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear

or if you use Java (to clear everything in Sheet1):

Sheets service = getSheetsService(credential);
service.spreadsheets().values().clear(REPORT_WARNINGS_LATAM_FILEID, "Sheet1!A1:ZZ", new ClearValuesRequest()).execute();

or if you want just to use it in a formula (for example: SUM) on another sheet (for example: Sheet2) you can use following reference:

=SUM(Sheet1!A1:ZZ)

^ this will sum all existing cells on Sheet1 and put the value in a cell on Sheet2, you can find the example here: https://docs.google.com/spreadsheets/d/1rP3YCl3ErlYjlYGT_Q-xFvkVr7yKw6WQfHklbohL1NM/edit?usp=sharing. In this example we have 3 columns and 5 rows on Sheet1, so this formula =SUM(Sheet1!A1:ZZ) selects all of them. You can add rows or columns on Sheet1 and this solution will still select all cells.

Improve this answer
1
  • 2
    I didn't down-vote, but your answer is very similar to what was already mentioned in the question and it will stop working correctly if you have more than 676 columns one day.
    – RyanCu
    Sep 21, 2021 at 12:43
1

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:

indirect("Sheet!1:"&countif(Sheet!A:A,"<>@")+countif(Sheet!A:A,"=@"))

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.

Improve this answer
3
  • 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.
    – user6757
    May 28, 2013 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, 2013 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.
    – user6757
    May 29, 2013 at 19:56
1

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

STEPS:

  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.

Improve this answer
1
  • 7
    Your method will result in a "snapshot" range. Adding both columns or rows will not change the named range !!
    – Jacob Jan Tuinstra
    Aug 12, 2013 at 20:44
0

I agree, I think @RyanCu's answer is cleanest. With the the new Named Functions feature in Sheets you can make his answer even easier to use. I added a named function SHEETRANGE with an argument sheetname and a formula definition of:

=OFFSET(indirect(sheetname&"!$A$1"), 0, 0, Rows(indirect(sheetname&"!$A:$A")), Columns(indirect(sheetname&"!$1:$1")))

You then invoke it as SHEETRANGE("sheetname") and it returns an array consisting of the entire range of the specified sheet.

My experience is that the use of INDIRECT() in the Named Function is not a problem; it seems to be treated as volatile if the sheetname parameter changes. For example, when called with a cell reference as a parameter e.g., SHEETRANGE(C3) it dynamically recalcs.

Improve this answer
0 
=CELL("address",Sheet2!A:A)&":"&ROWS(Sheet2!A:A)

references Sheet2 as a whole.

It returns a string like Sheet2!$A$1:1000 .

This means address of Sheet2 all rows (1000 in this case)

(change Sheet2 to your sheet's name in two places in formula)

It updates even if the sheet's name is changed.

Needs to be used with INDIRECT():

=INDIRECT(CELL("address",Sheet2!A:A)&":"&ROWS(Sheet2!A:A))
Improve this answer
0
-2

I was able to select the entire sheet by naming the range as the sheet without the exclamation mark.

I had a tab named data. Instead of using data! or data!A1:ZZ.

Improve this answer
1
  • 1
    When we assign a name to a named range, a range reference should be specified. What reference range do you used?
    – Rubén ♦
    Jan 4, 2018 at 3:31
-2

Use range = "#{sheet_name}!A:A"

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.