Google Sheets - how to trigger background color change of one or more cells by selecting a different cell?

Question

I am a total newbie at this and looking to make a colored visual study guide for a class I'm in. I'm interested in a way where if you select a certain cell, it causes another cell on the spreadsheet to be colored in.

For instance:

If I have clicked on cell A1, I want cell B3 to be colored red.
If I press directional key down and it brings me to cell A2, I want cell B3 to revert back to white and then cell B1 to be colored red.
If I press directional key down again and it brings me to cell A3, I want cell B1 to revert back to white and then both cells B2 and B3 colored red.

Thanks for the input!

Tanaike Tanaike · Accepted Answer · 2021-01-29T08:15:27

I believe your goal as follows.

If I have clicked on cell A1, I want cell B3 to be colored red.

If I press directional key down and it brings me to cell A2, I want cell B3 to revert back to white and then cell B1 to be colored red.

If I press directional key down again and it brings me to cell A3, I want cell B1 to revert back to white and then both cells B2 and B3 colored red.

You want to achieve above using Google Apps Script.

In this case, I thought that OnSelectionChange of the simple trigger can achieve your goal. And, when I saw your expected flow, I thought that the previous range might be required to be saved. When these are reflected to a sample script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the project. And, please reopen the Spreadsheet. By this, onOpen is run and the current cell is saved. And, when you select other cell except for "A1", and when you select "A1" for the cell "A1", the script is run. And then, when you press the key down, the script is run for the cell "A2", and when you press the key down again, the script is run for the cell "A3".

function onOpen() {
  PropertiesService.getScriptProperties().setProperty("prange", SpreadsheetApp.getActiveRange().getA1Notation());
}

function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const a1Notation = range.getA1Notation();
  const p = PropertiesService.getScriptProperties();
  const prange = p.getProperty("prange");
  const r = a1Notation == "A1" ? "B3" : prange == "A1" && a1Notation == "A2" ? "B1" : prange == "A2" && a1Notation == "A3" ? "B2:B3" : "";
  if (r != "") {
    sheet.getRange("B1:B3").clearFormat();
    sheet.getRange(r).setBackground("red");
  }
  p.setProperty("prange", a1Notation);
}

Result:

When above script is used, the following result is obtained.

Note:

In this script, the simple trigger is used. So when you directly run the script, an error occurs because the event object is not given. So please be careful this.

If you are not required to check the previous range, please modify above script as follows.

  function onSelectionChange(e) {
    const range = e.range;
    const sheet = range.getSheet();
    const a1Notation = range.getA1Notation();
    const r = a1Notation == "A1" ? "B3" : a1Notation == "A2" ? "B1" : a1Notation == "A3" ? "B2:B3" : "";
    if (r != "") {
      sheet.getRange("B1:B3").clearFormat();
      sheet.getRange(r).setBackground("red");
    }
  }

Reference:

onSelectionChange(e)