0

Is there any way to automatically delete a cell when you change tab? For example when you change from Sheet1 to Sheet2 it automatically deletes A1 cell from Sheet1 tab.

MrFrank
  • 59
  • 10
  • 1
    Take a look at [onSelectionChange()](https://developers.google.com/apps-script/guides/triggers) You may use e.range.getSheet().getName() in order to get the current sheet name but the event object does not contain a reference to the previous sheet. The contents of the event object: {"user":{"email":"","nickname":"},"authMode":"","source":{},"range":{"columnEnd":1,"columnStart":1,"rowEnd":1,"rowStart":1}} – Cooper Jun 23 '22 at 20:57
  • 1
    In your situation, I thought that this thread might be useful. https://stackoverflow.com/q/33890389 – Tanaike Jun 23 '22 at 22:20

1 Answers1

3

I believe your goal is as follows.

  • From Is there any way to automatically delete a cell when you change tab? For example when you change from Sheet1 to Sheet2 it automatically deletes A1 cell from Sheet1 tab., I understood that you want to clear a cell "A1" of "Sheet1" when the sheet is changed from "Sheet1" to "Sheet2".

In this case, how about the following sample script? In this sample script, I modified this sample script. In this case, onSelectionChange and PropertiesService are used for detecting the change of sheet.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, please confirm that 2 sheets of "Sheet1" and "Sheet1" are included in the Spreadsheet. When you use this script, please reopen Spreadsheet, and please set a value to the cell "A1" of "Sheet1" and then please change the sheet from "Sheet1" to "Sheet2". By this, the script is run.

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

function onSelectionChange(e) {
  const obj = [{ "from": "Sheet1", "to": "Sheet2", "clearCells": ["A1"] }]; // This object is created from your question.

  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const sheetName = range.getSheet().getSheetName();
  const t = obj.find(f => f.from == previousSheet && f.to == sheetName);
  if (sheetName != previousSheet && t) {
    e.source.getSheetByName(t.from).getRangeList(t.clearCells).clearContent();
  }
  prop.setProperty("previousSheet", sheetName);
}

Testing:

When this script is run, a cell "A1" of "Sheet1" is cleared as shown in the following image.

enter image description here

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165