1

I am trying to create multiple named ranges from a list spreadsheet ID's and sheet ID's. I am using the Sheets API batchUpdate method to define the named range parameters.

My requirement is to create named ranges for the entire columns (A:B) and not with row number boundations (A1:B1000).

The issue I am having is that even though I am not specifying a startRowIndex and an endRowIndex, the function is not creating a named range for the entire column (A:B), rather the named range which is created is bounded by row numbers (A1:B1000).

As per the documentation, the way to refer to entire columns in a GridRange is to not specify startRowIndex and endRowIndex, but this does not seem to work with addNamedRange request.

Please let me know if I am doing something wrong or if there is any other method to create a named range for whole columns.

I've also tried it with the setNamedRange method from SpreadsheetApp but the result is the same.

Here is my code :

ssid is the spreadsheet ID and sid is the sheet ID.

Sheets.Spreadsheets.batchUpdate(
        {"requests": [{
            "addNamedRange": {
                "namedRange": {
                    "name": "Named Range",
                    "range": {
                        "sheetId": sid,
                        "startColumnIndex": 0,
                        "endColumnIndex": 1,
                    },
                }
            }
        }]}
        ,ssid)
Pranjal Mishra
  • 19
  • 4
  • I think it's a default behavior. – vector Mar 28 '22 at 06:28
  • Have you tried updating the `rowCount` property of the sheet before adding the namedRange? e.g. ` request = { "updateSheetProperties": { "fields": "gridProperties.rowCount", "properties": { "gridProperties": { "rowCount": maxRowCount }, "sheetId": 0 } } }; ` – seanderickson Nov 08 '22 at 01:49

1 Answers1

1

In your situation, when you can use Google Apps Script, how about the following workaround? The flow of this workatound is as follows.

  1. Create a new named range.
    • Here, the range of "Sheet1!A:A" is set as the sample named range.
    • In this case, when the created named range is seen, when the default sheet is used, the named range of "Sheet1!A1:A1000" is created.
  2. Retrieve the created named range.
  3. Update the created named range with the range of "Sheet1!A:A".
    • By this update, the range of named range is changed from "Sheet1!A1:A1000" to "Sheet1!A:A".

When this flow reflects to a sample script, it becomes as follows.

Sample script:

function myFunction() {
  const name = "sampleNamedRange1"; // Please set the name of the named range.
  const a1Notation = "Sheet1!A:A"; // Please set the range as A1Notation.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.getRange(a1Notation);
  ss.setNamedRange(name, range);
  const createdNamedRange = ss.getNamedRanges().find(e => e.getName() == name);
  if (!createdNamedRange) throw new Error(`the named range of "${name}" is not found.`);
  createdNamedRange.setRange(range);
}
  • When this script is run, a new named range of sampleNamedRange1 is created as the range of Sheet1!A:A.

Note:

  • Unfortunately, when the flow of this workaround is run using Sheets API, "Sheet1!A1:A1000" couldn't change to "Sheet1!A:A". It seems that this is the current specification. In this case, when the created named range is updated using Spreadsheet service (SpreadsheetApp), the range was changed to "Sheet1!A:A".

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165