Retrieves All Named Ranges in Spreadsheet as a1Notation

GasTips
Google Apps Script / Sheets API / spreadsheet / namedRange

Gists

This is a sample script for Google Apps Script (GAS). This script retrieves all named ranges in Spreadsheet. The names and range of the retrieved named ranges are output as the keys and the values of JSON object, respectively. The sample output is {"name1": "Sheet1!A1:B2", "name2": "Sheet2!B1:C2",,,}. The name of named range has to be only one in the spreadsheet. This was used.

Sheets.Spreadsheets.get() of Sheets API can retrieve all named ranges. But the retrieved range is the grid range. So in this sample script, the grid range was converted to a1Notation. The main part of this sample script is here.

In order to use this script, please enable Sheets API at Advanced Google Services and API console as follows.

Enable Sheets API v4 at Advanced Google Services

  • On script editor
    • Resources -> Advanced Google Services
    • Turn on Google Sheets API v4

Enable Sheets API v4 at API console

  • On script editor
    • Resources -> Cloud Platform project
    • View API console
    • At Getting started, click Enable APIs and get credentials like keys.
    • At left side, click Library.
    • At Search for APIs & services, input “sheets”. And click Google Sheets API.
    • Click Enable button.
    • If API has already been enabled, please don’t turn off.

If now you are opening the script editor with the script for using Sheets API, you can enable Sheets API for the project by accessing this URL https://console.cloud.google.com/apis/library/sheets.googleapis.com/

Sample script :

When you use this sample script, please input spreadsheetId in main() and run it.

function getNamedRanges2(spreadsheetId) {
    var ss = SpreadsheetApp.openById(spreadsheetId);
    var sheetIdToName = {};
    ss.getSheets().forEach(function(e) {
        sheetIdToName[e.getSheetId()] = e.getSheetName();
    });
    var result = {};
    Sheets.Spreadsheets.get(spreadsheetId, {fields: "namedRanges"})
        .namedRanges.forEach(function(e) {
            var sheetName = sheetIdToName[e.range.sheetId.toString()];
            var a1notation = ss.getSheetByName(sheetName).getRange(
                e.range.startRowIndex + 1,
                e.range.startColumnIndex + 1,
                e.range.endRowIndex - e.range.startRowIndex,
                e.range.endColumnIndex - e.range.startColumnIndex
            ).getA1Notation();
            result[e.name] = sheetName + "!" + a1notation;
        });
    return result;
}

function main() {
    var spreadsheetId = "### spreadsheet ID ###";
    var result = getNamedRanges2(spreadsheetId);
    Logger.log(JSON.stringify(result));
}

Result :

This is a sample result. The names and range of the named ranges are output as the keys and the values of JSON object, respectively.

{
  "name1": "Sheet1!A1:B2",
  "name2": "Sheet2!B1:C2",
  "name3": "Sheet3!C1:D2",
  "name4": "Sheet4!D1:E2",
  "name5": "Sheet5!E1:F2"
}

References :

 Share!