google-apps-script - Search for text out of Datavalidation

Question

I want to realize a delivery list for a game, where people can register themselves with a Google form into a Memberlist. When they deliver then as example Ironore, it will be inserted via a form into a sheetlist.

When i then want to delete it out from the list, I want to search via a Datavalidation out of the member list. So i select as example "Jason_Matters" and the it should delete all rows that are from "Jason_Matters". The script I use looks like this. But I dont know, how I use the information out of the datavalidation:

function readRows_JS() {
var sheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lieferungen Team 
Matters");
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var Sheet2 = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mitarbeiter Liste');
var validate1 = Sheet2.getRange("H12").getDataValidation();

var ui = SpreadsheetApp.getUi();
var response = ui.alert('Sind Sie sich sicher?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[1] == "validate1") {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
}
};

What I have to insert for "validate1"?

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Answer 1

My guess is that you want to do something like this:

function readRows_JS() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Lieferungen Team Matters");
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var sh2=ss.getSheetByName('Mitarbeiter Liste');
  var selection=sh2.getRange("H12").getValue();
  var ui=SpreadsheetApp.getUi();
  var response=ui.alert('Sind Sie sich sicher?', ui.ButtonSet.YES_NO);
  if (response==ui.Button.YES) {
    var d=0;
    for (var i=0;i<vA.length; i++) {
      if (vA[i][1]==selection) {
        sh.deleteRow(i+1-d++);
      }
    }
  }
}