Topics → Google Apps Script - Custom Functions in Google Sheets

Includes

190 Tutorials

Free for Members

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.

190 Tutorials

Solve The Errors, Learn For Loop

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.

Advanced Intermediate

Add Columns Automatically

Create your own "Add Columns" button just like the add rows to the bottom.

Intermediate

How To Format Sheets for Notes

Create sortable notes in Three ways.
1. By adding the original word over and over again
2. Adding notes all in one cell with CMD + ENTER for new lines
3. Use Apps Script to sort the A column even with groups of rows.

Get Every Editor on Every Google File I Own

Get every editor if you're looking for the sheets and documents you've shared with other people and forgot them! Or just want to get a list of them into a Google Sheet.

Create Navigation Buttons in Sheets to Other Sheets

Add a button to go to another sheet inside your sheet.

Intermediate

How to Copy To New Tab When Checked

Copy text from one tab to another based on a checkbox being checked.

Beginner

Email Myself a Daily Random Motivational Quote

Quickly make a daily email to myself with a motivational quote.

Beginner

Show Sheet Tabs Based on Edit

Show a sheet or tab based on a code being entered in one sheet.

Intermediate

Reformat Pasted Cells Automatically

Create uniform formatting even if you're copy and pasting data that is differently formatted.

Intermediate

Can I get alerted when a tab name is changed?

Find out in this quick experiment if a tab name can be alerted to the owner of the spreadsheet. Great for owners and operators who want to know if someone is changing the sheet.

Automatically Copy Spreadsheet Tab to New File with Values Only, No Formulas

Automatically copy a spreadsheet tab to a whole new spreadsheet file, get the url of that new sheet, and make sure the new file is values only. no formulas. A member asked this question and I'm happy to answer it, code it, and let you in on the answer in this short video.

Intermediate

How To Duplicate a Tab and Move it to the End in Google Sheets

Learn how to automate creating a template and moving the tab to the far right, or the end of the google sheets tabs.

Intermediate

Build a Startup Pitch Generator in 20 minutes

Build a fully functioning AI powered startup pitch generator in 20 minutes. I saw a tweet listing startup accelerators and thought it would be cool to build a tool that edits my own startup idea into a personalized pitch to each startup accelerator fund. Get it for your google drive down below
https://docs.google.com/spreadsheets/d/1KevSvdwpONP89eBOe-8fDfp2fGlBafE7BAtI5wgGe2U

Get the AI script here: https://bettersheets.co/snippets/updated-open-ai-model-to-omni

Intermediate

Scrape Google Maps

Scrape any query in Google Maps into your Google Sheets. Enter your API key and enter a query. Get results. Get your APIKEY from here: https://developers.google.com/maps/documentation/places/web-service/get-api-key?setupProd=enable

Intermediate

Scrape Google Maps Added Phone and Reviews

Added two helper functions to get Phone numbers and reviews of the Google Maps Results.

Every Google Drive File Created in Last 24 Hours

Create a simple report in a Google Sheet that documents every file in your Google Drive that was created in the last 24 hours.

Intermediate

How to Move Tab To End Easily with Apps Script

Create an apps script to move active tab to the far right.

Send Better Email When Google Form Filled Out

Find out how to email all the form entries each time your form is filled out.

Intermediate

How To Send Two Emails in Same Trigger

Without having to create two functions and two triggers you can combine two email sends into one function.

Intermediate

Count Merged Rows

Count the number of cells or rows in a merged cell. This is a quick little code you can add to your sheets with the onOpen code.

Intermediate

Advanced Coding in Google Sheets for Programmers

Add a Custom Menu
Create Native-like Functions
Get URL and Sheet ID's via Apps Script
A Few things to know about Developing Google Sheet Add-ons

Advanced Intermediate

Automated Project Management in Google Sheets

Create automatic notifications when a project is assigned, a project needs review. And move a project from one tab to another tab.

Intermediate

How to Insert Multiple Rows in Google Sheets (Easy to Hard)

Learn how to add multiple rows to your google sheets by hand, and automatically.

Advanced Beginner Intermediate

One Million Checkboxes

Check out this fun onEdit() counting you can do with checkboxes in a fun sheet with 1 Million Checkboxes.
Anyone can log into google sheets and go here: https://bettersheets.co/checkboxes

Beginner

How To Download All Tabs as CSV in a Zip File

Download All Tabs as CSV in a Zip File, instead of downloading each one by hand, one by one. This apps script is super awesome to implement for those sheets you need a zip file of all the Tabs.

