google sheets
I am trying to hide a row when a cell is filled with "x". I have tried using scripts but they don't work.
So there are "A", "B" and "C" filled in column F.
I want the script to hide rows where "A" and "B" are filled in column F.
So if row 3 and 4 contain "A" in column F, those should be hidden.
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
You need a script for this. This script runs on every edit (onEdit), checks whether the edit is in the 5th column (E), and if the new value is 1, then it hides the row of that cell.
function onEdit(e) {
if (e.range.getColumn() == 5 && e.value == 1) {
e.range.getSheet().hideRows(e.range.getRow());
}
}
Best Answer