Topics > Google Apps Script - Custom Functions in Google Sheets

106 Tutorials

Google Sheets Apps Script: Automate Your Business with Custom Functions and New Features.

Google Sheets is a powerful tool that can help you organize, analyze, and share data. One of the best things about Sheets is that it allows you to automate repetitive tasks and create custom functions that can help you work more efficiently. Google Sheets Apps Script is the key to unlocking this potential. In this article, we'll explore the features of Apps Script, show you examples of what it can do, and teach you how to get started coding with it.

What is Google Sheets Apps Script?

Google Sheets Apps Script is a scripting language based on JavaScript that allows you to automate tasks, create custom functions, and interact with other Google services such as Drive, Calendar, and Gmail. Apps Script is built into Google Sheets, so you don't need to download or install anything to use it. All you need is a Google account and a spreadsheet.

How does Apps Script work?
Apps Script allows you to write scripts that can interact with your Google Sheets data. You can create custom functions that can be used like built-in functions, add custom menus and dialogs, and even send emails directly from your spreadsheet. The possibilities are endless, and Apps Script makes it easy to automate repetitive tasks and streamline your workflow.

What are the benefits of using Apps Script?
Apps Script can help you work more efficiently and save time by automating repetitive tasks. It allows you to create custom functions that can simplify complex calculations or data analysis. It also allows you to integrate your spreadsheet with other Google services, such as Drive, Calendar, and Gmail. With Apps Script, you can customize your spreadsheet to suit your needs and create new features that are not available in the standard Google Sheets interface.

See Examples of Apps Script
Apps Script can be used for a wide variety of tasks, from simple data formatting to complex data analysis. In this section of Better Sheets you'll be able to see tons of examples of Apps Script.

And not only see them but you'll be able to make a copy into your Google Drive. All Better Sheets members can copy the sheet from the tutorial directly into their Google Drive account.

What Apps Script Can Do

Create custom functions
With Apps Script, you can create custom functions that can be used like built-in functions in Google Sheets. For example, you can create a custom function that calculates the average of a range of cells, or a function that generates random numbers.

Automate repetitive tasks
Apps Script allows you to automate repetitive tasks, such as formatting data or sending emails. For example, you can create a script that automatically sends an email reminder to your team members when a task is due.

Integrate with other Google services
Apps Script allows you to interact with other Google services, such as Drive, Calendar, and Gmail. For example, you can create a script that saves a copy of your spreadsheet to Google Drive or creates a Google Calendar event based on data in your spreadsheet.

Create custom menus and dialogs
Apps Script allows you to create custom menus and dialogs in your spreadsheet. For example, you can create a custom menu that runs a script when a user clicks on it, or a dialog box that allows users to input data.

Learn How to Code with Apps Script

Spreadsheet Automation 101
Spreadsheet Automation 101 is the best way to learn the most in the least amount of time. It's 3 hours of programming jargon explained, Apps Script examples and walkthroughs.

Intro Crash Course
You can get a crash course in Apps Script if you want something fast and fleeting.  Learn to Code in Google Sheets is an intro to scripting. It's 40 minutes and gives you a taste of what's to come.

Advanced AI
And if you're looking for something more advanced, take the Adventures in AI course to see how to code apps script to use AI directly inside your Google Sheet.

All these courses are free for Better Sheets lifetime members. Check out our current prices at BetterSheets.co/pricing and join today.

106Tutorials

Learn how to debug a For Loop. These are common errors that happen when coding in Apps Script, specifically around the For Loop. This is Part 1 and 2. Get the goal of capture the flag exercise and I give you hints to each one.
Show a sheet or tab based on a code being entered in one sheet.
Create a cool function that brightens your sheet background from white to some specific color  a little bit each time you check off a to-do item.
This tutorial showcases a practical solution to convert visual cues into actionable data in Google Sheets. It's a step-by-step guide on using Google Apps Script to automate the process of reading background colors and converting them into text. The focus is on simplifying data processing tasks and enhancing the utility of spreadsheets for data analysis.
You can now generate 100% off unique 1 time use Gumroad Coupon codes from a Google Sheet.
It's $30, available now.
Lifetime members get it for free
https://bettersheets.co/tools/coupon-code-maker

