How to Use Google Apps Script to Trigger Functions from Image Buttons

The Usability Problem with Apps Script

You’ve written a brilliant Google Apps Script that automatically formats a spreadsheet, generates a PDF, and emails it to the accounting department. The problem is execution.

To run the script, a user has to click on “Extensions,” then “Apps Script,” wait for the IDE to load, select the correct function from the dropdown menu, and hit “Run.” For non-technical team members, this is intimidating and prone to error.

The most elegant solution is to create a graphical “Macro Button” directly inside the Google Sheet. You can draw a shape, label it “Generate Report,” and link it directly to your script. The user simply clicks the button, and the automation runs flawlessly in the background.

Step 1: Write the Function

First, you need a function to trigger. If you don’t have one, let’s create a simple one.

1. Open your Google Sheet.
2. Click Extensions > Apps Script.
3. Paste the following code:

function sendReportAlert() {
  var ui = SpreadsheetApp.getUi();
  ui.alert("Success", "The monthly report has been generated and emailed.", ui.ButtonSet.OK);
}

This script simply pops up a professional alert box in the center of the screen. Click the Save icon and return to your Google Sheet.

Step 2: Create the Visual Button

Google Sheets doesn’t have a native “Button” tool, but it has a built-in drawing engine that works perfectly.

1. Click Insert > Drawing. A blank canvas will appear.
2. Click the Shape icon (the circle overlapping a square) and select Shapes > Rounded Rectangle.
3. Draw the rectangle on the canvas.
4. Double-click inside the rectangle and type your button text (e.g., “Run Report”).
5. Use the formatting toolbar at the top to make it look like a button: center the text, make it bold, change the fill color to a vibrant blue, and change the text color to white.
6. Click the blue Save and Close button in the top right.

Step 3: Assign the Script

Your new button is now sitting on top of your spreadsheet grid. You can click and drag it anywhere you like.

To make it functional:

1. Click once on the button to select it.
2. Click the three vertical dots (the menu icon) in the top right corner of the button.
3. Select Assign script.
4. A small text box will appear. Type the exact name of your function from Step 1. (In our example, type: sendReportAlert). Do not include the parentheses ().
5. Click OK.

Step 4: Execute and Authorize

Click your button.

The very first time you click it, Google will throw a “Authorization Required” popup. This is a security feature to ensure scripts don’t run maliciously.

1. Click Continue.
2. Select your Google account.
3. Click Advanced, and then Go to [Project Name] (unsafe).
4. Click Allow.

This authorization only happens once. Now, click your button a second time. The script will execute instantly, and your custom alert box will pop up on the screen.

Conclusion

By hiding complex Apps Script code behind simple, graphical buttons, you can transform a chaotic spreadsheet into an intuitive, user-friendly software application. This approach drastically reduces the training time required for team members and ensures that your custom automations are utilized effectively.