Dropdown selection to populate a cell in multiple sheets

Question

I have a dropdown on sheet1 (overview) that I need to hide rows in the same sheet and populate a cell in multiple other sheets (sheet) when a certain selection is made

if dropdown = ACHIEVED then hide 2 rows in sheet1 and populate cell A1 with value of "5"

This is what I have but it doesn't seem to be working although the script runs with no errors

var overview = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overview');
var ss = SpreadsheetApp.getActiveSpreadsheet();


["T1", "T2", "T3", "T4", "T5", "T6"].forEach(function (s) {
var sheet = ss.getSheetByName(s);

var dropdownValueEnglish = overview.getRange("E80:F80").getValue();
var targetRangeEnglish = sheet.getRange("B69:F69");

var dropdownValueEnglish = overview.getRange("E80:F80").getValue();
var targetRangeEnglish = sheet.getRange("B69:F69");
if (dropdownValueEnglish == "ACHIEVED") {
  overview.hideRows(81, 2);
  sheet.getRange("F71").setValue("5");
}
else {
  overview.showRows(81, 2);
  sheet.getRange("F71").setValue("");
}

I wondered if it is because my dropdown is in 2 merged cells but I have tried to copy the value to a single cell first and it still doesn't work. Thank you in advance for any help you can give me.

Answer 1

If wish to get the values in "E80:F80" the you would have to use getValues() and it returns a 2d array. This should work by just using E80 if that it not what you wish they please provide a bit more explanation.

function dropdownSelectionToPopulateacell() {
  //https://stackoverflow.com/questions/77322218/dropdown-selection-to-populate-a-cell-in-multiple-sheets
  var ss = SpreadsheetApp.getActive();
  var osh = ss.getSheetByName('Sheet0');
  ["T1", "T2", "T3", "T4", "T5", "T6"].forEach(s => {
    var sh = ss.getSheetByName(s);
    var dve = osh.getRange("E80").getValue();
    if (dve == "ACHIEVED") {
      osh.hideRows(81, 2);
      sh.getRange("F71").setValue("5");
    }
    else {
      osh.showRows(81, 2);
      sh.getRange("F71").setValue("");
    }
  });
}

• Range.getValue()

• Range.getValues()

• format code

• google-apps-script reference

• javascript reference

• Learn More