What does Coupon Code Maker do?
Coupon Code Maker is designed for generating coupon codes, particularly useful for sellers on Gumroad. The tool, simplifies the process of creating large quantities of unique, single-use coupon codes.

The tool leverages Google Apps Script for its functionality, indicating a strong reliance on automation and API interactions. It's particularly noted for its ability to generate a high volume of codes quickly and efficiently, which can be exported as CSV files for use on platforms like AppSumo and others. This is important for marketing and selling products effectively on various online marketplaces.

There will be continuous updates to Coupon Code Maker. Get it now for a 1-time payment and you'll get all future upgrades for free.

If you're wondering how to build something like this check out Spreadsheet Automation 101 and Supercharge Your Sheets, two courses available for monthly members and lifetime members alike.
Create a sheet that can trash itself. It can delete itself instantly or on a timer. Maybe in 24 hours or a month.
Build a simple function that deletes undone items in our checklist.
Create a prompt to OpenAI with an array of cells
How to leverage Apps Script to create an API in less than 20 minutes. Create a license key for use in your own apps or as a cloud based API service. We'll create the API using Apps Script and the front end system using a basic Carrd page. Also I'll show you how to test your API in Postman, a free api checker web app.
Learn how to add random movie quotes and famous quotes to a Google Sheet. We'll use RapidAPI to get the code and just have a little bit of editing to the code to be able to use it in Google Sheets.
Curated sites, articles, and more web resources super easily into a Google Sheet. Create a bookmarklet automatically with this apps script tool.
Get your spreadsheet to check off your checklist for you.

Dive into spreadsheet automation with this easy to follow script, or copy the sheet and use the script as you wish.
Automatically uncheck a whole range of checkboxes. Or just do it yourself with the spacebar.
In this course we'll go step by excruciating step how to take a simple apps script from a bound script to your spreadsheet and turn it into a published Google Sheets add-on available to the public on the Google Workspace Marketplace.
Create a short function that runs every time a spreadsheet file is open. Redirects the user to the START tab. You can use this to direct anyone opening your file to a specific tab you want them to start on.
Create an automatic unchecker for a daily checklist. Fun way to learn Apps Script and simple Google Sheets automation.
By the end of this video you should be knowledgeable about how to automate spreadsheets. I'm assuming you have no knowledge of how to automate or how to code and so we're gonna start with a very blank page
Create an Email form that sends a few books to AI and gets back a book recommendation. Build the backend in Google Sheets Apps Script while using Carrd to easily make an online form.
How to use Google Sheets as a database by turning it into a REST API with Google Apps Script. This makes Google Sheets a powerful tool for managing data online, allowing for easy data entry and retrieval without manual sharing or updating.

This allows you to add and get data from the sheet over the internet without sharing the sheet. It shows how to:
  • Write code in Google Sheets that listens for web requests.
  • Use doGet and doPost functions to handle these requests.
  • Insert data into the sheet and fetch a summary of this data through the web.
  • Deploy the script as a web app to get a URL for accessing the API.
  • Test the API with tools like Postman or embed it in a web form.


I will introduce you to the power of AI writing in Google Sheets using a tool called Shaman. If you're a blogger looking to take your writing to the next level, this is the video for you. With Shaman, you can unleash your blogging potential by incorporating AI into your writing process.
A master class on merging cells. How to merge two cells. How to merge multiple cells. How to merge horizontally, vertically, and why.  Create groups. Create headers. And how to merge automatically. Members get the sheet and the apps script down below.
The video tutorial demonstrates how to leverage Google Sheets and Google Apps Script to create a simple API for managing and checking the status of jobs or projects. This is particularly useful for project or product managers who need a straightforward system for tracking job statuses without exposing their entire Google Sheet to external users. The process involves:

  1. Setting up a Google Sheet as a database: The sheet contains job IDs and their corresponding statuses. This setup acts as a backend where job statuses can be updated.
  2. Creating an API with Google Apps Script: By writing a custom script, a doGet function is deployed as a web app. This function allows users to query the Google Sheet by job ID via a URL, returning the job's current status.
  3. Deploying a web app: The script is deployed as a web application, which generates a unique URL. This URL acts as an endpoint for the API, where requests can be sent to check job statuses based on job IDs.
  4. Front-end form for checking job status: A form is created using an online tool (like CARRD) where users can input a job ID. Upon submission, the form sends a request to the API, retrieves the job status from the Google Sheet, and displays it to the user.
  5. Securing data visibility: This method ensures that only the relevant job status information is shared with external users, without revealing the entire content of the Google Sheet.
  6. Integration with other Sheets: The API can also be used in conjunction with other Google Sheets using the IMPORTDATA function, allowing for the automated retrieval of job statuses into other spreadsheets without manual data entry or revealing the original data source.

