If the data entered in the cell matches the range then run the google script command
I want to execute some code based on the entered value in a cell that is in a named range.
I've tried something similar to this, but it doesn't seem to work:
function onEdit() {
var aSheet = SpreadsheetApp.getActiveSheet();
var aCell = aSheet.getActiveCell();
var aColumn = aCell.getColumn();
var aDatarange = aSheet.getRange(aCell.getRow(),aColumn);
var aData = aDatarange.getValue();
var SomeRange = aSheet.getRangeByName('RangeName');
if (aData in 'SomeRange') {
execute some code
}
}
I am assuming this is an if statement that I am doing wrong, but I have tried var in range
both var = range
and it doesn't seem to work.
+3
source to share
1 answer
You need to get data from a range so that the value from a cell in the range data can be found. I've added a few new variables to your code and included comments about new lines of code.
function onEdit() {
var dataAsString,rangeData;
var aSheet = SpreadsheetApp.getActiveSheet();
var aCell = aSheet.getActiveCell();
var aColumn = aCell.getColumn();
var aDatarange = aSheet.getRange(aCell.getRow(),aColumn);
var aData = aDatarange.getValue();
var SomeRange = aSheet.getRangeByName('RangeName');
rangeData = SomeRange.getValues();//Get the values out of the range
dataAsString= rangeData.toString();//Convert the 2D array to a a comma seperated string of values
if (dataAsString.indexOf(aData) !== -1) {//The value of "aData" was found in the data string
//execute some code
}
}
+2
source to share