Breaking News: Grepper is joining You.com. Read the official announcement!

GAS - Dependent dynamic drop-down lists

Scary Sloth answered on February 16, 2023 Popularity 1/10 Helpfulness 1/10

answer GAS - Dependent dynamic drop-down lists

GAS - Dependent dynamic drop-down lists

Comment

Second drop-down dependent list

function onEdit(e) {
  const range = e.range;
  if (range.getColumn() === 3) { // Change the column number to match yours
    updateSubCategoryDropDown(range.getRow(), range.getValue())
  }
}

async function updateSubCategoryDropDown(rowNumber, namedRangeName) {
  const [cell, rule] = await Promise.all([
    SpreadsheetApp.getActiveSheet().getRange(rowNumber, 4), // idem
    createRuleDataValidation(namedRangeName)
  ])
  cell.setDataValidation(rule)
}

function createRuleDataValidation(namedRangeName) {
  const rangeValues = getNamedRangeValuesFromCacheOrPutIt(namedRangeName.replace(' ', '_'))
  const parsedRangeValues = JSON.parse(rangeValues)
  return SpreadsheetApp.newDataValidation().requireValueInList(parsedRangeValues).build()
}

function getNamedRangeValuesFromCacheOrPutIt(namedRangeName) {
  const cache = CacheService.getScriptCache();
  const cached = cache.get("namedRange_" + namedRangeName);
  if (cached != null) {
    return cached;
  }
  const namedRangeValues = JSON.stringify(SpreadsheetApp.getActive().getRangeByName(namedRangeName).getValues().flat())
  cache.put("namedRange_" + namedRangeName, namedRangeValues, 2400);
  return namedRangeValues;
}

Popularity 1/10 Helpfulness 1/10 Language whatever

Source: medium.com

Tags: dynamic whatever

Link to this answer
Share Copy Link

Contributed on Feb 16 2023

Scary Sloth

0 Answers Avg Quality 2/10

GAS - Dependent dynamic drop-down lists

Contents

More Related Answers

GAS - Dependent dynamic drop-down lists

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.