This approach is valuable for project management, allowing for real-time status checks of various jobs with minimal setup and without the need for complex database systems or advanced coding knowledge.
Welcome to the captivating world of Readable Hex Codes! In this video, we dive into the intriguing realm of colors represented by hex codes. You'll witness the magic unfold as we explore the delightful hues of "Coffee," "Decade," and many more. With a click, we'll reveal the true essence of these hex codes and bring them to life on a Google Sheet.
I'll show you how to set up a super powered checklist that can act like a micro manager. It emails you hourly with the top unchecked item on your To-Do list.
You're joining a company and they have some Google Sheets already created, and there's a sheet that has a cell in A1, this number, and you want to iterate it to 3, and B1 is connected to a lot of other stuff, so you don't want to mess with it. All you want to do is iterate a one, one at a time. I think the answer here is an Apps Script. 
Built a Domain Availability Checker in Google Sheets. You can build your own with the code provided. Use RapidAPI and Domainr.com to get an APIEKY for your own free checker.

Learn how to integrate sheets with a TLD Availability API.
We'll create a stop watch from scratch. Including Apps Script. What it will do is when you click a button (a checkbox) in a sheet, the script on every edit will add a start or a stop time, then add another time and calculate the duration.
Sit back and enjoy this comprehensive tutorial on how to create a live message in Google Sheets. In this video, we dive deep into the process of live message creation, providing step-by-step instructions and practical examples. Whether you're a beginner or an experienced user, this tutorial will empower you to unleash the full potential of Google Sheets.
Learn how to use apps script to add emoji animations when you complete a checklist in Google Sheets.
 I'm sharing a magical checklist that will make your sheets so darn amazing. We'll start with a to-do list on a Monday and check off tasks to see them move to a "done" list. I'll guide you through the process of creating an "on edit" function in Google Sheets using app scripts. By the end, you'll know how to archive completed tasks from your to-do list. Get ready to boost your productivity and stay motivated! 🚀
See how you can create your own API using Google Sheets' Web App feature. Deploy a web app and write doGet() to enable others to use an API you create yourself.
I will guide you through the process of creating an onboarding scheduler using Google Sheets and Google Apps Script.

We will automate the sending of emails and granting access to documents based on a schedule.

I will walk you through the script code step by step. By the end of the video, you will have a functional onboarding system that can send checklists and standard operating procedures (SOPS).
Access the OpenAI API, walkthrough of the way we engineer the Apps Script so that we can play around with temperature and token length. Walk through UrlFectchApp as well and parsing JSON.
Unlock the Power of OpenAI: Create Ultimate GPT Prompts for Seamless Interaction! Discover the Formula Inside, Explore APIs, and Boost Productivity. Watch Now!
 I will show you how to validate email addresses using the Mailboxlayer API and Google Sheets Apps Script.

I will guide you through the process of checking MX records, SMTP checks, and other cool features.

By the end of the video, you will be able to validate up to a thousand email addresses per month.

get your free apikey and 1,000 requests at mailboxlayer.com
Discover the Ultimate Google Sheets Hack! Transform Text with Title Case Formula. Say Goodbye to 'Proper' and Hello to Easy Capitalization. Find Out How! 
Upgrade to OpenAI's GPT 4 in your Google Sheets. And use GPT-3.5: Turbo for Powerful AI Prompt Calls! 

Learn how the new model enhances chat capabilities, including distinct messages and system prompts. Boost your productivity by integrating AI into your Google Sheets.
Learn how to use Formulas in Google Sheets. Use the Equal sign "=" to unlock a magical portal of over 500 functions and formulas. Learn one cool trick that even experts might not know about formulas.
'll show you a game-changing Google Sheet formula that will make your day, especially if you're into bulk publishing and chat GPT. With this formula, you'll be able to insert lists of keywords into prompts effortlessly. I'll guide you step-by-step on how to set it up and use it effectively. Get ready to become faster, better, and more efficient in your publishing tasks. Let's dive in!
If you have a list of tasks and statuses, the FILTER formula will help keep you from the brink of Overload by allowing you see each status in a different tab.

