How to set a named range for a data validation programmatically (in Google apps script) in a Google spreadsheet?

Question

Use Case

Example. I have a named range Apples (address "Sheet10!B2:B"), which in use for data validation for plenty of sheet cells. The data range for Apples can be changed (in a script), e.g. to "Sheet10!D2:D".

It works from UI

I can set manually a named range as a data source of data validation. In this case, the data validation of a cell will always refer to the named range Apples with updated the data range.

How to make it in Google Apps Script?

GAS Limits

The code, for setting data validation, should look like this, if you have a namedRange object:

      mySheet.getRange('F5')
        .setDataValidation(
          SpreadsheetApp.newDataValidation()
            .requireValueInRange(
              namedRange.getRange()
            )
            .setAllowInvalid(false)
            .build()
        );

DataValidationBuilder.requireValueInRange() does not work here as it requires only class Range (it cannot get NamedRange), and no reference to a named range will be used.

Is there a workaround or so?

UPD1 - Spreadsheet.getRangeByName() does not work

Getting range by name does not help, the data validation will get actual range address. SpreadsheetApp.getActive().getRangeByName("Apples")

UPD2 No way to make it so far in GAS

As @TheMaster posted, it's not possible at this moment.

Please set +1 for posts:

• https://issuetracker.google.com/issues/143913035
• https://issuetracker.google.com/issues/203557342

P.S. It looks like the only solution will work is Google Sheets API.

Answer 1

As a half-answer, if you want just validation and can live without the drop-down list of valid values, you can programmatically set a custom formula that references the named range. This reference to the named range will not get expanded in the AppsScript, so future changes to the Named Range's actual range will percolate to the validator. Like so:

      mySheet.getRange('F5')
        .setDataValidation(
          SpreadsheetApp.newDataValidation()
            .requireFormulaSatisfied(
               '=EQ(F5, VLOOKUP(F5, ' + namedRange.getName() + ', 1))'
            )
            .setAllowInvalid(false)
            .build()
        );

(The formula just checks that the value in the cell being tested is equal to what VLOOKUP finds for that cell, in the first column -- I'm assuming the named range content is sorted.)

Answer 2

I thought that in your situation, I thought that when Sheets API is used, your goal might be able to be used.

Workaround 1:

This workaround uses Sheets API.

Usage:

1. Prepare a Google Spreadsheet.

1. Please create a new Google Spreadsheet.

2. From Example. I have a named range Apples (address "Sheet10!B2:B"), which in use for data validation for plenty of sheet cells. The data range for Apples can be changed (in a script), e.g. to "Sheet10!D2:D"., please insert a sheet of "Sheet10" and put sample values to the cells "B2:B" and "D2:D".

3. Please set the named range Sheet10!B2:B as Apple.

2. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, please enable Sheets API at Advanced Google services.

function myFunction() {
  const namedRangeName = "Apple"; // Please set the name of the named range.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet10");
  const requests = [{ updateCells: { range: { sheetId: sheet.getSheetId(), startRowIndex: 0, endRowIndex: 1, startColumnIndex: 0, endColumnIndex: 1 }, rows: [{ values: [{ dataValidation: { condition: { values: [{ userEnteredValue: "=" + namedRangeName }], type: "ONE_OF_RANGE" }, showCustomUi: true } }] }], fields: "dataValidation" } }];
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

• In this request, the name of the named range is directly put to userEnteredValue.

3. Testing.

When this script is run to the above sample Spreadsheet, the following result is obtained.

When this demonstration is seen, first, you can see the named range of "Apple" which has the cells "B1:B1000". When a script is run, data validation is put to the cell "A1" with the named range of "Apple". In this case, the values of data validation indicate "B1:B1000". When the range named range "Apple" is changed from "B1:B1000" to "D1:D1000" and the data validation of "A1" is confirmed, it is found that the values are changed from "B1:B1000" to "D1:D1000".

Workaround 2:

This workaround uses the Google Spreadsheet service (SpreadsheetApp). In the current stage, it seems that the Google Spreadsheet service (SpreadsheetApp) cannot directly achieve your goal. This has already been mentioned in the discussions in the comment and TheMaster's answer. When you want to achieve this, how about checking whether the range of the named range is changed using OnChange as following workaround 2?

Usage:

1. Prepare a Google Spreadsheet.

1. Please create a new Google Spreadsheet.

2. From Example. I have a named range Apples (address "Sheet10!B2:B"), which in use for data validation for plenty of sheet cells. The data range for Apples can be changed (in a script), e.g. to "Sheet10!D2:D"., please insert a sheet of "Sheet10" and put sample values to the cells "B2:B" and "D2:D".

3. Please set the named range Sheet10!B2:B as Apple.

2. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, please install OnChange trigger to the function onChange.

First, please run createDataValidation. By this, data validation is put to the cell "A1" of "Sheet10". In this case, the set range is the range retrieved from the named range "Apple". So, in this case, the range is Sheet10!B2:B1000.

As the next step, please change the range of the named range from Sheet10!B2:B1000 to Sheet10!D2:D1000. By this, onChange` function is automatically run by the installed OnChange trigger. By this, the data validation of "A2" is updated. By this, the values of data validation are changed.

const namedRangeName = "Apple"; // Please set the name of the named range.
const datavalidationCell = "Sheet10!A2"; // As a sample. data validation is put to this cell.

function onChange(e) {
  if (e.changeType != "OTHER") return;
  const range = e.source.getRangeByName(namedRangeName);
  const a1Notation = `'${range.getSheet().getSheetName()}'!${range.getA1Notation()}`;
  const prop = PropertiesService.getScriptProperties();
  const previousRange = prop.getProperty("previousRange");
  if (previousRange != a1Notation) {
    const rule = SpreadsheetApp.newDataValidation().requireValueInRange(e.source.getRangeByName(namedRangeName)).setAllowInvalid(false).build();
    e.source.getRange(datavalidationCell).setDataValidation(rule);
  }
  prop.setProperty("previousRange", a1Notation);
}

// First, please run this function.
function createDataValidation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rule = SpreadsheetApp.newDataValidation().requireValueInRange(ss.getRangeByName(namedRangeName)).setAllowInvalid(false).build();
  ss.getRange(datavalidationCell).setDataValidation(rule);
  const prop = PropertiesService.getScriptProperties();
  const range = ss.getRangeByName(namedRangeName);
  const a1Notation = `'${range.getSheet().getSheetName()}'!${range.getA1Notation()}`;
  prop.setProperty("previousRange", a1Notation);
}

References:

• Method: spreadsheets.batchUpdate
• UpdateCellsRequest
• DataValidationRule

Answer 3

Currently, This seems to be impossible. This is however a known issue. +1 this feature request, if you want this implemented.

https://issuetracker.google.com/issues/143913035

Workarounds from the tracker issue creator:

If a validation rule is manually created with a NamedRange via the Sheets GUI, it can then be copied programmatically using Range.getDataValidations(), and subsequently used to programmatically create new DataValidations. DataValidations created this way maintain their connection to the NamedRange, and behave like their manually created counterparts. This demonstrates that the functionality to 'use' NamedRanges for data validation rules is already possible with Apps Scripts, but not the option to 'create' them.