Google Sheets scripting

The Sandbox Software/Technology
1

I’ve never done any scripting in Google Sheets, but I’ll trying to hide columns automatically if the value in row 11 is a ‘Y’. (This row is data validated, so no worry about case or people entering Yes or anything like that.)

Can someone tell me how that works? I’d like it to update on edit.

2

I don’t remember… but wanted to mention, I was put-off when I realized there’s a lag to running scripts.

3

I’m trying to use ChatGPT to do this, and this feels close, but it’s not working:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var col = range.getColumn();

if (sheet.getName() !== “Game Interest”) {
sheet = e.source.getSheetByName(“Game Interest”);
}

if (row == 11) {
if (sheet.getRange(row, col).getValue() == “Y”) {
sheet.hideColumns(col);
}
}
}

4

so you have a bunch of columns and if the value in row 11 is Y then you want to hide that column? Is that what you want to do?

your code is assuming that the edit is done on a single cell with row = 11. If the range of the edit is larger than that then your code doesnt work. Btw, you dont need to call “getRange” to get the value of the cell that was edited. YOu can just use range.getDisplayValue().

Another problem with your code is the way you check the sheet. You need to do this:

if( range.getSheet().getName() == “Game Interest”){
… do stuff
}

I just tested your code and it does work for me when I change a single cell in row 11.

5

Thank you! I worked with a friend that does google sheet stuff, and we figured it out. It ended up being pretty much exactly want you said — ChatGPT got close, but not quite what I wanted. I guess I wasn’t clear enough with the prompt.

6

This works:

 function onEdit(e) {

var range = e.range;
var row = range.getRow();
var col = range.getColumn();

  if(range.getSheet().getName() == "Game Interest"){


    if( 11 >= row && 11 <= row + range.getNumRows()-1){

      var vals = range.getValues();
        for(i = 0; i < range.getNumColumns(); i++){
          if(vals[11-row][i] == "Y")range.getSheet().hideColumns(col+i);
      }

    }

  }

}