Great if you're assigning work to VAs or you just became overloaded with tasks in your own task management system.

A great alternative to Conditional Formatting as well.

And a sneaky new little Apps Script that saves the day.
Create the Ultimate Headline Writer: Unleash the Power of Google Sheets and OpenAI's API / GPT-3.5-Turbor to Create Compelling Website Headlines. Write in different styles, using different parameters to make the ultimate headline writer for an agency or for your own business.
If you type the letters of PRIDE in the first 5 columns in the first row of your sheets they'll turn the color of the pride flag. If you want to create your own easter egg in sheets then follow along this step-by-step tutorial as I show you how.

This might be better than Conditional Formatting! We're using the onEdit() simple trigger in Apps Script..
How to create a form programmatically using Google Apps Script. I show you step-by-step how to create a form, set the title, add text items, and set the destination of the form to a specific sheet. This tutorial is perfect for anyone who wants to automate the process of creating forms, whether it's for templates, lead magnets, or recurring forms. No prior coding experience is required.
Find out How to Optimize your Apps Script functions. Learn the trade offs between flexibility, edit-ability, and protection. Different cell references allow you to do more with your apps script.

In the context of the video we're talking about an AI prompt and how I used different Cell References when writing different Apps Script functions. I'll show you why I chose those syntaxes and why you might choose different ones.

This video is more about Apps Script than writing Prompts but can help you create better web apps inside of Google Sheet, utilizing the best of Apps Script and creating your own custom functions.
Rewrite YouTube Titles with GPT-3 API. Writes shorter, more persuasive titles I can use to increase views on YouTube. Code is included in Apps Script of sheet.
A short 10 minute video to get you started with Apps Script. Showing you menu: Extensions > Apps Script. And shows off how to write a function, do some math, manipulate some text. Members can get Spreadsheet Automation 101 in https://BetterSheets.co/courses
This is an attempt to create a waterfall effect in sheets. Where an AI response becomes the prompt in another ai API call.
I show you one way not to do it, that fails with onEdit() but then I do show one particular way it could work. We wrestle with AI a bit in this video and the conclusion is not so sweet.

Get the sheet below with the Apps Script and API code already written for you.
It's healthy programming. I'll show you some common errors you will encounter when you first start programming in Apps Script
I show you how I use a Google Form and a Google Sheet to power the testimonials page on BetterSheets. I walk you through the process of collecting testimonials, approving them, and displaying them on the website. I also explain how I implemented the functionality using Apps Script. If you're interested in learning about spreadsheet automation and how to leverage Google Forms and Sheets for your own projects, this video is for you!

And Spreadsheet Automation 101 is a great way to learn most of what I go over in this video.
Create a chat interface inside of Google Sheets using GPT-4. Insert rows as prompts get written. This sheet will help us ultimately write better prompts. We can actually tell the difference between what we're doing to get good vs bad response from the prompts.
Create a little web app, or a tool inside of Google Sheets that can take any text input and give us the correct case we want. It can do Title Case or Sentence Case. It can do UPPER and lower case as well.

I added a little apps script to it that will check only one of the options. Check out the Radio Button Apps Script in another video.
I need help to supercharge my YouTube channel! Are you struggling to find the best strategies to boost your YouTube channel's performance too? Let's see if Mr. Beast can help.


Continuing to show you how to write a function in Apps Script.
Introductng a new tool from Better Sheets. A free Google Sheets™️ Add-On to parse spin syntax content.

Revolutionize Your Google Sheets writing with Spin Tax Content Spinner! Say Goodbye to Tedious Editing. Get All the Variations You Need. 

  • Spin randomly to get one variation
  • Spin text for all variations
  • Spin text for all variations including duplicates

Spintax is written with pipes separating content you want to randomly select. {Hi|Hello|Hey} would either be Hi, or Hello, or Hey.
Create a ChatGPT prompt inside a sheet, edit that prompt with a single input 100 times over. In this case we're creating a twitter thread prompt and replacing the topic 100 times. so you can use 1 prompt to run 100 ai calls, and then we go further

We make it better.