Intermediate

Saving OpenAI API Calls inside of Google Sheets

Copy text output and input into a google sheet from your OpenAI API calls. Great way to save AI text into a sheet or start to create your own AI writer, or even clone ChatGPT

Intermediate

SheetLord Update: duplicate tab and automatically add Search Term

Updated SheetLord with a new function.

Intermediate

Self Destructing Google Sheet

Create a sheet that can trash itself. It can delete itself instantly or on a timer. Maybe in 24 hours or a month.

Intermediate

Automate Form Response to Confirmation Email

Create an email automatically when someone fills out a google form. include a url that has a clickable confirmation link and a unique id.

Advanced

Sync Many Tabs without ImportRange()

Sync more than two tabs with Apps Script

Advanced

Launching SheetOps The Business Operations Task Manager

Jump in and watch this quick run through of what is SheetOps. It's not just an automated task list, it's a business operations command center. Command the chaos! Get it at https://BetterSheets.co/tools/sheetops

Advanced Intermediate

Better BMI Calculator

Learn how to calculate BMI and create a custom BMI formula in Google Sheets! In this video, you'll explore the basic formula for BMI using weight and height, then dive into Google Apps Script to create a native BMI function. Perfect for anyone looking to enhance their spreadsheet with custom functionality.

Intermediate

Generate Coupon Codes from a Google Sheet - Announcing Coupon Code Maker

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.

Intermediate

Built a Cookie Clicker Clone in Google Sheets

I built a clone of Cookie Clicker, live on YouTube. Watch the full 1 hour live stream here.

In this video I update it a bit, and show off that it works!!

Learn how to build trigger builders and use user properties to store data you don't want a user to change (easily)

Advanced

Idea Graveyard - Create a Decaying Checklist

Build a simple function that deletes undone items in our checklist.

Intermediate

Brighten Up Your Checklist Automatically

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.

Intermediate

Automatically Check Past Stages

Create an automation that runs whenver we check off a checklist item the past stages also check.

Beginner

I Created an Automatic Emailer

While creating SheetOps, a new product, I came up with this idea of an automatic emailer that I can just enter an email and select a series of emails to send them. Needs an unsubscribe link. And so I made it in less than 45 minutes. Here it is. You can start using this right now for free if you're a member.

Advanced Intermediate

Proof of Life

Create a script that sends an email to ask for proof of life every month, or it will destroy the google sheet.

Advanced

Build a License Key Generator and API in 20 Minutes

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.

Advanced

Create an Automated Task List

I've created simple automations to make an awesome automatic task list. Add a checkbox when adding a task. Add a timestamp when done. Also emailing every day the top task.

Intermediate

How to Turn Background Color to Text

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.

Intermediate

How to Format Timestamp to Day and Month

Create a formatted timestamp to the full name of the day and month.
Formats here: https://developers.google.com/sheets/api/guides/formats

Quickly Build a Daily Random Motivational Quote Website

Build a website from scratch using a little bit of Google Sheets as the backend. Using Carrd to build a html website from the data and display a random quote every time you refresh the page. All the code including the HTML embed is in the sheet below.

Intermediate

Sync Tabs in Different Sections

How to sync different sections on different tabs. In this case we're taking data from multiple tabs putting into one, and syncing between those.

Advanced

Don't Delete My Formulas

How to prevent someone from deleting your formulas. Beyond protecting your range or sheet.

Build a Book Recommender with AI in Google Sheets

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.

Advanced

Quickly Integrate AI and Google Sheets

Get the code here: https://bettersheets.co/snippets/openai-api-prompt-and-response

Intermediate

Timestamp When Done (With Code)

Automatically create a timestamp when a certain cell is marked as Done. I show you how to customize this scrip and where to get the script. I hope this is simple enough for beginners to start automating you sheets. If you want to see more automations and dive deeper into apps script enroll in Spreadsheet Automation 101
and see more examples of Apps Script at BetterSheets.co/snippets

Beginner

Embed Quote and Author to Daily Quote Website

Step by step tutorial to update random quote site to show both Quote and Author separately. Example site: https://todayquote.carrd.co/

Quick tutorial to show you how to build a random quote site →Here

Advanced

Go to Specific Cell When Spreadsheet is Opened

Create an automation that goes directly to a cell you pick every time you open the spreadsheet file.

Beginner

Move Blank Rows

