0

When I open a hidden sheet by a link, the system shows me notification that it's hidden with Unhide button:

example

When I click on Unhide I want to execute a script. Is it possible?

Kirby
  • 2,847
  • 2
  • 32
  • 42
  • 1
    In the current stage, unfortunately, there are no methods for directly running the script by clicking "Unhide" button in your situation. So, I proposed a workaround. Could you please confirm it? If that was not useful for your situation, I apologize. – Tanaike Apr 13 '22 at 10:28
  • @Tanaike Yes, it could work. I was thinking about it. But it will just fire always this event... So, performance might suffer. Anyway, that's good workaround. I'll test it. Thanks! – Kirby Apr 13 '22 at 11:09
  • 1
    Thank you for replying. About `But it will just fire always this event`, it's yes. So I run the script using the if statement. In the current stage, as another workaround, I thought [this workaround](https://tanaikech.github.io/2021/12/16/pseudo-onedit-trigger-for-google-spreadsheet-without-simple-and-installable-triggers-using-google-apps-script/). But, in this case, it is required to use Javascript. So I proposed the method for using `onSelectionChange`. – Tanaike Apr 13 '22 at 11:45
  • 1
    If you want to restrict strongly the if statement, the cell range can be restricted. I think that this can be also added. And, for example, it can be also checked whether "Sheet2" is showing from the hidden condition when `onSelectionChange` is fired. – Tanaike Apr 13 '22 at 14:01

1 Answers1

2

Issue and workaround:

In the current stage, there are no methods for directly running the script by clicking "Unhide" button in your situation. So, in this case, it is required to use a workaround. In this workaround, onSelectionChange of the simple trigger is used. When onSelectionChange is used, the change of tab can be detected. Ref When "Undide" button is clicked, the tab is changed. This workaround uses this situation.

Usage:

1. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet.

function onOpen(e) {
  const prop = PropertiesService.getScriptProperties();
  const sheetName = e.range.getSheet().getSheetName();
  prop.setProperty("previousSheet", sheetName);
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const sheetName = range.getSheet().getSheetName();
  if (sheetName != previousSheet && range.getRichTextValue().getLinkUrl() != "") {

    // When the tab is changed, this script is run.
    range.setBackground("red");

  }
  prop.setProperty("previousSheet", sheetName);
}

2. Testing.

In order to use this script, please reopen Google Spreadsheet. By this, the tab name is stored to PropertiesServices. The detection of change of tab uses this information.

And, in this case, as a sample situation, it supposes that "Sheet2" is hidden. And, the cell link of "Sheet2" is put in "Sheet1". When this link is clicked, a dialog is opened. When "Unhied" button is clicked, the tab is changed. And this change of tab is detected by onSelectionChange. By this, the script can be run.

The demonstration is as follows.

enter image description here

Note:

  • Unfortunately, the simple trigger of onSelectionChange cannot use all methods of Google Apps Script. Ref Although I'm not sure about your script, I thought that this is an important point of this workaround.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165