onOpen on specific Sheet in AppScript

1
votes

I have an onOpen execution code but I want it to execute only when a specific sheet is opened (For example on opening Sheet1 the onOpen function should run.) Does anyone know how to achieve this?

Here is what exactly I want to achieve:

  1. On open of the spreadsheet, check if the active sheet is sheet1. In case it is then execute a condition within that sheet, as shown below:
onOpen()
{
    //If the active sheet is "Sheet1" then perform this:
    //Sheet Execution comes here.
}

But here is the problem with above statement:

onOpen will only check once the sheet is opened. I want to check it every time the active sheet is "Sheet1". So for example, initially the user opens the spreadsheet, they land to "Sheet1" Now the condition is checked and output is displayed, but what if user changes the tab to say "Sheet2" and then comes back to "Sheet1". In that case onOpen() won't execute. I want to perform my check everytime the user lands or switches to "Sheet1".

Now here's another scenario:

using onSelectionChange:

onSelectionCHange()
{
    //If the active sheet is "Sheet1" then perform this:
    //Sheet Execution comes here.
}

Now this will work everytime user switches to "Sheet1", but here's the problem, even if user switches to different cells in "Sheet1" onSelectionChange will be triggered, which is again something undesired. All I want is to only trigger the function each time the tab/sheet is changed to "Sheet1" and not only when the spreadsheet is opened or any selection cell is changed.

1
google-apps-scriptgoogle-sheets

1 Answers

3
votes

Explanation:

To execute a block of code inside the onOpen function only when you open the spreadsheet file from Sheet1 then you can construct the following if condition:

function onOpen(e) {
const as = e.source.getActiveSheet().getName();
  if (as == 'Sheet1'){
//code you want to be executed when you open the spreadsheet from Sheet1
}
}

Drawback:

The onOpen function is only executed when you open the spreadsheet file. You can't just switch between sheets and expect onOpen to work.

Solution:

To execute a code when switching between different sheets you can instead use onSelectionChange(e). The following example sets the value of the cell A1 of Sheet1 when you switch from a different sheet to Sheet1:

function onSelectionChange(e) {
  
const as = e.source.getActiveSheet();
  if (as.getName() == 'Sheet1'){ 
    as.getRange('A1').setValue("This is Sheet1");
  }
}

And here is a general code template to execute a code when switching to Sheet1:

  function onSelectionChange(e) {
      
    const as = e.source.getActiveSheet();
      if (as.getName() == 'Sheet1'){ 
        // put your code here
      }
    }

References: