I have 2 Sheets "Client (Table1 below)" and "Color Description (table2 below)." I want to use GAS to do the following:
- onEdit I want to compare 2 ranges then...
- Find each instance where "Client Sheet - Col2" matches "Color Description Sheet - Col1"
- Get the address of the cell where the instance was located
- 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
Client | Color | Cell Address |
---|---|---|
Jon | red | |
Beth | pink | |
Tom | blue | |
Cj | red | |
Omar | green | |
Lisa | Purple |
Color | Description |
---|---|
Red | Something about Red |
Green | Something about Green |
Blue | Something about Blue |
Purple | Something about Purple |
White | Something about White |
Pink | Something about Pink |
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" ofClient
sheet by searching the values ofColor
.
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
ofonEdit 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:
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
}