We add a checkbox and another prompt to take the output of the 1st prompt and run it again. And we can do that 100 times too. So each horizontal row can be it's own ChatGPT.

You can get the ai Apps Script in this video's sheet or in the sheet connected to the GPT-4 upgrade: https://bettersheets.co/tutorials/upgrade-google-sheets-openai-prompt-to-gpt-4
I start off this video with the idea to show beginners how to automate emails in their sheets, and also add a little bit of AI. Then half way through the video I have a question... can I write AI and then email myself everyday? We try to do that. So you get to see me struggle, and log, and debug. It's quite intense.


Learn more Automation: https://bettersheets.co/courses/spreadsheet-automation-101

Get the AI GPT-3.5 and GPT 4 code: https://bettersheets.co/tutorials/upgrade-google-sheets-openai-prompt-to-gpt-4

Better Sheets members can get the code and the sheet in the video below.
Ever wanted to display some data from a google sheet on your site? Without using 3rd party tools here is the javascript, html and the sheet you will need. and step by step instructions on how to use it.
Create an epic writing tool to generate text from AI with a parade of prompts. A list of prompts you create and write that can be used over and over again. Or use this template to create your own workbook to sell a sheet of your own prompts.
Add labels to gmail emails you get from email addresses you put in a Google Sheet list. Great for tagging members, subscribers, users, when you want to prioritize them in your Gmail. Great to see a function laid out that interacts with Gmail inside of Google Sheets Apps Script.
SPOILER ALERT! In this video are the full solutions to the For Loop problems you can solve. If you haven't tried yet, copy the sheet and make an attempt to debug all 9 For Loops.
extending Google Sheets in a really fun way. We're gonna be sending emails. I have had literally two people in the past week and a half ask me for this and I answered their question very specifically, but I wanna generally reach out to everyone here, give everyone the opportunity to learn this.
Walk through step-by-step going from a blank sheet to an AI powered prompt engineering cheat sheet of a spreadsheet.

Watch the Upgrade to GPT-4 video to see the differences between GPT-3 and GPT-4 Apps Script

Create the Apps Script to create a new sheet from some data inside your sheet. Script included in the sheet below.
Learn how to automate spreadsheets with Apps Script. The pre-course videos equip you with the fundamentals of coding before you start.
Learn how to think like a programmer and develop a coding mindset with these expert tips from a seasoned Google Sheets programmer. Don't fall for the common, ineffective advice, and instead follow this practical advice, including learning the process, figuring out how to search, and avoiding memorization. With these habits, you'll be on the path to successful coding. 

It's hard to code. It's even harder to learn to code. This is my thought process to help you become resilient enough to learn, and continue to learn to code.
A raw, 1 shot take of figuring out how to automatically delete all sheets not named on a whitelist of sheet names. Using the on Change Installable trigger. This video was tough to make!
We fix the problem of OpenAI's API not remembering our last questions. And we label the text as Human and AI. I also add an archive Chat and new Chat function to create a new chat fresh.
In this video, I'm going to share with you how to go basically from sheet to script and then back to the sheet again.
A pretty cool script I came up with to click and set active the next tab. No matter how many tabs/sheets you have. Makes a book-like page turning action. At the end of the video I say you have to wait, but now you can get the sheet and the script only. Spreadsheet Alchemy is still a work in progress.
welcome to the technical walkthrough of VisiCalc 2023. This is the sheet that I released on April 1st for April Fool's Day. Learn how to use the onEdit() function in Apps Script. We learn the Apps Script for: Save a Sheet , Active Cell coloring, and the Out of Memory Toast.
Calculate Return on Ad Spend with a formula combination. Bonus: create a more elegant solution in Apps Script using JsDoc syntax to create an autocomplete native function. We use the formula combination of Concatenate / Round to create the ratio, but in Apps Script we do it a whole lot better.
Use the power of OpenAI's GPT-3 inside of Google Sheets. In this quick start tutorial I show you how to take OpenAI's curl commands and change them into Apps Script to use GPT-3 inside of sheets. We write a tweet, we ask questions. And now you can use this power for greater good!
Learn how to email yourself a cell from a Google sheet every day in just a few simple steps! This powerful technique will change the way you manage your data. Follow along and get ready to be amazed! 

