CODEWITHSUNDEEP
google-apps-scripttriggers
Kajsa
Kajsa

Reputation: 419

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

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?

Upvotes: 1

Views: 4914

Answers (2)

alberto vielma
alberto vielma

Reputation: 2342

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
  */
}

Upvotes: 1

user11982798
user11982798

Reputation: 1908

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);
    }
  }
}

Upvotes: 0

Related Questions