Apps Script function to move all blank rows if a certain column is not filled in. Learn how to automate this script →Here

Intermediate

Automate Move Blank Rows

Automate the apps script we just created that moves blank rows from one sheet to the other sheet. Original tutorial to write the apps script

Intermediate

Scoping Functions in Apps Script

Create multiple functions that run with the result of other functions.

Advanced

Google Maps Scraper for Outreach

Get this fun template to search on google maps via API, and quickly message via Whatsapp. Then Manage the outreach.

Intermediate

Email Certain Department Based on Google Form Entries

Message a certain department when google form is submitted.

Advanced

Form Automations: Close Multiple Forms When Over Limit

Adding to the Form Automations:
Allow User to Reopen form without Apps Script access
Set Limit in a Sheet, not in Apps Script
Set Limits for different Forms

Advanced

Close Form After Certain Number of Responses

Create automatic triggers that close a form after a certain number of responses.

Intermediate

Make AI-generated Poems Based on Name

Create simple poems based on someone's name with AI.
AI Apps Script Code here: https://bettersheets.co/snippets/updated-open-ai-model-to-omni

Intermediate

How to Create AI Images From Google Sheets

Use the Open AI API and Dall-e-3 to create image in Sheets

Intermediate

Enter Google Drive File Name Get URL

Create a simple automation with an installable trigger that allows you to enter the Google Drive file name and get the URL inside your Google Sheet.

Intermediate

Weekly Email Sheet as Lead Magnet

Create a fun freebie for selling spreadsheets

VAR vs CONST vs CONSTANT in Apps Script

Level up your variable assignments in this quick video on why there is Var or Const, or a Constant.

Tips to Navigating Thousands of Lines of Code In Apps Script

Find out how I use Apps Script built in features to code very quickly and efficiently.

Beginner

Convert Google Sheets into a REST API

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.

Advanced

Expense Tracker Template with Data Entry Form Inside Sheets

Create a data entry form for an expense tracker. Simple dashboard too with sum and sum between two dates.

Intermediate

SUM() FILTER() IFNA()

Create New Worksheet When Google Form Submitted

Automate copying a template for new form submissions with user data inserted into the new sheet.

Advanced

Better Email Form on Google Sites

Submit email from a Google Sites to Google Sheets with this easy to embed html form instead of using Google Forms.

Advanced Intermediate

Automate Google Sheets With Zero Experience

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

Beginner

Access the Dictionary API to get Definitions for Words in Google Sheets

Advanced

Automatically Clear Content | Refresh Reuse Recycle Templates

Create automatic functions to clear content.

Intermediate

Automatically Uncheck A Daily Checklist

Create an automatic unchecker for a daily checklist. Fun way to learn Apps Script and simple Google Sheets automation.

Beginner

Activate A Certain Sheet When Opening a Spreadsheet

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.

Intermediate

Introduction to Google Sheets™ Add-on Course

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 Tracking Number Fetcher with Google Sheets and Google Sites

Build a tracking number google site with Google Sheets as a backend.

Advanced

Breaking Through Errors In Apps Script

See where to find errors in your apps script and how to fix them. You will either get syntax errors ( the way the code is written) or runtime errors (when you actually run the code).

Intermediate

Perfect Use of AI in Google Sheets (Categorize Tasks)

Explore how to use AI for task categorization in Google Sheets, from setting up the environment to using a pre-made code template. Watch as we walk through the process, from generating an API key to fine-tuning the prompt for effective categorization of work and home tasks.

Final code is available in the sheet below. And the original template code is available here:
https://bettersheets.co/snippets/updated-open-ai-model-to-omni

Intermediate

OpenAI API Prompt with Array of Cells

Create a prompt to OpenAI with an array of cells

Intermediate

CONCATENATE()

Use Random Quote API in Google Sheets

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.

Intermediate

Curate Google Sheets Easily - Automatic Bookmarklet Maker

Curated sites, articles, and more web resources super easily into a Google Sheet. Create a bookmarklet automatically with this apps script tool.

Beginner

Create Your Own NIGHT Mode (like PRIDE)

Create easter eggs in your sheets.

Conditional Formatting Intermediate

AND()

Uncheck Every Checkbox in Google Sheets

Automatically uncheck a whole range of checkboxes. Or just do it yourself with the spacebar.

Intermediate

Laziest Checklist Ever

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.

Intermediate

Build Your Own AI Writer in Google Sheets

Create your own AI writer with these google sheets tips and tricks and code.