Learn to automate an email, with data, from a sheet.
In this tutorial, learn how to create a ChatGPT-like interface using Google Sheets and Apps Script. Record your questions and get answers from OpenAI's GPT-3 API. Follow this step-by-step guide to build your own interactive GPT-3 interface.

  • Style a sheet like ChatGPT. Use the correct colors and interface design.
  • Add new rows to record the questions/prompts.
  • Add rows for the responses, and answers we get from OpenAI API.
Write an apps script to delete lines easily.
Unlock the Power of Email Notifications! 📧 Dive into our latest video tutorial, where we show you step-by-step how to add email notifications to your Google Sheets. Stay ahead of the game, get instant updates, and keep your workflow seamless! Check it out now! 
Embed a little HTML into your site to capture Emails straight to a Google Sheet. I've written the apps script and the html and the javascript for you. As easy as copy and pasting. Use my referral link if you choose to use Carrd for this: https://try.carrd.co/kamphey
Now that we have a script that we wanna run every day, how do we run this? This is a key part of programming. It's like Cron Jobs in Apps Script
Create a start time and end time and duration of that, with a little bit of Apps Script.
One of the most requested videos, Ever. How to put data, a number, some text, from a Google Sheets onto a website without having to embed the entire Google Sheet. This is the way!
ow do I show the date and time for the last time something was changed in a sheet? So what's interesting is that this This shows you Google Sheets shows an version history up here and shows you the last edit, but sometimes we want to have that data available to us inside of the sheet.
Get floor prices into your spreadsheet with URLFETCHAPP. Accessing OpenSea's API directly from google sheets. Get any data you want. from Market Cap to One Hour Volume
Learn how to manage any event with RSVPs and multiple organizers. Use Google Forms pre-filled links, and interesting formulas to manage the catering automatically. And we write Apps Script to send email to non-rsvp'ed guests.
Here are 5 advanced ways to manage projects inside of Google Sheets Manage Task Flow with IF() and a Checkbox Manage Data Inputs with IF() and ISBLANK() Create Quick Dashboard headers with: Transpose / Unique Create Pickers with Index / Match Email reports with Apps Script
this is gonna be fun because we get to create a calculator with Apps Script.
There was a stories craze. Every app/platform/business was adding 24 hour lasting videos to their users accounts. Now you can do that with Google sheets.
This is one of the most popular asked first "automations". How to move a row of data based on a single criteria in a column. Great for sales teams and maintaining a CRM.
Google Sheets uses a scripting language called Google Apps Script, which is based on JavaScript. If you know how to code in JavaScript, you'll find Google Apps Script very familiar. To get started, open a new Google Sheet and go to the "Extensions" menu, then click "Apps Script".
How to create a video checklist. Create a sheet with check boxes that you check off to mark the videos as seen. Great for those creating lead magnets or checklist digital products. Enables you to create a video course without having to download/upload any videos. Your users can keep track of their progress and get all the benefit of your knowledge.
Exclusive video only for Better Sheets members. This is, uh, troubleshooting your click tracking web app or slash sheet.
Editing macros in Google Sheets helps you do more in less time.
Enable your apps script functions be auto completed while you're using Google Sheets.
I walk you through everything you may encounter along the way of coding in Google Apps Script. Discover the challenges and pitfalls before you fail. If you’ve never coded before, I’m right there with you all the way.
revisiting the "what changed" custom function I built in a past video. Showing you more apps script
Learn how to track every change in your Google Sheets with a simple custom function in this video on "What Changed." Discover how to compare sentences, paragraphs, and more with ease. Follow along as we create the function from scratch. Get ready to streamline your editing process!

We code a little apps script to figure out the difference between two cells.

Revolutionary Method to Manage Your Video Stories! Learn how to delete old videos in just 24 hours. Keep your content fresh and engaging. Click here for the ultimate guide. 
Automate your apps script and macros you can record.
Let's code a Radio button that turns off all checkboxes except the one clicked.
Generate Coupon Codes with 5 Levels of Difficulty - From Copy-Pasting to Advanced Google Sheets Formulas. Get up to thousands of unique codes easily! Watch now for coupon mastery inside of Google Sheets.
Learn how to set up a Stripe web hook in a Google sheet and Stripe to track new customers, payments, and cancellations with ease! Get the script and code on GitHub, available for everyone. No need to be a Better Sheets member.