Named and Protected Ranges

The Sheets API allows you to create, control and delete named and protected ranges. The examples on this page illustrate how some common sheet operations can be achieved with the API.

In these examples, the placeholders spreadsheetId and sheetId are used to indicate where you would provide those IDs. The spreadsheet ID can be discovered from the spreadsheet URL; the sheet ID can be obtained from the spreadsheet.get method.

In addition, named and protected ranges have their own IDs (referred to as namedRangeId and protectedRangeId in this document). These IDs are used to identify the ranges when making requests to update or delete them. The ID is returned in the response to an API request that creates a named or protected range. They can also be obtained from a spreadsheets.get request, in the Spreadsheet response body.

Add named or protected ranges

The following spreadsheets.batchUpdate request contains two request objects. The first gives the range A1:E3 the name "Counts". The second provides a warning-level protection to the range A4:E4. This level protection still allows cells within the range to be edited, but prompts a warning prior to making the change.

These requests will return an AddNamedRangeResponse and an AddProtectedRangeResponse, containing the range IDs and properties.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "addNamedRange": {
        "namedRange": {
          "name": "Counts",
          "range": {
            "sheetId": sheetId,
            "startRowIndex": 0,
            "endRowIndex": 3,
            "startColumnIndex": 0,
            "endColumnIndex": 5,
          },
        }
      }
    },
    {
      "addProtectedRange": {
        "protectedRange": {
          "range": {
            "sheetId": sheetId,
            "startRowIndex": 3,
            "endRowIndex": 4,
            "startColumnIndex": 0,
            "endColumnIndex": 5,
          },
          "description": "Protecting total row",
          "warningOnly": true
        }
      }
    }
  ]
}

Delete named or protected ranges

The following spreadsheets.batchUpdate request contains two request objects. The first deletes an existing named range, using the namedRangeId obtained from a previous API call. The second deletes an existing range protection, using the protectedRangeId obtained from a previous API call.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "deleteNamedRange": {
          "namedRangeId": namedRangeId,
      }
    },
    {
      "deleteProtectedRange": {
        "protectedRangeId": protectedRangeId,
      }
    }
  ]
}

Update named or protected ranges

The following spreadsheets.batchUpdate request contains two request objects. The first updates the name of an existing named range to "InitialCounts", using the namedRangeId obtained from a previous API call. The second request updates an existing protected range so that it now protects the same named range, and allows only the listed users to edit those cells. This request makes use of the namedRangeId and protectedRangeId obtained from previous API calls.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "updateNamedRange": {
        "namedRange": {
          "namedRangeId": namedRangeId,
          "name": "InitialCounts",
        },
        "fields": "name",
      }
    },
    {
      "updateProtectedRange": {
        "protectedRange": {
          "protectedRangeId": protectedRangeId,
          "namedRangeId": namedRangeId,
          "warningOnly": false,
          "editors": {
            "users": [
              "omar@example.com",
              "sam@example.com",
            ]
          }
        },
        "fields": "namedRangeId,warningOnly,editors"
      }
    }
  ]
}

Send feedback about...

Need help? Visit our support page.