Advanced

SUBSTITUTE()

Build Your Own PROPER() Custom Apps Script Function to Capitalize Each Word

There's something wrong with PROPER() the google sheets function. Let's fix it by customizing our own TitleCase() function inside of Apps Script.

Intermediate

PROPER()

Embed Email Form and Name Form into Website to Google Sheets

Add names to the Email2Sheet apps script and code.

Advanced

Add Tasks to Google Tasks From Google Sheets

Create tasks in Google Tasks from Google Sheets using Apps Script

Advanced

Creating a Custom URL Link for Your Spreadsheet

Create an apps script function that gets the url of your spreadsheet into a cell.

HYPERLINK() CONCAT()

Create a Vote Button from Google Sites to Google Sheets

Connect your published Google Site to Google Sheets with Apps Script and a Button. Great to use for Voting, upvoting, NPS, or anything you want feedback on, at the click of a button from your Google site to a Google Sheet.

Intermediate

How To Change Date Format Automatically

Create automations to change date format based on the format you want, inside your sheet.

Advanced

Tweet From a Sheet

See how to tweet from a sheet and get the code below.

How do I reference a different spreadsheet in Apps Script?

I'll show you how to get text from one spreadsheet file to another with Apps Script. Yes you can do this by Importrange() but you might want another way to do it.

Intermediate

IMPORTRANGE()

Simple Inventory Management Automations

Create a few simple automations like email notifications and low stock alerts.

Beginner Intermediate

ISBLANK() IF()

Shaman: AI Blog Writer

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.

Intermediate

Automatic Screenshots in Apps Script

Advanced

Add NPS to Your Google Site

Create buttons on your Google Site to get an NPS, or Net Promoter Score directly to a Google Sheet.

Intermediate

AVERAGE()

Build a Job Status Checker API in 15 Minutes

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:

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.
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.
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.
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.
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.
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.

Advanced

IF() ISBLANK() IFNA()

Explore Readable Hex Codes

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.

How to Make an AI Integrated Freebie Spreadsheet

Get the freebie here at @abloommedia on TikTok or on Abloommedia.com
Sorry no sheet available even for members. Get the AI code in the video mentioned: Upgrade AI to GPT 4

Intermediate

IF() ISBLANK()

Build Your Own Net Promoter Score (NPS) Survey on a Web Site

Create an embeddable NPS system on a web page.

Advanced

ROUND() AVERAGE() DCOUNTA()

Get Sheets Wrapped 2024 | Your Year in Google Sheets

Get this free review of your Google Sheets in 2024.
https://bettersheets.co/sheetswrapped

Beginner

DATEDIF() TODAY() YEAR()

Americano Tournament Template With Four Automations

Created a tournament template with 4 automations. So tabs can be copied by a laptop, ipad, or mobile.

Intermediate

UNIQUE() SORT() SUMIF() SUM()

Create an Internal Google Sheets Add-on

The entire process to create and publish a Google Sheets Add-on internally to your own domain.

Intermediate

How to Merge Cells in Google Sheets

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.

Beginner

JOIN() CONCATENATE()

SearchLord: One Sheet to Search Them All

I created a new product called SearchLord. It's free for now and really fun to search multiple sites with 1 key word, or 1 phrase. Free for everyone at bettersheets.co/sheetlord

In this video I add 1 more feature to SearchLord to show you how to use a FILTER() function. And we add a custom menu with a little quick apps script function.

Beginner Intermediate

SUBSTITUTE() ENCODEURL() FILTER() IFNA()

CRM Automations

Simple automations you can add you to your CRM to keep it clean and looking great.

Intermediate

IF() ISBLANK() WORKDAY() DATEDIF()

MicroManager: Hourly Email Reminders

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.

Advanced

Can I Automatically Rename a Sheet based on a Date?

Change the title of a spreadsheet from within a cell in the spreadsheet.

Intermediate

TODAY() YEAR() MONTH() DAY()

Open Spreadsheet to Motivational Quote Automatically

Create a simple script to open a particular tab. In this case we're opening to a random motivational quote each time.

Beginner

RAND() INDEX() ROUNDUP() COUNTA()

SubSpyHub Subreddit Scanner

I built a subreddit search tool to keep track of subreddits with keywords and interesting conversations.

Intermediate

ISBLANK() IF() HYPERLINK() SUBSTITUTE()

Build Your Own Free Domain Availability Checker

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.

Advanced

