Disabling Buttons Put on Google Spreadsheet using Google Apps Script

Gists

Description

This is a sample script for disabling the buttons put on Google Spreadsheet using Google Apps Script.

When a script is run by clicking a button on Google Spreadsheet, there is the case that you don't want to make users run the script in duplicate. This sample script achieves this situation.

Demo

In this demonstration, 2 types of buttons are used. Those are the drawing and image, respectively. When the button is clicked, the worker of 10 seconds is run. You can see that after the button was clicked, even when the button is clicked again, the worker script is not run.

Sample script

In order to use this script, please copy and paste the following script. And please put 2 types or one type of button on the Spreadsheet. And, please set the function name. Then, when you click the button, the script is run.

// This is for the drawing object.
function run1() {
  const mainFunctionName = "run1"; // Function name of this function.
  const alertFunctionName = "alert";
  const drawings = SpreadsheetApp.getActiveSheet().getDrawings();
  const drawing = drawings.filter((e) => e.getOnAction() == mainFunctionName);
  if (drawing.length == 1) {
    drawing[0].setOnAction(alertFunctionName);
    SpreadsheetApp.flush();

    worker(); // This is the script you want to run.

    drawing[0].setOnAction(mainFunctionName);
  }
}

// This is for the image object.
function run2() {
  const mainFunctionName = "run2"; // Function name of this function.
  const alertFunctionName = "alert";
  const images = SpreadsheetApp.getActiveSheet().getImages();
  const image = images.filter((e) => e.getScript() == mainFunctionName);
  if (image.length == 1) {
    image[0].assignScript(alertFunctionName);
    SpreadsheetApp.flush();

    worker(); // This is the script you want to run.

    image[0].assignScript(mainFunctionName);
  }
}

// This is for showing an alert.
function alert() {
  SpreadsheetApp.getUi().alert("Now, the script is running.");
}

// Please put your script here.
// In this sample, wait of 10 seconds is used.
function worker() {
  Utilities.sleep(10000);
  SpreadsheetApp.getActiveSheet().appendRow(["Done"]);
}

Note:

  • This is a simple sample script. So please modify this for your actual situation.

References