Switching Buttons for Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet / button

Gists

These are the sample scripts for achieving the switching buttons for Google Spreadsheet using Google Apps Script. The management of images using Spreadsheet service is growing now. But, in the current stage, in order to achieve the switching buttons, it needs a little ingenuity. In this report, I would like to introduce 4 kinds of the switching buttons.

Pattern 1

In this pattern, the drawing is used as the button.

When the drawing is used as the button, in this case, 2 drawings are prepared and both drawings are overwrapped. Each button has the function of button1 and button2, respectively. When a button is clicked, the z index is replaced. By this, the switching button can be achieved. Because in the current stage, the image cannot be directly edited by the Google Apps Script.

Sample script

const button1 = () => switching("button1");
const button2 = () => switching("button2");

function switching(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const drawings = sheet.getDrawings();
  drawings.forEach((d) => d.setZIndex(d.getOnAction() == name ? 0 : 1));
  const temp = ss.insertSheet();
  SpreadsheetApp.flush();
  ss.deleteSheet(temp);
  sheet.activate();
}

Note

  • In this case, in order to refresh the change of z index of drawings, it is required to change the focus to other sheet. By this, when the button is clicked, the button is flashing in a moment. About this, I would like to expect the future update.

Pattern 2

In this pattern, the drawing is used as the button.

When the drawing is used as the button, in this case, 2 drawings are prepared, and one button is put to the cell “C3” and another one is other position in the same sheet. Each button has the function of button1 and button2, respectively. When a button is clicked, the clicked button is replaced with the button of the another position. By this, the switching button can be achieved.

Sample script

const button1 = () => switching("button1");
const button2 = () => switching("button2");

function switching(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const drawings = sheet.getDrawings();
  if (drawings[0].getOnAction() == name) {
    drawings[1].setPosition(3, 3, 0, 0);
    drawings[0].setPosition(10, 5, 0, 0);
  } else {
    drawings[0].setPosition(3, 3, 0, 0);
    drawings[1].setPosition(10, 5, 0, 0);
  }
}

Note

  • In this case, it is not required to refresh by focusing to other sheet. But, another button is required to be put to the same sheet.

Pattern 3

In this pattern, the cell is used as the button.

When the cell is used as the button, in this case, the cell “C3” is used. And the OnSelectionChange event trigger is used for executing the script.

Sample script

function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const a1Notation = range.getA1Notation();
  if (a1Notation == "C3") {
    range.setBackground(
      range.getBackground() == "#0000ff" ? "#ff0000" : "#0000ff"
    );
    sheet.getRange("A1").activate();
  }
}

Note

  • In this case, in order to select the button again, it is required to change the selected cell. This is the current specification.

Pattern 4

In this pattern, the checkbox is used as the button.

When the checkbox is used as the button, unfortunately, in this case, the checkbox is put to the cell “C3”. And the OnEdit event trigger is used for executing the script.

Sample script

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const a1Notation = range.getA1Notation();
  if (a1Notation == "C3") {
    range.setBackground(
      range.getBackground() == "#0000ff" ? "#ff0000" : "#0000ff"
    );
  }
}

Note

  • In this case, in order to use the checkbox, the text cannot be directly used. But, I think that the switching speed is fastest in these 3 patterns.

Note

  • These are the simple sample scripts. So please modify them for your actual situation.

References

 Share!