IF() ISBLANK() FILTER() ARRAYFORMULA()

Automate Help Desk Ticket Closing

Create a simple automated help desk ticketing system.

Create tickets
Closed dropdown
Calculate time to close
Create dashboard

Advanced

IF() COUNTA() COUNTIF() FILTER() AVERAGE()

Iterate Numbers with a Simple Apps Script

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.

Beginner

Daily Writer's Word Count Tracker

Walk through creating an awesome automatic word count tracker that's easy to use and motivates the writer to keep going. With AI generated quotes from famous writers, along with simple formulas to generate states along the way.

Intermediate

TEXT() ROUND() CONCATENATE() TODAY() SUM() +1 more

Create a Stop Watch in a Google Sheet

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.

Access Free API's with Google Sheets

Get Kanye Quotes, Hogwarts Houses, Public Holidays, and more fun with API's in this 1 hour dive into Free APis available online and accessible in Google Sheets.
API's here: freepublicapis.com

Intermediate

IF() ISBLANK() IMAGE() COUNTIF()

Creating a Live Message in Google Sheets

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.

Intermediate

Magical Checklists That Archive Done Items

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! 🚀

Intermediate

Celebrate Your Completed Checklists

Learn how to use apps script to add emoji animations when you complete a checklist in Google Sheets.

Advanced Intermediate

RAND() INDEX() IF() COUNTIF() IFNA() +3 more

Create Your Own API by Deploying a Web App

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.

Advanced

IMAGE()

Onboarding Scheduler

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).

Intermediate

Building an AI Prompts Playground

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.

Advanced

Created a GPT function in Sheets

Unlock the Power of OpenAI: Create Ultimate GPT Prompts for Seamless Interaction! Discover the Formula Inside, Explore APIs, and Boost Productivity. Watch Now!

Advanced

How to Validate Email Addresses

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

Intermediate

Add Title Case to Google Sheets

Discover the Ultimate Google Sheets Hack! Transform Text with Title Case Formula. Say Goodbye to 'Proper' and Hello to Easy Capitalization. Find Out How!

Advanced

Upgrade Google Sheets OpenAI Prompt to GPT 4

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.

Advanced

LEN()

Your First Formula

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.

Beginner

TRANSPOSE() CONCATENATE()

One Weird Google Sheet Formula for Bulk Publishing With AI

'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!

Intermediate

SUBSTITUTE() IF() ISBLANK()

FILTER Can Save You From Task Overload

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.

FILTER()

Ultimate Headline Writer with GPT in SHEETS

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.

Advanced

IF() ISBLANK()

Two Things to Know When Starting to Learn Google Sheets

Two things I would teach every beginner to immediately improve your skillset in Google Sheets:
1. How to Combine Formulas
2. How to Copy/Paste Apps Script Code to Your Sheet
Learn these two things in this quick video going over why I think these are important. And how unintuitive they are to beginners!

→ Learn more about Formula Combinations
→ Learn more Apps Script in Spreadsheet Automation 101

Beginner

IF() ISBLANK()

Create Your Own Pride Flag Easter Egg in Sheets

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..

Conditional Formatting Advanced

How to Create a Google Form Automatically

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.

Intermediate

Why Different Cell References in AI Integration in Sheets?

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.

Advanced

YouTube Title Rewriter with GPT-3

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.

Advanced

Getting Started Coding in Apps Script

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

Beginner

Create a Waterfall of AI Prompts and Responses

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.

Advanced

IF() ISBLANK() INDIRECT()

Seek Errors When Coding Apps Script

It's healthy programming. I'll show you some common errors you will encounter when you first start programming in Apps Script

Advanced

How to Power Testimonials with Google Forms and Sheets

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.

Advanced

IFNA() FILTER()

Ultimate Prompt Writer and Prompt Rater

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.

Data validation Advanced

IFERROR() FILTER()

Build a Case Converter From Scratch

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.

Intermediate

UPPER() LOWER() PROPER() IF()

Mr. Beast Renamed My YouTube Titles

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.

Advanced

ROW() INDEX() JOIN()

How to Write a Function - Learn to Code in Google Sheets Part 2

Continuing to show you how to write a function in Apps Script.

Beginner

NEW TOOL Parse Spintax for Content Spinning

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.

SUBSTITUTE()

Run 100 ChatGPTs at One Time

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

Intermediate

IF() ISBLANK() SUBSTITUTE()

Spreadsheet Automation for Beginners

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.

Beginner

