Google Apps Script - Spreadsheet - Create onEdit Dropdown list in a cell based on values not coming from a spreadsheet

Question

I have seen examples on how to create a dropdown list based on values coming from Range values from other spreadsheets, dropdown list depending on a cell value. In all examples i have seen, values were coming from range values from other spreadsheets.

What i want to achieve is :

in a spreadsheet with column headers, i have no line.
when i edit the 1st cell, it shows a dropdown list with values. Only when i edit the cell. If possible i would like not to pre-populate a the cell of the st column for a fixed number of rows, cause i am thinking this may consumes time, and i do not know how many cells there will be there in the file in one month.
values from the dropdown list are retrieved from another Google Workspace Service.

What i experienced at the moment :

I am able to retrieve the values i want to display in my dropdown list
If i am right, to construct a dropdown list, there is to use the SpreadsheetApp.newDataValidation() method to build the dropdown list
My values are stored in a 2 dimensional array, as it seems this would be the way to pass the values to a range of cells that is passed into parameter of the previous method, with the Build method at the end
i was able to set the values for a range of cells (ex: A1:A10) and display a dropdown list icon on each cell, but the values displayed when editing the cells are like val1;val2;val3...

My questions :

How do i create a dynamic dropdown list on the cell of the 1st column, dynamically, when i edit the cell ? If the cell of the 1st colum has to be prefilled, then at least why not

I do not know if iwa clear enough. This is the idea of the script :

var v_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var v_sheet = v_spreadsheet.getActiveSheet();
// I imagine i could specify A1:A1 to specify only the cell that is being edited ?
var range = v_sheet.getRange("A2:A5");
// my array below is 2 dimensions : v_array[0][0] = 'val1', v_array[0][1] = 'val2', etc
range.setValues(v_array_taskLists);
var dd_values = range.getValues();
var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(dd_values).build();
range.setDataValidation(validationRule);

As you can see i am passing by a range data, which may not be the correct way

Any help greatly appreciated Many thanks in advance

everspader everspader · Accepted Answer · 2021-03-10T19:28:49

Well, since you said you are able to retrieve the correct data you want to display in the dropdown. You can use this function to populate it. Where range is a range of cells and list is the list of values you want displayed in the dropdown.

function setRangeDataValidation(range, list) {
  var rangelValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(list)
    .setAllowInvalid(false)
    .build();
  range.setDataValidation(rangelValidation);
}

You can change the values available in the dropdown dynamically using the onChange trigger (that's the approach I used in a similar application). Let's say you want to have the data validation change in column A when some change is made in another column, say B in the same row.

function onChange(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getActiveSheet()

    if (e.changeType === 'EDIT') {
      var activeRange = ws.getActiveRange();
      var editCol = activeRange.getColumn();
      
      if (editCol === 1) {
        // in case more rows are changed at the same time (copy and paste)
        var numRows = activeRange.getNumRows();
        var startRow = activeRange.getRow();

        var yourList = //the list to be updated in the dropdown

        //You can loop through the list of values that were edited in column A in case you want a different dropdown in column B for each value passed in column A.
        for (var row = 0; row < numRows; row++) {
          // Here if you want different lists in the dropdown for different cells in columns A you can do the following
          //var cellColA = activeRange.getCell(startRow + row, 1).getValue();
          // yourList = updatedList;

          // Then et range of cell in column B
          var cellColB = ws.getRange(startRow + row, 2);
          setRangeDataValidation(cellColB, youList)
        }
      }
    }

Keep in mind though that the onChange trigger is an installable trigger that you can install in this way:

function createSpreadsheetChangeTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  ScriptApp.newTrigger('onChange')
    .forSpreadsheet(ss)
    .onChange()
    .create();
}

Notes:

If you just need to track changes to the spreadsheet content, and not its structure, it's arguably more appropriate to use an installable onEdit() trigger instead of an onChange() one.
Installing this via an onOpen trigger is not a good idea, since this will install a new trigger every time the spreadsheet is opened, and you will end up with many duplicate triggers.

Google Apps Script - Spreadsheet - Create onEdit Dropdown list in a cell based on values not coming from a spreadsheet

1 Answers

Notes: