Disabling Buttons Put on Google Spreadsheet using Google Apps Script

submit.md

Disabling Buttons Put on Google Spreadsheet using Google Apps Script

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

• getDrawings()
• Class Drawing
• getImages()
• Class OverGridImage

Thank you!
I earlier thought of using Lock Service for preventing conflicts. This way is interesting for me as I was not aware of Drawing and Images classes.

@Max-Makhrov Thank you for your comment. I think that when the multiple clicks are run in order, the lock service is useful. In this case, the 2nd, 3rd and so on clicks are reserved during the script of 1st click is running. And, those are run in order after the 1st run.