Hey there, my lovely friends! Today, I’m going to spill the beans on how to execute a script in Google Sheets for custom and powerful functionalities.
Setting up the script
To execute a script in Google Sheets, you need to add it to your spreadsheet. Here’s how you can do that:
Step 1
Open the Extensions menu and choose “Apps Script” to access the script editor. If you haven’t switched to the new menu yet, you can find the script editor under the Tools menu.
Step 2
The script editor will open in a new browser tab. Remove the existing text from the editor area.
Step 3
Type or copy-paste the script you want to execute. If you don’t have a script ready yet, you can use the following code that writes “Hello, world” in the selected cell.
function helloWorld() {
const cell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
cell.setValue("Hello, world");
}Step 4
Add this tag to your code to ensure that the script only affects the current spreadsheet.
/**
* @OnlyCurrentDoc
*/Step 5
Give your script project a name by clicking on the “Untitled project” header and entering a descriptive name. For this example, let’s call the project “Hello, world”.
Step 6
Click the save button to save your script.
Step 7
The script is now ready to be executed.
Running your script
The first time you run a script in Google Sheets with a given Google account, you’ll need to authorize the code to run. Here’s how you can do that:
Step 1
In the script editor, make sure the script you want to execute is selected in the “Select function to run” dropdown, then click the “Run” button.
Step 2
Wait for the popup window to appear, then click “Review permissions”.
Step 3
Select the account under which you want to run the script.
Step 4
Review the permissions listed, then click “Allow” if you agree to grant those permissions to the script and trust the source.
Step 6
The script is now authorized. Click “Run” once again to execute the script and paste “Hello, world” into the active cell(s).
Executing a script from a button
It’s possible to run scripts directly from the spreadsheet. Here’s how you can do that:
Step 1
Open the “Insert” menu and choose “Drawing”.
Step 2
Use the tools in the drawing editor to create a button, then click “Save and close” to add it to your spreadsheet.
Step 3
Select the button and click the three dots that appear to open the menu, then choose “Assign script”.
Step 4
Type the exact name of the function you want to execute as it appears in the script editor. Then, click “OK”.
Step 5
You can now run the script by clicking on the button.
In summary
In this tutorial, I’ve shown you how to execute a script in Google Sheets to add custom functionalities to your spreadsheets. If you want to learn more, check out all the Google Sheets tutorials on Crawlan.com. Until next time, my friends, and enjoy your super spreadsheets!
