CODEWITHSUNDEEP
google-apps-script
viv227295
viv227295

Reputation: 397

How to edit EditVal to get a range rather than in cell text in Google Apps Script

Input Data enter image description here

Expected Output :

When i input any name (from C3:C!Sheet2) in C3:C!Sheet1, in D3:D!Sheet1 there is an auto dynamic drop-down list which contains only those languages from A3:A!Sheet2 which correspond to that inputed name in C3:C!Sheet1.

Currently when I input "IndividualName" in C3:C!Sheet1, then only the expected output is shown. But i don't want "IndividualName", i want any name from C3:C!Sheet2.

Here below is the link to the published Google Spreadsheet to better understand what i am trying achieve. (Sorry i was not clear enough before.)

https://docs.google.com/spreadsheets/d/1BcoB4-xIW9zRU_Vr8TciAIJFV4Fajp3vWTtitKYQXOc/edit#gid=906258441

How to edit line no. 13 in the Google Apps Script below in such a way that instead of it referring to the content == "IndividualName", it refers to range which is = column C:C in sheet2 in the spreadsheet.

1   function onEdit(event){
2   var ss=SpreadsheetApp.getActiveSpreadsheet()
3    var s=ss.getSheetByName("Sheet1")
4     var sheet = event.source.getActiveSheet().getName();
5  var editedCell = event.range.getSheet().getActiveCell();
6  var editVal=event.range.getSheet().getActiveCell().getValue()
7  var editRow=event.range.getSheet().getActiveCell().getRow()
8  
9  var s1=ss.getSheetByName("Sheet2")///////get new sheet
10  var lr1=s1.getLastRow()//////get last row of new sheet
11 
12  if(sheet=="Sheet1" && editedCell.getColumn() ==3){
13    if(editVal=="IndividualName"){
14     var validation=s1.getRange(3, 1, lr1,1)//////get column A data of new sheet
15      var cell= s.getRange(editRow,4,1,1)
16      var rule = SpreadsheetApp.newDataValidation().requireValueInRange(validation,false)
17      .setAllowInvalid(false)
18      .build();
19      cell.setDataValidation(rule);
20    }}}

Thanx!

Upvotes: 0

Views: 101

Answers (1)

Brian
Brian

Reputation: 4344

If I'm reading this correctly, you want to add a Validation dropdown next to any input name built from Sheet 2.

To do that, you need to loop through the lists and return an object which can be used to create a validation menu. I would do the following:

function newValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');

  // Get the data from the database and the list to check against.
  var sheet1Data = sheet1.getDataRange().getValues();
  var sheet2Data = sheet2.getDataRange().getValues();

  // Loop through any names in sheet 2
  for(var i=2; i<sheet2Data.length; i++) {
    var langs = [];

    // loop through the database for each name
    for(var j=2;j<sheet1Data.length;j++) {

      // if the names match, push the corresponding value into the array
      if(sheet2Data[i][2] == sheet1Data[j][2]) {
        langs.push(sheet1Data[j][0]);
      }
    }

    // Include an error if the array is empty
    if(langs.length === 0) {
      langs.push("No languages matched");
    }

    // Build the validation rule and append it to the matching row
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(langs).build();
    sheet2.getRange(i+1, 4,1,1).setDataValidation(rule);
  }
}

I renamed your template sheets to be a little more friendly in the code. This script will check the entire sheet when you run it from the Script Editor. You could attach a trigger to check any time an edit is made. It could also be pared down to only match rows where there is no validation on column C to be a little faster, but I'll leave that to you.

You can see a working example in this Google Sheet.

Upvotes: 2

Related Questions