I have 2 Sheets "Client (Table1 below)" and "Color Description (table2 below)." I want to use GAS to do the following:

1. onEdit I want to compare 2 ranges then...
2. Find each instance where "Client Sheet - Col2" matches "Color Description Sheet - Col1"
3. Get the address of the cell where the instance was located
4. Set that value in "Client Sheet - Col3(Cell Address)"

The desired output would look something like this: [John | red | Color Description A2] or [Beth | pink | Color Description A7]

e is triggered by multiple things on the spreadsheet

I hope this makes sense. TIA for your help.

CodePudding user response：

I believe your goal is as follows.

• You want to achieve your process of 1 to 4 in your question using Google Apps Script.

Sample script:

In this sample script, from your question, the sheet names of Client and Color Description are used. About this, please modify them for your actual situation.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const client = ss.getSheetByName("Client");
  const colorDescription = ss.getSheetByName("Color Description");
  const obj = colorDescription.getRange("A2:B"   colorDescription.getLastRow()).getValues().reduce((o, [a, b]) => (o[a.toLowerCase()] = b, o), {});
  const values = client.getRange("B2:B"   client.getLastRow()).getValues().map(([b]) => [obj[b.toLowerCase()] || ""]);
  client.getRange(2, 3, values.length, 1).setValues(values);
}

• When this script is run, the values of column "B" of Color Description sheet are put to the column "C" of Client sheet by searching the values of Color.

Note:

• In this sample script, it supposes that the 1st row of each sheet is the header row from your sample sheet in your question.
• I cannot understand about onEdit of onEdit I want to compare 2 ranges then.... In my proposed script, the script can be run by the script editor, button, custom menu, and so on. But if you want to run the script using OnEdit, can you provide the condition for executing the script? By this, I would like to modify it.

References:

• reduce()
• map()

CodePudding user response：

This is what I came up with. Sorry if this is kind of clunky as I'm new to it all.

function myFunction() {
    var clientColors = SpreadsheetApp.getActiveSpreadsheet();
    var clientSheet = clientColors.getSheetByName("Client");
    var colorSheet = clientColors.getSheetByName("Color Description");
    var lastRownumberClient = clientSheet.getLastRow();
    var lastRownumberColor = colorSheet.getLastRow();
    var clientSheetVals = clientSheet.getRange(2,2,lastRownumberClient-1,1).getValues();//-1 to remove header row
    var colorSheetVals = colorSheet.getRange(2,1,lastRownumberColor-1,1).getValues();//-1 to remove header row

    var colorColA = clientSheetVals.map(function(row){//This maps out ColB in the client sheet and returns the matches from ColA in the color sheet
    var theItem = row[0];
    var  lookupColorRangeValues = colorSheetVals.map(function(row){return row[0];});            
    var index = lookupColorRangeValues.indexOf(theItem) 2;
      
    return ['A' [index]]});//'A' concatenates the the column letter to the returned index number
 
 
 
 clientSheet.getRange(2,3,clientSheet.getLastRow()-1,1).setValues(colorColA);//Sets the returned values in ColC of the client sheet -1 to remove header row
}