SPLIT() INDEX()

Embed a Number in a Website from a Google Sheet

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.

Advanced

Prompt Parade

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.

Advanced

CONCATENATE() JOIN() IF() ISBLANK()

Tag Emails From A List of Members in Google Sheets

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.

Advanced

SOLUTIONS! Solve The Errors, Learn For Loop

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.

Advanced Intermediate

Send Daily Email Updates

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.

Advanced

Create a ChatGPT Cheat Sheet Spreadsheet

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

Intermediate

SUBSTITUTE() IF() SWITCH() ISBLANK() CONCATENATE() +2 more

Create a new Spreadsheet from just a Name in a Sheet.

Create the Apps Script to create a new sheet from some data inside your sheet. Script included in the sheet below.

Advanced

Spreadsheet Automation 101: Introduction to Pre-course Videos

Learn how to automate spreadsheets with Apps Script. The pre-course videos equip you with the fundamentals of coding before you start.

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

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.

Automatically Delete All Sheets If Not Listed Name

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!

Advanced

ChatGPT Clone in Google Sheets Part 2

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.

Advanced

From Sheet to Script And Back Again - Learn to Code In Google Sheets Part 3

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.

Beginner

Create Navigation Like A Book or Presentation

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.

Advanced

Visicalc 2023 Technical Walkthrough

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.

Advanced

Calculate ROAS in Google Sheets

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.

Intermediate

CONCATENATE() ROUND()

Quickstart Tutorial OpenAI API in Google Sheets

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!

Email Yourself a Cell from a Google Sheet, Every Day

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.

Beginner

ChatGPT Clone in Google Sheets Part 1

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.

2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)

Write an apps script to delete lines easily.

Advanced

Sheet Stories / Video Notes + ADDED: Email Notifications

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!

Advanced

IF() INDEX() CONCATENATE()

Capture Emails from Website Form to a Google Sheet (Without Zapier)

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

SUBSTITUTE()

Now Do It Every Damn Day - Learn to Code in Google Sheets Part 5

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

Advanced

Create a Timer with Apps Script

Create a start time and end time and duration of that, with a little bit of Apps Script.

Intermediate

ISBLANK() IF()

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?

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.

NOW()

Embed a Headline in a Website from Google Sheets

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!

Advanced

SUBSTITUTE()

OpenSea Data Inside Sheets

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

Manage Your Wedding Guest List in Google Sheets

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.

COUNTA() COUNTIF() FILTER() IFERROR() MATCH() +1 more

5 Ways To Use Google Sheets for Advanced Project Management

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

Intermediate

IF() ISBLANK() TRANSPOSE() UNIQUE() COUNTIF() +3 more

Create a CPM Custom Function (Create Better Calculators!)

this is gonna be fun because we get to create a calculator with Apps Script.

Beginner

Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.

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.

IMAGE() INDEX() SPLIT() ISBLANK() IF() +1 more

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script

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.

Learn to Code in Google Sheets, For Programmers

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".

Intermediate

IMAGE()

Better Sheets Videos Checklist

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.

Data validation Beginner

FILTER() SORT() SPARKLINE() COUNTIF() COUNTA() +1 more

Troubleshooting Bitly in a Google Sheet Script

Exclusive video only for Better Sheets members. This is, uh, troubleshooting your click tracking web app or slash sheet.

Advanced

How to Edit a Macro

Editing macros in Google Sheets helps you do more in less time.

Advanced

Make your Custom Functions Like Native Functions | Custom Function Autocomplete

Enable your apps script functions be auto completed while you're using Google Sheets.

Advanced

Write Your First Script

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.

New Syntax for WhatChanged Formula in Google Script

revisiting the "what changed" custom function I built in a past video. Showing you more apps script

Advanced

Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop

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.

Advanced

Sheet Stories / Video Notes + Clear 24 Hour Old Videos

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.

Advanced

SPLIT() IF() INDEX() ISBLANK() IMAGE() +1 more

How to Trigger Macros Daily

Automate your apps script and macros you can record.

Advanced

TODAY()

Create a Radio Button From Checkboxes Using Google Apps Script

Let's code a Radio button that turns off all checkboxes except the one clicked.

Data validation Advanced

OR() NOT() IFS()

5 Ways to Create Coupon Codes | Create UUIDs

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.

RANDBETWEEN() DEC2HEX() CHAR() MID()

How To Set Up Stripe Webhook to Google Sheets with Google Script

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.

Advanced