How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run

There have been a few instances in my work where I need to remove a button (more accurately, a button drawing) or and image from a Google Sheets tab once the associated script has been run.

Perhaps we just want the user to run a process on a Google Sheet workbook, just once but not more times. This would be a good case for removing the button or drawing after use.

Note: This tutorial expects that you know how to create a drawing or a button from the Google Sheets drawing tool. 

https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/

Create a button

To create a drawing go to Insert > Drawing. The drawing app will appear. You can select the button shape by selecting the shape icon > Shapes > and then select the button shape. Next, draw out the size of the shape. To add text to the shape simply double click the shape and the text cursor caret (|) will appear. Add your text and his save.

The code

We will keep this super simple. Go to Extensions > Apps Script to open the Google Apps Script editor.

Add the following code:

The script above is not intended as a standalone function to be incorporated into your own script, rather it is an example of how a button can be deleted. You more than likely be incorporating lines 7 to 12 into your script as a part of your own project.

Let’s continue with setting up your button drawing followed by a script breakdown.

Code Breakdown

On line 5, we call the current Google Sheet bound to this Google Apps Script project using the getActiveSpreadsheet() method on the SpreadsheetApp class. This will allow us to work with the spreadsheet.

Next, we select the sheet that we have our button in with the getSheetByName() method. For us, this is simply, “Sheet1”. This will allow us to now work in the selected sheet tab. Line 6

We can now call the getDrawing() method on the sheet object. This will return an array of all the available drawings on our sheet that we can loop through. Line 7

To find which of our drawings is the one we want to remove we can use the Javascript find() method to iterate through our array looking for any drawing that has the “deleteButton” action connected to it. On each iteration, we can call the getOnAction() method to get the name of the connected function, if one exists.

The find() method takes a callback function call as an argument. The first argument of the callback function is the current element in the array which, in our case, will be each drawing in the selected Google Sheet. The method returns the current item if there is a match or undefined if there isn’t a match in the array. Line 9

Once we have selected the correct drawing we can apply the remove() method on it to delete it from our Google Sheet.

Setup your button drawing.

Head to your button on your Google Sheet. Right-click it and select the vertical ellipses in the top right. A menu will appear. Select ‘Assign script’.

A dialogue box will pop up. For our example, we will add our function "deleteButton" here.

Run the script

If this is the first time you have run code in the current script you are working on then you will need to click the button once to authorise the code and then again to run the code to delete the button.

Code for Deleting and image

Images in Google Sheets are different from drawings. Images are uploaded or assigned to the Google Sheet. You can also assign your Google Apps Script project’s function to an image to be run when clicked in the same way you would an image.

In many cases, images can work better than drawings because the source image file is not destroyed when you remove the image from the sheet. This means that you can insert it again programmatically at a later time unlike with drawings.

Here’s the code:

Some differences to deleting drawings

Find and removing images are only a little different from removing drawings.

First we call the getImage() method on the selected sheet tab of the workbook.  Line 7

This will give us a list of images in the sheet tab that we can use the find JS method on to find our image with our assigned “deleteImg” function. Note here we use the getScript() method on our image this time. Line 9. 

Just like with the drawing object we can use the remove() method on our image.

The video

Possible uses

Below are a few ways I have implemented this script in my own projects.

1. Force click button twice to authorise a script on a new Google Sheet template. Generally, Leave a large Drawing with a message to click it once to authorise the script attached and then again to remove the drawing and display custom menus and unhide selected sheet tabs.
2. Collecting data one time from an external source. If we need a user to run code to collect data from an external source once as part of a larger project, then this might be a good option.
3. Remove as a result of an action on a sheet or script. If say, a user has sent the sheet for submission and a button might carry out actions that would significantly modify the sheet after it has been submitted then this button could be removed as a part of the submission process.

Conclusion

Deleting buttons, drawings and images connected to Google Apps Script functions can be pretty useful for certain tasks in Google Sheets.

If you want to see an example of deleting buttons in the wild, click on the link below (You might have a fun surprise).

Example Sheet

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

 

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Happy coding!

~Yagi