Is it possible to trigger on cell selection in Google sheets script?

Question

I am trying to get a spreadsheet with a lot of columns to hide some of them based on the currently selected cell/column.

Example: Say columns C-R are grouped in 3's (C-D-E, F-G-H, ....). I only want to see the first column in each group unless a cell in the group is selected. If a cell in column F, G or H is selected I want to see these column.

|__A__|__B__|__C__|__F__|__G__|__H__|__I__|__L__|__M__|__P__|

Now I've got a script that can hide and show groups however I need to find a way to trigger the function. The closest I have found managed is to trigger onChange but this requires there to be a change in the cell, I want selection to be enough.

function onSelect(){
  code for cell check and showing and hiding columns here.  
}

Any ideas in how, one could accomplish such a trigger?

Yes, you can use the triggrer onEdit() and then say if on edit the cell edited is ... then ... — Kevkeev13, Nov 01 '19 at 16:53
You can't use the onEdit() for selecting a cell but you can use to to detect an actual edit of a cell. However, if you put check boxes in those cells then you can detect the cell with a single click. Take a look at this question: https://stackoverflow.com/questions/58662000/is-it-possible-to-use-simple-triggers-to-increment-a-cell-whenever-another-cell/58662442#58662442 — Cooper, Nov 01 '19 at 17:07
@Cooper, Thanks for the tip, the problem with that is that all my cells would contain data, so they can't be check boxes. I will just have to accept that this is not a feature that I can get with the current API. — Kajsa, Nov 04 '19 at 12:05
There are definitely some advantages to having a tool like Excel that resides on your computer that can be totally dedicated to your needs. On the other hand I also like the fact That google provides a wide variety of tools and APIs that allow me to integrate a lot of features into one unique package via Google app script. And I am willing to understand that there have to be limitations placed on certain aspects of that interface so that the even their computers can support everybody’s needs at the same time. I have been able to build several useful web apps that have greatly enhance my life. — Cooper, Nov 04 '19 at 12:17
You can use the new [onSelectionChange()](https://developers.google.com/apps-script/guides/triggers#onselectionchangee) event introduced in April 2020. Detail answer posted [here](https://stackoverflow.com/a/65904275/1437046) — shreyansp, Jan 27 '21 at 22:08
This question duplicates https://stackoverflow.com/questions/47919639/google-app-script-monitor-spreadsheet-selected-ranges/65904275#65904275 — shreyansp, Jan 27 '21 at 22:16

score 1 · Accepted Answer · answered Nov 04 '19 at 11:52

Currently, there is no trigger for when a cell is selected or clicked (something like an onClicked or onSelected function), as a workaround you could add to the groups you already have, one checkbox to the first row of every first column of your groups, then when they are pressed you can trigger the onEvent(e) to show the hidden columns.

to add a new first row, right-click on the '1' in the numbers column at the left and then select 'insert one above'

to add a checkbox, select the cell/cells you want, then click on Insert -> Tick Box

Now you can use the onEvent(e) like this:

function onEdit(e){
  // get the number of the column. E.g: C = 3
  Logger.log(e.range.getColumn());
  /* 
     Then you would be ready to apply the logic you made 
     about hiding and showing columns
  */
}

Thanks for the tip, but I think the built in expand/collapse button that appears above the column headers when you have groups is a nicer way of doing this. Maybe there will be an _onSelect_ trigger in a future version but until then I think that button will do. — Kajsa, Nov 04 '19 at 12:19

score 0 · Answer 2 · answered Nov 02 '19 at 01:48

This only as an alternative, first trigger, you copy and paste in the same cell in column c, then the next, you must the similar method, you must copy and paste in same cell in column that you will edit:

function onEdit()
{
  var spreadsheet = SpreadsheetApp.getActive();
  var mysheet=spreadsheet.getSheetByName('MainSheet');

  var ctRangeCount=(mysheet.getRange("T1").getColumn()-mysheet.getRange("C1").getColumn()+1)/3;
  for (a=0;a<ctRangeCount;a++)
  {
    mysheet.hideColumns(a*3+3+1,2);

    if(mysheet.getCurrentCell().getColumn()==a*3+3)
    {
      mysheet.showColumns(a*3+3+1,2);//hideColumns(a*3+3+1,2);
    }
  }
}

Is it possible to trigger on cell selection in Google sheets script?

2 Answers2