Named ranges in Google Sheets

A named range is a feature in Google Sheets that lets you give a range a range a unique name. This is a very useful feature since you can use the name of range in formulas and functions instead of using its A1 notation.

For example, if you have a spreadsheet that you use for budgeting, you might have two named ranges called Expenses and Income. You can then reference these ranges in formulas and functions by using their name. So instead of =SUM(Sheet2!A3:A17), you can use =SUM(Income). Using names for ranges instead of their A1 notation makes formulas and Apps Script code easier to understand.

Prerequisites

This tutorial will assume that you are familiar with:

Why should you use named ranges in Google Sheets?

There are several benefits to giving meaningful names to ranges in your spreadsheet.

  • Make your formulas and scripts easy to read and understand

  • Giving variables a meaningful descriptive name is a best practice in coding. Doing so helps others read and understand your code.

  • Similarly, you can think of a range in a spreadsheet as a variable that references an array of data. Therefore, it is a best practice to name ranges that get used in multiple places in your spreadsheet.

  • You only need to edit range references in one place

  • If you need to make changes to a range that is used in multiple formulas and scripts, you only need to make this change in one place if you're using a named range.

  • All of the formulas and scripts will immediately use the edited range. This prevents errors that result from formulas using an outdated range reference.

How to create a named range in Google Sheets?

There are two ways to create a named range: (1) manually using the Google Sheets UI and (2) using Apps Script code.

Creating a named range using the Google Sheets UI

There are two ways to create a named range using the Google Sheets UI:

1. Select a range, right click and select Define named range. Then enter a name for this range and select Done.

2. Select Data —> Named ranges from the menu. Then create the named range from the sidebar by entering a name and selecting the range.

Creating a named range using Google Apps Script

To create a named range using Apps Script, use the setNamedRange() method of the Spreadsheet object. The method takes two arguments as input: (1) the name of the range (2) the range itself.

function createNamedRange() {
  var ss = SpreadsheetApp.getActive();
  var range = ss.getRange("Sheet1!A1:B5");
  ss.setNamedRange("Expenses", range);
}

Running the above function will create a named range called "Expenses" that is a reference to the range Sheet1!A1:B5.

A screenshot of a Google Sheets spreadsheet displaying a newly created named range called

How to use a named range in a Google Sheets formula?

Using a named range in a formula is straightforward. Just use the name instead of specifying the range's A1 notation. For example, instead of =SUM(Sheet1!A1:B5), use =SUM(Expenses) instead.

A screenshot of a Google Sheets spreadsheet that shows the user entering the formula =SUM(Expenses) in a cell. Here,

Working with named ranges in Google Sheets using Google Apps Script

Reading data from a named range using Apps Script

To read data from a named range, first reference the named range by using the getRangeByName() method of the Spreadsheet object. Then, read the values in the range by using the getValues() method.

function readNamedRange() {
  var range = SpreadsheetApp.getActive().getRangeByName("StudentGrades");
 var values = range.getValues();
 Logger.log(JSON.stringify(values));
}

Writing data to a named range using Apps Script

To write data to a named range, first reference the named range by using the getRangeByName() method of the Spreadsheet object. Then, write a two-dimensional array of values to the range by using the getValues() method. Note: the size of the array (i.e., the number of its rows and columns) must be the same as that of the named range.

The code below writes the two-dimensional array dataToBeWritten into the range named StudentGrades.

function writeToANamedRange() {
  var range = SpreadsheetApp.getActive().getRangeByName("StudentGrades");

  var dataToBeWritten = [
      ["Student", "English Grade", "Math Grade"],
      ["Erik B", "A", "C"],
      ["Lisa K", "B", "A"],
      ["Faye N", "A-", "A"],
      ["Rose A", "B", "B"],
      ["Derek P", "B-", "A-"]
  ];

 range.setValues(dataToBeWritten);
}

Using named ranges with custom functions in Google Sheets

You can use named ranges as arguments when calling a custom function from a cell in your spreadsheet. However, when you do this, the function will never know that it was passed a named range. Instead, it will directly receive the data that is contained in the named range that was passed to it as an argument.

Let us say the range Sheet8!A1:A6 contains 6 number values: 1, 2, 3, 4, 5 and 6. This range has the name SixValues.

A screenshot of a Google Sheets spreadsheet containing a named range called SixValues.

Now, let's write a custom function called STRINGIFY() that returns the JSON string representation of the argument that is passed to it. 

function STRINGIFY(value) {
  return JSON.stringify(value);
}

When you call the function STRINGIFY() with the named range SixValues as the argument, you'll see that the value returned is the string representation of the two-dimensional array of values contained in the range Sheet8!A1:A6. The function STRINGIFY does not know that you called it with a named range or even a range. It only sees the values in the range that you passed as an argument to it.

A screenshot of a Google Sheets spreadsheet showing the user entering the formula =STRINGIFY(SixValues) in a cell.A screenshot of a Google Sheets spreadsheet showing the output of the formula =STRINGIFY(SixValues) displayed in the cell.

The value [[1],[2],[3],[4],[5],[6]] is the JSON string representation of the two-dimensional array below. The outer array contains rows and each inner array contains the values in each row (i.e., its columns).

[
  [1],
  [2],
  [3],
  [4],
  [5],
  [6]
]

Reading all the named ranges in a Google Sheets spreadsheet

To read all the named ranges that have been defined in your spreadsheet, use the getNamedRanges() method of the Spreadsheet object.

The logNamedRanges() function below, reads all the named ranges in the spreadsheet and logs their name, the name of the sheet that contains the range and the range's A1 notation.

function logNamedRanges() {
  var rangeList = SpreadsheetApp.getActive().getNamedRanges()
  rangeList.forEach(function (namedRange) {
    
    // Log the name of the range
    Logger.log(namedRange.getName());
    
    var range = namedRange.getRange();
    
    // Log the name of the sheet containing the named range
    Logger.log(range.getSheet().getName());
    
    // Log the named range's A1 notation
    Logger.log(range.getA1Notation());
  });
}
A screenshot of the logs popup window displaying the logs written by the logNamedRanges() function.

Conclusion

In this tutorial, you learned about named ranges in Google Sheets.

Thanks for reading!

Sign up to be notified when I publish new content

By signing up you agree to the Privacy Policy & Terms.