Thursday, September 19, 2024

Google sheet: Select default value from the list as per Change is dropdown

function onEdit(e) {

  var sheet = e.source.getActiveSheet();

  var range = e.range;

  var value = range.getValue();

  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var activeRange = SpreadsheetApp.getActiveSheet().getActiveRange();

  var rangeAddress = activeRange.getA1Notation();

  var column = rangeAddress.match(/[A-Z]+/)[0];

  var row = rangeAddress.match(/\d+/)[0];

  // Specify the cell where the dropdown is located (e.g., A1)

  var dp = 'C' + row;//Dropdown Location to update values

  var dropdownCell = sheet.getRange(dp);

  var range1 = sheet.getRange(dp);


  //Dropdown where we are making changes to updated second dropdown(e.g , In B Column we have a dropdown)


  if (column.startsWith('B')) {

    // Small, Caps and Int are the Name Ranges, Need to change it as the Value changes

    if (value == 'Small') {

      setDropdownUsingNamedRange(dp, value);

      setDefaultValue(dp, column + row);

    }

    if (value == 'Caps') {

      setDropdownUsingNamedRange(dp, value);

      setDefaultValue(dp, column + row);

    }

    if (value == 'Int') {

      setDropdownUsingNamedRange(dp, value);

      setDefaultValue(dp, column + row);


    }

  }


}

// funcation used to set name range for the dropdown

function setDropdownUsingNamedRange(range, nameRange) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange(range); // Change this to the cell where you want the dropdown


  // Set the named range as the source for the dropdown

  var rule = SpreadsheetApp.newDataValidation()

    .requireValueInRange(SpreadsheetApp.getActiveSpreadsheet().getRangeByName(nameRange), true)

    .build();


  range.setDataValidation(rule);

}

// Setting the default values in the dropdown. 

function setDefaultValue(dp, dropdownValue) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange(dropdownValue); // Specify the dropdown cell

  var value = range.getValue();

  var targetCell = sheet.getRange(dp); // Specify where to set the default value


  // Check the value of the dropdown

  if (value === 'Caps') {

    targetCell.setValue('A');

  } else if (value === 'Small') {

    targetCell.setValue('a');

  } else if (value === 'Int') {

    targetCell.setValue('1');

  }

}

No comments:

Post a Comment