Are you ready to supercharge your Google Sheets experience? If you’re a beginner looking to unlock the true potential of your spreadsheets, then you’re in for a treat. Welcome to the world of Google Sheets Apps Script, where the magic happens right within your spreadsheets.
Imagine being able to automate repetitive tasks, create custom functions, and build interactive web apps, all without leaving the familiar confines of Google Sheets. With Google Sheets Apps Script, you can turn your ordinary spreadsheets into dynamic, efficient, and personalized tools that work exactly how you want them to.
This article will take you on an exciting journey through more than 20 practical and beginner-friendly Google Sheets Apps Script examples.
Whether you’re a spreadsheet enthusiast, a budding data analyst, or a small business owner seeking to streamline your processes, these examples will empower you to harness the full potential of Google Sheets Apps Script.
Throughout this article, we’ll explore a variety of use cases, from automating data entry to sending personalized emails and even creating custom menu options. Each example will showcase the versatility and power of Google Sheets Apps Script, demonstrating how you can make the most out of this hidden gem in the Google Sheets toolbox.
So, if you’re ready to dive into the world of Google Sheets Apps Script and embark on a journey of automation and customization, let’s get started.
What is Google Sheets Apps Script?
Google Sheets Apps Script is a powerful and versatile scripting language that allows you to automate tasks, enhance functionality, and extend the capabilities of your Google Sheets spreadsheets. Developed by Google, this scripting platform enables you to create custom functions, build interactive applications, and integrate external services, all from within the familiar environment of Google Sheets.
Automation Simplified
Imagine being able to automate repetitive tasks that eat up your valuable time. With Google Sheets Apps Script, you can eliminate manual data entry, updates, and calculations. By writing simple scripts, you can instruct Google Sheets to perform actions automatically, saving you effort and reducing the risk of errors.
Custom Functions for Advanced Calculations
Sometimes, the built-in functions of Google Sheets might not fulfill your specific requirements. Google Sheets Apps Script lets you create custom functions tailored to your needs. Whether you need to perform complex calculations, manipulate data, or generate reports, custom functions can significantly enhance your spreadsheet’s capabilities.
Interactive Web Applications within Spreadsheets
One of the most exciting features of Google Sheets Apps Script is its ability to turn your spreadsheet into an interactive web application. You can design user interfaces, create forms, and collect data from others, all within your spreadsheet. This opens up a world of possibilities for collecting surveys, managing inventory, or even creating basic web apps without any external tools.
Integration with Other Google Services and APIs
Google Sheets Apps Script allows you to seamlessly integrate your spreadsheet with other Google services like Gmail, Google Drive, and Google Calendar. Furthermore, you can leverage external APIs (Application Programming Interfaces) to fetch and manipulate data from external sources, enhancing the real-time information in your sheets.
Getting Started With Google Sheets Apps Script
Don’t worry if you’re new to coding – Google Sheets Apps Script is beginner-friendly. With a gentle learning curve, even those without programming experience can quickly grasp the basics. Google provides ample documentation, tutorials, and a script editor within Google Sheets, making it easy to start writing and testing your scripts.
In essence, Google Sheets Apps Script empowers you to transform your spreadsheets from static data containers into dynamic, efficient, and interactive tools. It’s a gateway to a world of automation, customization, and innovation that can streamline your workflow, boost your productivity, and bring your data to life. Whether you’re a spreadsheet enthusiast, a data analyst, or a business professional, Google Sheets Apps Script is a valuable skill that can unlock the full potential of your Google Sheets experience.
Example 1: Create a Custom Function With Google Sheets Apps Script
One of the simplest ways to make your Google Sheets even more amazing is by creating your own special functions using Google Scripts. You know how Google Sheets has lots of useful functions, right? Well, you can make your own too.
To find the ones everyone uses often, just click the Functions icon to see a list of available functions.
But guess what? Google Scripts lets you make your own special functions just the way you like them.
Imagine this: You have some numbers from a fancy thermometer at work, but they’re in Celsius. No problem. You can make your own special formula that changes Celsius to Fahrenheit. So, all you have to do is click once, and ta-da. All your numbers get magically converted.
Ready to create your very first special formula? It’s easy.
Step 1: Open Google Apps Script Editor
The first thing we need to do is launch the Google Apps script editor. To do that, head to the Extensions menu and select the option for Apps Script.
After selecting that option, a new window will launch, where you can now write your JavaScript code.
Here is what it looks like:
Step 2: Enter Your Custom Function Code
Now that we have launched the Apps Script editor, we need to type in our custom function code. Simply clear what you currently have and paste the following code window.
function CSTOFH (input) {
return input * 1.8 + 32;
}
Step 3: Save The Project
After entering the code for your custom function in the code window, simply go ahead and save the project. Also, you want to rename the project to something appropriate. For this example, we will save it as “CelciusConverter.”
Step 3: Test The Custom Function
With our custom function now entered and saved, we must test it to see whether it works. To do that, click a cell in your spreadsheet and type in the following formula:
=CSTOFH(32)
After typing in the above formula, as we showed you, simply hit Enter on your keyboard. Google Sheets should automatically generate the converted result in the selected cell.
Here is what ours looks like:
And that’s it. You can see how easy it is to create any custom function using Google Sheets Apps Script.
Example 2: Auto Generate Charts Using Google Sheets Apps Script
Did you know you can make charts automatically with Google Sheets Apps Script? It’s like a shortcut that saves you from manually creating charts in Google Sheets.
Stick with us, and we’ll guide you on how to make charts automatically using Google Sheets Apps Script. It’s easier than you think.
For this example, we will be using the following sample data.
Step 1: Launch Google Apps Script
Start by launching Google Sheets Apps Script. To do that, navigate to the Extensions menu and select the option for Apps Script.
Step 2: Enter The Code
After selecting Apps Script, Google Sheets will launch the Apps Script Editor. Simply clear what you have in the code Window and type in the following code:
function GradeChart(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheets()[0];
var gradechart = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(sheet.getRange('A1:B11'))
.setPosition(5, 5, 0, 0) .build(); sheet.insertChart(gradechart); }
Step 3: Save The Project And Run
Now that you are done with the code, simply go ahead and save the project, then run.
After running the code, Google Sheets should automatically generate a chart in our sample spreadsheet. Here, take a look at what ours looks like:
If you need to make reports often, like every week or month, this automatic chart function can save you heaps of time. It’s like having a helpful robot do the work for you.
Example 3: Create A Custom Menu Using Google Sheets Apps Script
What if you don’t want to keep opening the script to make the chart automatically? What if you want the chart to be just a click away in the Google Sheets menu? Well, guess what? You can totally do that.
To make your own menu, you just need to tell the spreadsheet to add your special menu every time you open it. It’s like telling it, “Hey, show this cool option in the menu.”
You do this by making an onOpen() function in the Script editor, right above the Gradechart function you made earlier. It’s kind of like giving your spreadsheet a little instruction manual. Cool, right?
Here is how to go about that.
Step 1: Launch Google Sheets Apps Script
Given that we’re working with the same sample data we used in the previous example, all that’s required is to navigate to the Extensions Menu and then opt for Apps Script. This action will launch the Apps Script editor.
Step 2: Update The Code In The GradeChart Function
Now that we have our Apps Script editor open, here’s what we’re going to do: we’ll go back to the GradeChart function we made before and make some changes to the code. To make these changes, click the code box where we first typed in the GradeChart function.
But here’s the trick – instead of adding the new code below the old one, this time, we’re going to put the new code right above the GradeChart function code. It’s like rearranging the pieces of a puzzle.
Here is the new code we will use for this example:
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{ name: 'Create Grade Chart...', functionName: 'GradeChart' }
];
spreadsheet.addMenu('Charts', menuItems);
}
Step 3: Save The Project And Run It
Last but not least, make sure to save your script and run it.
After that, just refresh your spreadsheet. Guess what? You’ll notice that your fresh new Menu choice appears exactly like how you named it in your script.
Give it a click, and there it is – your very own special function in the menu.
Here is what ours looks like:
From the screenshot above, you can clearly see that we have been able to add a new Menu option called Charts to our spreadsheet.
Now, let’s test that menu to see if it works.
Step 4: Test Our Newly Created Menu
To test our newly created menu called Charts, we first need to delete the chart we currently have in our spreadsheet.
With our old chart now deleted, all we need to do is head over to our recently created menu and select the option for Create Grade Chart.
In case you’re a bit confused, the video below should provide some clarification:
That’s how to create a custom menu in Google Sheets using the Apps Script. That wasn’t so difficult, was it?
Example 4: Send Automated Reports Using Google Sheets Apps Script
Here’s another cool trick you can use with Google Apps Script to simplify your work. Imagine this situation: you’re in charge of a big team and need to send out lots of emails about the same topic. Let’s say you did performance reviews for each team member and wrote your comments in a Google Spreadsheet.
Wouldn’t it be amazing if you could just click a button and have those comments automatically sent to all 50 or 60 employees simultaneously? No need to write each email one after the other. That’s the magic of Google Scripting.
Like the scripts we discussed earlier, you can create a special script for this too.
For this example, we will use the following sample data.
Now that we have our sample data nicely figured out, let’s get down to business.
Step 1: Launch Apps Script
Okay, let’s start with the basics. The first step is to open up Google Sheets Apps Script. This is where all the cool stuff takes place. To do this, just go to the Extensions Menu and pick the Apps Script option.
Step 2: Type In Your Send Email Code
With the Apps Script editor now launched, delete everything you currently have in the code window and paste the following code.
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 7; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 3)
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[2]; // Third column
var subject = "My review notes";
MailApp.sendEmail(emailAddress, subject, message);
}
}
Step 3: Save And Run
With our code now correctly entered, we only need to save the project and run it.
The magic in the script above happens row by row in your spreadsheet. It’s like a little helper that goes through each row and sends an email to the address in the second column. And guess what? The email message is what you typed into the third column.
The sendEmail function in Google Scripts is like a superhero power because it makes email stuff happen automatically. This means less work for you and more time saved.
This script is like a sneak peek into the incredible world of Google Apps Scripting. It’s like bringing Gmail and Google Sheets together to do stuff for you.
Now, let’s go to our email to see if these emails were automatically sent after running the script.
After going through our sent email, we can see that the script worked perfectly. How cool can that be?
Example 5: Create Navigation Menu In Spreadsheet Using Google Sheets Apps Script
When working with a Google Sheets spreadsheet with many sheets, it can be hard to easily find and open the right one.
In the spreadsheet below, you’ll see quite a few sheets, and you have to scroll through them individually.
Sadly, it’s not very user-friendly to locate sheets by scrolling sideways, and switching between sheets back and forth can also be a bit of a hassle.
Even though Google Sheets has an “All Sheets” menu that allows you to peruse the list of all the sheets in your spreadsheet, it isn’t well organized.
Thankfully, we can build a custom navigation menu to quickly find and open any spreadsheet we want.
Here is how to go about it
Step 1: Launch Apps Script in Google Sheets
Begin by launching Apps Script. Look for the “Extensions” option in the top menu. From the “Extensions” menu, select “Apps Script.” This will open the Apps Script editor, where you can write and manage your scripts.
Step 2: Enter your Custom Menu code
With our Apps Script editor launched, we only need to enter the following code in the code window. Here is the custom navigation menu code we will use for this example:
//@OnlyCurrentDoc
// Use a onOpen() simple trigger to create
// a custom menu.
function onOpen() {
var adminMenu = SpreadsheetApp.getUi().createMenu("Admin")
.addItem("HR", "hr")
.addItem("Legal", "legal")
.addItem("Facilities", "facilities")
.addItem("Executive", "executive")
.addItem("PR", "pr");
SpreadsheetApp.getUi().createMenu("Departments")
.addItem("Sales", "sales")
.addItem("Marketing", "marketing")
.addItem("Finance", "finance")
.addItem("Engineering", "engineering")
.addSubMenu(adminMenu)
.addToUi();
}
// Activate the sheet named sheetName in the spreadsheet.
function setActiveSpreadsheet(sheetName) {
SpreadsheetApp.getActive().getSheetByName(sheetName).activate();
}
// One function per menu item.
// One of these functions will be called when users select the
// corresponding menu item from the navigation menu.
// The function then activates the sheet that the user selected.
function sales() {
setActiveSpreadsheet("Sales");
}
function marketing() {
setActiveSpreadsheet("Marketing");
}
function finance() {
setActiveSpreadsheet("Finance");
}
function hr() {
setActiveSpreadsheet("HR");
}
function engineering() {
setActiveSpreadsheet("Engineering");
}
function legal() {
setActiveSpreadsheet("Legal");
}
function facilities() {
setActiveSpreadsheet("Facilities");
}
function executive() {
setActiveSpreadsheet("Executive");
}
function pr() {
setActiveSpreadsheet("PR");
}
Note: The code above does something unique: it makes a special menu with some choices tucked under a sub-menu named “Admin.” This setup helps people using the spreadsheet to locate and open specific sheets quickly. Plus, they can change between sheets without any tricky left-and-right scrolling. We also put comments in the code to help you grasp how it all comes together.
Step 3: Save the Project And Run
Finally, save the project by clicking on the floppy disc icon and then click on the Run icon to activate the script.
Step 4: Test The Script
Having saved our code and run it in our spreadsheet, let’s head back to our sheet to see if we can see any changes.
From the screenshot above, you can see that we were able to create a custom navigation menu that allows us to switch between different sheets seamlessly.
Example 6: Display Toast Notifications In Google Sheets Using Google Apps Script
Think of a toast as a little pop-up message that gives you helpful info without bothering you too much.
These messages show up briefly and then vanish on their own.
When using Google Sheets, these toast messages appear at the bottom-right part of your screen.
Let’s show you how to display a toast notification using Google Sheets Apps Script.
Step 1: Launch The Apps Script
First thing first, launch the Apps Script. You can do this by heading to the Extensions menu and selecting the option for apps script.
Step 2: Enter The Script
Now, wipe everything you currently have on the code window and replace it with the following code:
function toastMessageTitle() {
SpreadsheetApp.getActive().toast("Could not import data from the URL.", "⚠️ Error");
}
Step 3: Save The Project And Run
Now that we’ve put our script neatly into the code window, we can save the project and give it a go by running it.
Step 4: Test The Script
Now that we’ve finished doing all the steps we explained, let’s try out the script to see if it really did what we wanted. To check, we’ll go back to our spreadsheet and see if we notice the message on the screen.
If you did each step just like we showed you, your spreadsheet should now resemble something like this:
Example 7: Create Pop-Up Alert Messages In A Spreadsheet Using Google Sheets Apps Script
A pop-up alert message is like a little box that shows important stuff you need to see. It can also ask you if you’re sure about something before doing it. To make one of these pop-up alerts, you just have to write some code using Google Apps Script.
Don’t stress, it’s actually really easy. You only need a few lines of code to make the alert appear, and we will show you how to proceed.
Step 1: Open The Sheet Where You Want To Show The Pop-Up Alert
Start by opening the sheet where you want to show the pop-up alert.
Step 2: Launch Apps Script
With our sheet now opened, go to the Extensions menu and select the option for App Script.
Step 3: Type In Your Code
Now, wipe off everything you have in the code window and replace it with the following code:
function alertMessageOKButton() {
var result = SpreadsheetApp.getUi().alert("Alert message", SpreadsheetApp.getUi().ButtonSet.OK);
SpreadsheetApp.getActive().toast(result);
}
Step 4: Save The Project And Run The Script
Having entered our code correctly, we can save the project and run the script. After doing this, we should notice some changes in our sheet.
Here is what ours looks like:
Now, that’s how to create a pop-up alert in Google Sheets using Apps script. That was easy, right? We thought so too.
Example 8: How To Get Input From The User Using A Prompt Dialog
Did you know you can ask people for information using this pop-up in Google Sheets? Yup, and guess what? You can make this happen with the magic of Google Sheets Apps Script. Keep reading, and we’ll guide you through the steps to get user input using a prompt dialog in Google Sheets.
Here is how to go about it:
Step 1: Open The Sheet Where You Want To Create A Prompt Dialog
Start by opening the sheet where you want to create a prompt dialog in Google Sheets.
Step 2: Launch Apps Script
Done opening the sheet? Great. Now, navigate to the Extensions menu and select the option for Apps Script.
Step 3: Type Your Code
Now comes the exciting part. You’re going to write the code that makes the prompt dialog appear. Simply clear off what you currently have in the code window and replace it with the following code:
function displayPrompt() {
var ui = SpreadsheetApp.getUi();
var result = ui.prompt("Please enter your name");
//Get the button that the user pressed.
var button = result.getSelectedButton();
if (button === ui.Button.OK) {
Logger.log("The user clicked the [OK] button.");
Logger.log(result.getResponseText());
} else if (button === ui.Button.CLOSE) {
Logger.log("The user clicked the [X] button and closed the prompt dialog.");
}
}
Step 3: Saving Your Project And Running The Script
Now that you’ve written your code to create a prompt dialog, it’s time to save your work and see it in action.
Before you run your script, make sure your work is saved. Click on the floppy disk icon in the Apps Script editor’s toolbar. This ensures that your code is safely stored.
Now, look for the play button at the top of the editor. When you’re ready to see your prompt dialog in action, click this button. Your script will run, and the magic will happen.
If you followed all the steps exactly as we showed you above, your spreadsheet should look something like this:
Example 9: Create Buttons In Google Sheets To Run Custom Scripts
Do you know you can create buttons in Google Sheets that trigger customized actions?
This tutorial will walk you through creating a “Send Quote” button. Once clicked by the user, this button will execute a specific function written in Google Apps Script.
This functionality can prove immensely valuable in constructing straightforward applications right within the user interface of Google Sheets.
Let’s show you how you can do it:
Step 1: Create A Button In Google Sheets
There are two main ways to go about this. You can add a picture you want to work like a button, or craft the button yourself using a drawing tool.
For this tutorial, we would like to make our own buttons using the drawing tool because it allows us to quickly create the perfect button for any situation.
To do that, navigate to the insert menu and select the option for Drawing.
Step 2: Create The Button Using Shapes And Text
After selecting the option for Drawing, Google Sheets will automatically launch a drawing window, where you can quickly create your button using shapes and buttons. For this example, we will create a button using a square, along with the text “Send Quote.”
After creating your button, simply click on Save and Close. With this done, Google Sheets should add the button to your spreadsheet.
Here is what ours looks like:
Step 3: Create The Function Using Apps Script
Having created our button, we need to now create the function for the script we would like to assign to the button. To do that, head to the Extensions menu and select the option for Apps Script.
With the Apps Script editor now fully launched, delete what you currently have on the code window and replace it with the following code:
function sendQuote() {
SpreadsheetApp.getActive().toast("Sending quote.");
}
Finally, save the project by clicking on the floppy disc icon.
Step 3: Assign A Script To The Button
Now, it’s time for the next part: getting a special Google Apps Script ready for the button. When people click the button, this special action you create will happen. First, you’ll need to make this action using Google Apps Script and then link its name to the button.
To do that, click the three dots menu on the button you created and select the option for Assign Script:
Type in the name of the function you wish to execute and click OK. For our example, we will use sendQuote. Remember, only enter the function’s name – no parentheses or extra values. Currently, it’s not possible to send additional information to a function when it’s activated by clicking a button.
Step 4: Test Button
Give it a shot by clicking the button you made. Once you click, the special action you picked should start happening. If you followed the steps we discussed earlier, you’ll notice a little message in the bottom right corner of your spreadsheet.
Here, take a look at what ours looks like:
Example 10: Create Birthday Reminders In Google Sheets Using Apps Script
We are all really terrible at remembering dates, especially birthdays. And it makes us feel really sad when we forget them. But not anymore. Using Google Sheets Apps Script, we can now create birthday reminders about our friends and loved ones, ensuring we never miss the special birthdays of the people we care about.
We will use the following sample data to create birthday reminders in Google Sheets using Apps Script.
With our sample data nicely put together, let’s get down to business.
Step 1: Launch Apps Script
To begin, open Google Sheets Apps Script. You can do this by going to the Extensions menu and choosing the Apps Script option.
Step 2: Type In Your Code
When the Apps Script editor launches, you’ll notice a short function in the code window, delete that and replace it with the following code:
function main() {
// Load the sheet that contains the birthdays.
var sheet = SpreadsheetApp.getActive().getSheetByName("Birthdays");
// Get the last row in the sheet that has data.
var numRows = sheet.getLastRow();
// Load data in the first two columns from the second row till the last row.
// Remember: The first row has column headers so we don’t want to load it.
var range = sheet.getRange(2, 1, numRows - 1, 2).getValues();
// Use a for loop to process each row of data
for(var index in range) {
// For each row, get the person’s name and their birthday
var row = range[index];
var name = row[0];
var birthday = row[1];
// Check if the person’s birthday is today
if(isBirthdayToday(birthday)) {
//If yes, send an email reminder
emailReminder(name);
}
}
}
// Check if a person’s birthday is today
function isBirthdayToday(birthday) {
// If birthday is a string, convert it to date
if(typeof birthday === "string")
birthday = new Date(birthday);
var today = new Date();
if((today.getDate() === birthday.getDate()) &&
(today.getMonth() === birthday.getMonth())) {
return true;
} else {
return false;
}
}
// Function to send the email reminder
function emailReminder(name) {
var subject = "Birthday reminder: " + name;
var recipient = Session.getActiveUser().getEmail();
var body = "It is " + name + "'s birthday today.";
MailApp.sendEmail(recipient, subject, body);
}
Step 3: Save And Run
Once you’re done with the code, simply click the floppy disc icon to save the project and then hit the play button to Run the script.
If you did everything correctly, you should get an email reminder in your email, assuming you have someone on the spreadsheet whose birthday is today.
After saving the code and running the script, I received the following email informing me of my son’s birthday today.
Now, that’s to get birthday reminders using the Google Sheets Apps script.
Example 11: Create Star Ratings In Google Sheets Using Apps Script
Star rating systems are quite common on websites where users rate products or services. For instance, you might have encountered star ratings on sites like Amazon. Here, users share their thoughts by giving products a certain number of stars.
These star ratings aren’t just for websites, though. They can also come in handy when you want to collect and understand user feedback in spreadsheets. Instead of just showing a number, visualizing ratings with stars can be more intuitive.
But here’s the catch: Google Sheets doesn’t offer a direct way to display stars based on numerical ratings. No worries, though – we can create our own solution.
In this section, I’ll walk you through creating a custom function in Google Sheets. This function will let you show star ratings using Google Apps Script.
For this example, we will use the 5-star rating system where 0 is the lowest rating, and 5 is the highest rating.
Here is the sample data we will use:
But before we delve any further, let’s quickly talk about how this custom function we intend to create will work.
In this beginner-friendly tutorial, we will create a special function named STAR(). This function will do something cool: when you give it a number (let’s call it “val”), it’ll give you back a star rating that matches that number. Imagine it as a way to show how much people like something using stars (★).
Now, here’s the cool part. You can also tell the function to use white stars (☆) to fill up the empty space. By default, it won’t fill them. But if you say “fill is true,” it’ll always show five stars, using white ones if needed. For instance:
If the rating is 3 stars:
- If “fill” is true, it will show ★★★☆☆.
- If “fill” is false (which it is by default), it’ll just show ★★★.
Exciting, right? Let’s dive into the details and learn how to make this work.
Now that we understand the basics, let’s jump into creating the script for this function.
Step 1: Launch Google Sheets Apps Script
First things first, let’s fire up Google Sheets Apps Script. We can do that by navigating to the Extensions menu and selecting the options for Apps Script.
Step 2: Type In The Following Function
When the Apps Script editor launches, you’ll see a default function. Delete those and replace them with the following function:
/**
* Returns a star rating for a numeric value
* @param {4} val The value to generate the star rating for
* @param {true} fill optional - Fill with blank stars as needed
* @returns {string} A star rating for the value
* @customfunction
*/
function STAR(val, fill = false) {
// If val is an array, it means that
// an ARRAYFORMULA is being used.
if(Array.isArray(val))
return val.map(STAR);
// We have to round since half stars are not supported
val = Math.round(val);
// Only 5-star ratings are supported so
// val must be between 0 and 5.
if(val < 0 || val > 5)
return "N/A"
let returnVal = "";
// Add val number of stars
for(let i = 0; i < val; i++)
returnVal += "\u2605";
// If fill is true, add empty stars to get to 5 stars.
if(fill) {
for(let i = 0; i < 5 - val; i++)
returnVal += "\u2606";
}
return returnVal;
}
Step 3: Save The Project And Run The Script
Having entered your code correctly, you only need to click the floppy disc icon to save the project and then click the play button to run the script.
Step 4: Test The Function
With our custom function now created, let’s return to our spreadsheet to test it. For this example, we will use cell B3, so select that cell and enter the following formula:
=Star(1)
After typing in your formula, simply hit the Enter button on your keyboard, and Google Sheets will automatically generate the result in the selected cell.
Here, check out what ours looks like:
After repeating the process for the other cells, here is what our spreadsheet now looks like:
Example 12: Automatically Resize Columns In Google Sheets Using Apps Script
Want to learn how to automatically resize columns and rows in Google Sheets using Apps Script? Well, today is your lucky day. Follow us as we will take you through the step-by-step process of doing that.
Here is the sample data we will use for this example:
We will start by creating a script to resize columns.
Step 1: Launch Apps Script
Let’s begin by launching Google Sheets Apps Script. You can do that by navigating to the Extensions menu and selecting the Apps Script option.
Step 2: Enter Your Code
With the Apps Script editor now launched, click on the code window and delete the default function. With that done, replace it with the following code:
function autoResizeSingleColumn() {
SpreadsheetApp.getActiveSheet().autoResizeColumn(1);
}
Step 3: Save The Project And Run
Now that you’ve entered your code, look for the floppy disk icon. Click on it to save your project. Once you’ve done that, find the play button and click on it to run the script.
Now, let’s see if it works. If you look at column A on our spreadsheet before we run the script, you’ll notice that “Employee names” isn’t showing complete.
However, after running our script, you can clearly see that it now reads complete.
Example 13: Automatically Resize Rows In Google Sheets Using Apps Script
Now that you’ve learned how to automatically adjust column sizes using Google Sheets Apps Script, let’s take a quick look at how to do the same thing for rows using the same method.
We’ll use the same example data as in the previous instance.
Step 1: Launch Apps Script
Let’s start by opening Google Sheets Apps Script. To do this, go to the Extensions menu and choose the option that says Apps Script.
Step 2: Enter Your Code
Now that you have the Apps Script editor open, go ahead and click on the area where the code is shown. You can delete the function that’s already there. After you’ve done that, replace it with the code provided below:
function autoResizeMultipleRows() {
SpreadsheetApp.getActiveSheet().autoResizeRows(3,5);
}
Step 3: Save The Project And Run The Script
Now that you’ve typed your code, find the little icon that looks like a floppy disk. Click on it to save your project. Once you’ve saved it, find the button that looks like a triangle pointing to the right (play button) and click on it to make your script run.
After completing the execution, you should see some obvious changes in the sizes of the specified rows.
Example 14: Rename A Sheet In Google Sheets Using Apps Script
In this section, we will guide you on how to change the name of a sheet in a Google Sheets document using Apps Script. It’s quite simple, and the code you need to write is just a few lines.
Let’s assume we have a spreadsheet with the following name, as shown in the image below, and we want to rename it to Operations:
Here is how to go about it:
Step 1: Launch Apps Script
To get started, open up Google Sheets Apps Script. You can do this by going to the Extensions menu and choosing the option that says Apps Script.
Step 2: Enter The Following Code
Now that you’ve got the Apps Script editor open, go ahead and click inside the code window. You can delete the function that’s already there. Once you’ve done that, swap it out with the code provided below:
function renameSheet(currentName, newName) {
var sheetToRename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentName);
if (sheetToRename) {
sheetToRename.setName(newName);
Logger.log(currentName + ' sheet has been renamed to ' + newName);
} else {
Logger.log('Sheet ' + currentName + ' not found.');
}
}
// Rename "Engineering" sheet to "Operations"
renameSheet("Engineering", "Operations");
Step 3: Save the Project and Run
After you’ve put in your code, spot the little picture of a floppy disk. Give it a click to save what you’ve done. Once you’re all set with saving, hunt down the button that looks like a triangle pointing to the right (play button) and give it a click. This will start running your script.
After running the script, return to your spreadsheet and see if the name has changed.
Here is what ours looks like.
As shown in the screenshot above, it’s clear that we’ve successfully changed the name of our Sheet from “Engineering” to “Operations.”
Now you can see how effortless it is to rename a sheet using Google Sheets Apps Script.
Example 15: Find All Occurrences Of A Search Term In Google Sheets Using Apps Script
Are you trying to find specific words or data in your Google Sheets? The findAll() method is here to help. It’s like a magic tool that locates all the times a particular word or phrase appears in your sheet. Don’t worry, it’s not complicated at all – even beginners can do it. Let’s walk through the steps together:
For this example, we will use the following sample data.
Step 1: Launch The Apps Script Editor
Start off by navigating to the top menu on your Google Sheets. You’ll see a menu item called “Extensions.” Go ahead and click on it. After clicking “Extensions,” a dropdown menu will appear. Among the options, you’ll find “Apps Script.” Click on it.
Step 2: Write The Search Code
Write the Search Code In the Apps Script editor, you’ll see a blank area. Don’t worry, you don’t need to be a programmer for this. Just copy and paste the following code:
// This function finds cells that contain "A" and sets their background colors to yellow.
function highlightCellsContainingA() {
// Find cells that contain "A"
let ranges = SpreadsheetApp.getActive()
.createTextFinder("A")
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.findAll();
// Convert the TextFinder result to an array
ranges = ranges.map(function(range) {
return range;
});
// Set the background colors of those cells to yellow.
ranges.forEach(function (range) {
range.setBackground("yellow");
});
}
Step 3: Save The Project And Run
Once you’re done writing your search code, simply click on Save Project and select the play button to run the script.
Here is what our project now looks like after running the script.
From the image above, you can clearly see that we have been able to use Google Sheets Apps Script to find cells containing A. Not only that, we were able to highlight the cells for better clarity.
Example 16: Replace Occurrences Of A Search Term In Google Sheets Using Apps Script
Imagine you’ve entered a specific word or phrase all over your Google Sheet, and you suddenly realize you need to change it. Don’t worry, the replaceAll() method is here to save your day! This method lets you effortlessly replace all instances of a certain word or phrase with a new one.
For this example, we will use the same sample sheet we used for the previous example.
Here is how to go about it.
Step 1: Launch Google Sheets Apps Script
Begin by going to the upper part of your Google Sheets window, where the menus are. Look for “Extensions” in this top menu. When you do that, a list of choices will pop down. One of these choices is “Apps Script.” Click on “Apps Script.”
Step 2: Write The Replacement Code
Inside the Apps Script editor, there’s a space that’s empty and waiting. No need to be concerned – you don’t have to be a programmer. Simply copy and paste the code provided below:
// This function finds cells that contain "A" and replaces them with "F", while setting their background colors to yellow.
function replaceAndHighlight() {
// Find cells that contain "A"
let ranges = SpreadsheetApp.getActive()
.createTextFinder("A")
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.findAll();
// Replace "A" with "F" and set the background colors of those cells to yellow.
ranges.forEach(function (range) {
range.setValue(range.getValue().replace("A", "F"));
range.setBackground("yellow");
});
}
Step 3: Save The Project And Run The Script
After writing the replacement code, simply save the project and run the script.
If you followed the steps we discussed above, you should notice some obvious changes in your spreadsheet. Here, take a look at what ours looks like:
From the image above, you can see that we were able to replace A with F using the Google Sheets Apps Script.
Example 17: Hide A Sheet In Google Sheets Using Apps Script
Are you aware that you can actually hide a sheet in Google Sheets using App Script? Indeed. If you’re new to this concept, let’s guide you on how to do it:
For this example, we will use the following worksheet, which has two different sheets in it.
Here is how to do it
Step 1: Launch Google Sheets Apps Script
The first thing we need to do is launch the Google Sheets Apps Script editor. To do that, head to the Extensions menu and select the options for Apps Script.
Step 2: Write The code
Now, click on the code window and paste the following code
// This function hides a sheet with a specific name.
function hideSheet(sheetName) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
if (sheet) {
sheet.hideSheet();
Logger.log('Sheet "' + sheetName + '" is now hidden.');
} else {
Logger.log('Sheet "' + sheetName + '" not found.');
}
}
// To hide the sheet named "Student test scores", use this:
hideSheet("Student test scores");
Step 3: Save The Project and Run Script
Once you’re done typing the code. Simply save the project and click on the play icon to run the script.
Now, let’s head back to our sheet to see if the sheet we intended to hide has now been hidden.
After checking our spreadsheet, we see that the “Student test scores” sheet has now been hidden.
That’s how to hide a sheet using Google Sheets Apps Script.
Example 18: Show A Hidden Sheet In Google Sheets Using Apps Script
Having shown you how to hide a sheet using Google Sheets Apps Script, let’s quickly go over how you can show a hidden sheet using the same method.
If you look at the screenshot below, you can see that we have only one sheet named “Student Exam Scores.” And that’s because we have previously hidden the sheet for Student test scores in the previous example.
To show the hidden sheet, take the following actions:
Step 1: Launch Apps Script
Let’s begin by launching Google Sheets Apps Script. Start by visiting the Extensions menu, then choose the options for Apps Script.
Step 2: Write The Code
With the Apps Script editor now launched, click on the code window and paste the following code:
// This function toggles the visibility of a sheet with a specific name.
function toggleSheetVisibility(sheetName) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
if (sheet) {
if (sheet.isSheetHidden()) {
sheet.showSheet(); // Show the hidden sheet
Logger.log('Sheet "' + sheetName + '" is now visible.');
} else {
sheet.hideSheet(); // Hide the visible sheet
Logger.log('Sheet "' + sheetName + '" is now hidden.');
}
} else {
Logger.log('Sheet "' + sheetName + '" not found.');
}
}
// To toggle the visibility of the sheet named "Student test scores", use this:
toggleSheetVisibility("Student test scores");
Step 3: Save The Script And Run
Now that you’ve typed in your code correctly, just save the project and run the script – that’s all there is to it.
Once you’re done saving the project and running the script, the hidden script should now be visible. Here is what our spreadsheet now looks like:
From the screenshot above, you can see that the previously hidden script, “Student test scores,” is now visible.
Example 19: Insert an image into a cell in Google Sheets using Apps Script
Now, you have the ability to add pictures to cells using Apps Script. The insertCellImage () function, which we will show you, does exactly that.
It needs a range and the image’s web address (URL). Then, it places the image into all the cells within that range. You can even provide an optional title and description for the image to make your spreadsheet content more accessible.
For this example, we will use the following image URL to insert the image into a cell in Google Sheets.
Here is how to go about it:
Step 1: Access The Spreadsheet Where You Want To Insert The Image
Start by opening the spreadsheet where you want to insert the image.
Step 2: Launch Apps Script
With your sheet now open, navigate to the Extensions menu and select the option for Apps Script. This action launches the Apps Script editor.
Step 3: Write The Code
Once the Apps Script editor launches, click the code window and type in the following code.
function insertImage() {
var imageUrl = "https://design.google/_next/image?url=https%3A%2F%2Fstorage.googleapis.com%2Fgd-prod%2Fimages%2Fa910d418-7123-4bc4-aa3b-ef7e25e74ae6.799a99c1196c2fd4.webp&w=3840&q=75";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange("A1");
// Insert the image
cell.insertImage(imageUrl, 1, 1);
}
Step 4: Save The Project And Run The Script
Finally, save the project and run the script.
After running the script, head to your spreadsheet to see if the image has been inserted.
Here is what ours looks like:
From the screenshot above, you can clearly see that the image has now been added to cell A.
Example 20: Add Links To A Cell In Google Sheets Using Apps Script
In this section, you’ll discover how to put links into cells in Google Sheets using Google Apps Script. When adding links, you can choose if the whole text in the cell should become a link or just part of it.
Follow us, and let’s go over the details together. Remember, we will use the same sample sheet as the previous example.
Step 1: Launch Google Sheets Apps Script
Let’s kick off by launching the Google Sheets Apps script. We can do that by visiting the Extensions menu and selecting the option for Apps script.
Step 2: Write The Code
Once the Apps Script editor launches, click on the code window and type in the following code:
function linkCellContents() {
var range = SpreadsheetApp.getActive().getRange("Link!A2");
var richValue = SpreadsheetApp.newRichTextValue()
.setText("More information")
.setLinkUrl("https://design.google/_next/image?url=https%3A%2F%2Fstorage.googleapis.com%2Fgd-prod%2Fimages%2Fa910d418-7123-4bc4-aa3b-ef7e25e74ae6.799a99c1196c2fd4.webp&w=3840&q=75")
.build();
range.setRichTextValue(richValue);
}
Step 3: Save The Project And Run The Script
Done writing the code? Great. Now, save the project and run the script.
Here, take a look at what our spreadsheet looks like after running the script:
From the screenshot above, you can see that we have been able to insert a link into cell A2. Now, you can see how easy it is to insert a link into a cell using the Google Sheets Apps script.
Final Thoughts
Google Sheets Apps Script is a powerful scripting language that can be used to automate tasks, create custom functions, and build interactive web applications. It is a beginner-friendly language that can be learned by anyone with basic programming skills.
In this article, we have explored some of the many ways that Google Sheets Apps Script can be used to improve your spreadsheets. We have seen how you can use it to:
- Create custom functions to perform complex calculations
- Automate repetitive tasks, such as data entry and email sending
- Build interactive web applications
- Integrate your spreadsheets with other Google services
We have also provided some beginner-friendly examples to help you get started with Google Sheets Apps Script.
If you are interested in learning more about Google Sheets Apps Script, there are many resources available online. Google provides a comprehensive documentation website, as well as a number of tutorials and blog posts. There are also many third-party websites and books that can teach you how to use Google Sheets Apps Script.
With a little effort, you can use Google Sheets Apps Script to transform your spreadsheets into powerful tools that can help you automate your work, improve your productivity, and make better decisions.
Here are some additional tips for getting started with Google Sheets Apps Script:
- Start small and simple. Don’t try to learn everything at once. Start by creating a simple custom function or automating a simple task.
- Use the built-in documentation. Google provides comprehensive documentation for Google Sheets Apps Script. This documentation is a great resource for learning the basics of the language.
- Find a mentor or community. There are many online communities where you can ask questions and get help with Google Sheets Apps Script. This can be a great way to learn from others and get support when you need it.
With a little effort, you can learn to use Google Sheets Apps Script to automate your work, improve your productivity, and make better decisions.