Create a array about Datas from GSheet-CodePudding

I am in the process of completing a GoogleScript training course. Currently I am trying to transfer table values to a dropdown field in GSheet. When I write the script manually it works very well, but how do I need to create the script that I can get the values out of the cells of a worksheet?

function myTest(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("AllCity");
  var myArray = "";

  //
  //Create the values for the drop-down-field
  //
  for (var i = 4; i < 15; i  ) {
      myArray = myArray   '"'   sheet.getRange(i,1).getDisplayValue()  '", ' 
      }

  myArray = '['   myArray   ']'

  Logger.log(myArray);

  sheet.getRange('A2').activate();
  sheet.getRange('A2').clearDataValidations();
  sheet.getRange('A2').setDataValidation(SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true)
    //Manually it works :( - but how about the variable "myArray"?
    .requireValueInList(["Hamburg", "Stuttgart", "Frankfurt", "München", "Freiburg", "Paris", "Warschau", "New York", "Passau", "Wien", "Rom", ]  , true)
    .build()); 
}

Here I have provided a sample file: https://docs.google.com/spreadsheets/d/1qNuoJYWuuM73jTdVtxpQCm8lbWvdzOWRc-UJxvggDGk/edit?usp=sharing

Does anyone have a hint for me where my thinking error is, or could adjust my script accordingly so that I can see and learn in the process.

Greetings

CodePudding user response：

In your situation, how about the following patterns?

Pattern 1:

In this pattern, an array is created by retrieving the values from the cells "A4:A14", and, the data validation rule is created using the created array.

function myTest() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("AllCity");
  var myArray = sheet.getRange("A4:A14").getDisplayValues().map(([a]) => a); // or you can also use getValues instead of getDisplayValues.
  sheet.getRange('A2').clearDataValidations();
  sheet.getRange('A2').setDataValidation(SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true)
    .requireValueInList(myArray, true)
    .build());
}

Pattern 2:

In this pattern, the data validation rule is created using the range of "A4:A14".

function myTest() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("AllCity");
  sheet.getRange('A2').clearDataValidations();
  sheet.getRange('A2').setDataValidation(SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true)
    .requireValueInRange(sheet.getRange("A4:A14"), true)
    .build());
}

Reference:

requireValueInRange(range, showDropdown)