CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
isaaacs
isaaacs

Reputation: 89

How to use indexOf for entire column?

I am creating a column (Column A) with dropdown lists which are dependant on the adjacent value in Column G of the same row. The content of the dropdown lists are in another sheet (OE names) where they are indexed to select the correct list for the value.

A sample of the sheet with only the relevant scripts and columns is here: https://docs.google.com/spreadsheets/d/1bligSkSDr0dtU3Zwj1c-SasvKbHqX-QhbM8zysIvM-Q/edit?usp=sharing

The code I have tried is:

function DropDowns() {

var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();;
var OEsheet = ss.getSheetByName("OE names");
var SOsheet = ss.getSheetByName("Sales Order");
var OEcolumn = SOsheet.getRange("A2:A");
var Bcolumn = SOsheet.getRange("G2:G");

  OEcolumn.clearContent().clearDataValidations();

  var OEnames = OEsheet.getRange(1, 1, 1, OEsheet.getLastColumn()).getValues();

  var OEnamesIndex = OEnames[0].indexOf(Bcolumn.getValue()) + 1;

  if(OEnamesIndex != 0){
 var validationRange = OEsheet.getRange(2, OEnamesIndex, OEsheet.getLastRow());
 var validationRule = app.newDataValidation().requireValueInRange(validationRange).build();

 OEcolumn.setDataValidation(validationRule);

}
  }

This code does not do what is required because it creates dropdown lists but they seem to only be taken from the first column in the OE names sheet, so does not match it to the value in Column G. The problem seems to be that indexing an entire column does not index each individual value going down the column. How would I be able to make this work?

Thank you

Upvotes: 3

Views: 245

Answers (2)

Yaakov Bressler
Yaakov Bressler

Reputation: 12168

Here's my version of a solution which is accomplished by:

  1. Building the validator range
  2. Setting the value in column A to that of G.
  3. Building and applying the validation rule to A.

(Also, I took the liberty to problem solve it in the file you shared above. You may need to reset it.)

function DropDowns_2(){

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();;
  var OEsheet = ss.getSheetByName("OE names");
  var SOsheet = ss.getSheetByName("Sales Order");
  var Bcolumn = SOsheet.getRange("G2:G");
  var Bcolumn_vals = SOsheet.getRange("G2:G").getValues();

  // a little sloppy with converting to titlecase – but in one line!
  var Bcolumn_vals_title_case = Bcolumn_vals.map(function(x){return [x[0].charAt(0).toUpperCase() + x[0].slice(1).toLowerCase()]})
  var OEcolumn = SOsheet.getRange("A2:A");

  // Set the values
  OEcolumn.setValues(Bcolumn_vals_title_case)

  // build the validation rule here
  var OEnames = OEsheet.getRange(1, 1, 1, OEsheet.getLastColumn()).getValues()[0];
  if (OEnames.length==0){Console.log('Error: no names in OE Sheet'); return} // abort
  var validationRule = app.newDataValidation().requireValueInList(OEnames).build()
  OEcolumn.setDataValidation(validationRule)
  // done!
}

Upvotes: 1

Tanaike
Tanaike

Reputation: 201723

  • You want to put the data validation rules to the column "A" on the sheet of "Sales Order".
  • You want to use the content of the dropdown list from the sheet of "OE names".
  • For example, in your shared Spreadsheet, the values of cells "G1" and "G2" are Lachhmangarh and Beawar, respectively. In this case, you want to use the contents of the column "A" and "B" in the sheet of "OE names", respectively.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? The flow of this modified script is as follows.

Flow:

  1. Retrieve values from the sheet of "OE names"
  2. Create an object for using to create the data validation rules.
  3. Clear values and data validation rules at the column "A" of the sheet of "Sales Order".
  4. Retrieve values from the column "G" on the sheet of "Sales Order".
  5. Create the data validation rules from the retrieved values and the object.
  6. Set the created data validation rules to the column "A" of the sheet of "Sales Order".

Modified script:

function DropDowns() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var OEsheet = ss.getSheetByName("OE names");
  var SOsheet = ss.getSheetByName("Sales Order");

  // Retrieve values from the sheet of "OE names"
  var OEnames = OEsheet.getRange(1, 1, 1, OEsheet.getLastColumn()).getValues()[0];

  // Create an object for using to create the data validation rules.
  var obj = OEnames.reduce(function(o, e, i) {
    o[e.toUpperCase()] = OEsheet.getRange(2, i + 1, OEsheet.getLastRow());
    return o;
  }, {});

  // Clear values and data validation rules at the column "A" of the sheet of "Sales Order".
  var OEcolumn = SOsheet.getRange("A2:A" + SOsheet.getLastRow());
  OEcolumn.clearContent().clearDataValidations();

  // Retrieve values from the column "G" on the sheet of "Sales Order".
  // Create the data validation rules from the retrieved values and the object.
  var Bcolumn = SOsheet.getRange("G2:G" + SOsheet.getLastRow());
  var rules = Bcolumn.getValues().map(function(e) {return e[0].toUpperCase() in obj ? [app.newDataValidation().requireValueInRange(obj[e[0].toUpperCase()]).build()] : [null]});

  // Set the created data validation rules to the column "A" of the sheet of "Sales Order".
  OEcolumn.setDataValidations(rules);
}

Note:

  • In the values of the column "G" of the sheet "Sales Order", there are the values of Bari and BARI, and there are not existing values in "OE names".
    • In this script, Bari and BARI are used as the same values.
    • About not existing values, the data validation rules are not set.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 3

Related Questions