Topics > Automating

94 Tutorials

You find yourself doing rote tasks. Again and again, day after day. While designing better sheets makes your life in the sheets better. There’s far more life outside the sheets. Here are the videos that help you record simple tasks and repeat them without fail.


Are you tired of doing the same repetitive tasks in Google Sheets day after day? Do you wish you had more time to focus on the bigger picture instead of being bogged down by mundane data entry and formatting? Well, the solution to your problem is simple – automate your Google Sheets workflow! In this article, we will explore the benefits of automating your Google Sheets and provide you with tips and tricks on how to do so efficiently.


What is Google Sheets?

Google Sheets is a cloud-based spreadsheet software that allows users to create and edit spreadsheets online while collaborating with others in real-time. It is a free alternative to Microsoft Excel and offers many of the same functionalities, such as formulas, charts, and pivot tables. With Google Sheets, you can easily organize and analyze your data, making it an essential tool for businesses, students, and professionals alike.


Automating Google Sheets

As mentioned earlier, automating your Google Sheets can help you save time and reduce errors by streamlining your workflows. By automating your Sheets, you can eliminate the need for repetitive data entry, formatting, and calculations. Here are some ways to automate your Sheets:


Macros
Macros are a series of actions that you can record and replay in Google Sheets. They allow you to automate tasks such as formatting cells, copying and pasting data, and running calculations. Macros are an excellent way to save time and reduce errors, especially if you perform the same tasks repeatedly.


Google Apps Script
Google Apps Script is a scripting language that allows you to extend the functionality of Google Sheets. With Apps Script, you can create custom functions, automate tasks, and even build web applications that integrate with Google Sheets. Apps Script is an advanced tool that requires some coding knowledge, but it is a powerful way to automate your Sheets.

Triggers
Triggers are a powerful tool in Google Sheets that allow you to automate repetitive tasks by setting up scripts to run automatically under specific conditions. In essence, triggers work like cron jobs but within Google Apps Scripts, allowing you to start scripts automatically in response to specific events or at designated times.

Types of Triggers

There are two types of triggers that you can set up in Google Sheets: event-based triggers and time-based triggers.

Event Based Triggers
Event-based triggers are triggered by specific events that occur in your Google Sheet, such as a change to a cell, a new row being added, or a form being submitted. You can set up an event-based trigger to run a script automatically when one of these events occurs, allowing you to automate tasks such as data entry or updating calculations.

Time Based Triggers
Time-based triggers are triggered based on a specific time or interval, such as every hour, every day, or every week. You can set up time-based triggers to run a script automatically at the designated time or interval, allowing you to automate tasks such as sending reminder emails or generating reports.

Find out about all the triggers available in Google Sheets, here on Better Sheets.

Add-ons
Add-ons are third-party tools that you can install in Google Sheets to add new features and automate tasks. There are many add-ons available for Google Sheets, ranging from simple data formatting tools to advanced data analysis and visualization tools. Add-ons can help you save time and improve your productivity by automating repetitive tasks and simplifying complex workflows.
I've created Free Google Sheet Add-ons. You can find them here.

Google Sheet Formula Frustrations Solved
Find every formula you'll ever need in Google Sheets here at Better Sheets. Whether you're a beginner or an advanced user, I’ve got you covered with a comprehensive guide of 504 formulas.

Are you struggling to find a specific value in a column of data? Look no further than the powerful VLOOKUP formula. Or maybe you need to calculate the sum of values that meet specific criteria - try out SUMIF. And when it comes to frequency of values, COUNTIF has you covered.

Have you heard of the mysterious and powerful IF formula? It can turn your spreadsheets into gateways of productivity. And don't forget about the oft-partner ISBLANK(). 

Find step-by-step tutorials for any formula here on Better Sheets. Every formula page comes with links to written blog posts and Better Sheets tutorials featuring the exact formula.

Looking for a way to segment data based on specific criteria? The FILTER formula is perfect for you. 

If you need to replace VLOOKUP, give INDEX/MATCH a try.

At bettersheets.co/formulas, I have everything you need to take your Google Sheets skills to the next level. 
From ARRAYFORMULA() to ZTEST(), Better Sheets has it all. 

At bettersheets.co/formulas, I have everything you need to take your Google Sheets skills to the next level. 
From ABS() to ZTEST(), Better Sheets has it all. 

94Tutorials

Write an apps script to delete lines easily.
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
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.
Make a copy and rename to BACKUP plus the Date of the backup. Execute this automatically via Triggers in your Apps Script. Two are shown here. to back up a tab or back up a spreadsheet file.
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.
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
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.
Learn how to add multiple rows to your google sheets by hand, and automatically. 
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
Create a sequence of numbers that magically appears as you type in each row. Put this in A1 and start typing in column B: =SEQUENCE(COUNTA(B:B),1,1,1)
Move a range, or a set of cells, to a new area with a simple formula that's just "curly brackets". Very easy and simple to use once you know it.
Sync more than two tabs with Apps Script
Create an automation that runs whenver we check off a checklist item the past stages also check.
Drive your office crazy by setting a trigger to lighten the text and background of your sheet by 1 rgb point every time they edit, or based on every hour!

Learn how to create triggers in a fun way.
Find out how to email all the form entries each time your form is filled out.
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).
Create an automation that goes directly to a cell you pick every time you open the spreadsheet file.
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.
Build a simple function that deletes undone items in our checklist.
How to prevent someone from deleting your formulas. Beyond protecting your range or sheet.
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.
Submit email from a Google Sites to Google Sheets with this easy to embed html form instead of using Google Forms.
Copy text from one tab to another based on a checkbox being checked.
Create uniform formatting even if you're copy and pasting data that is differently formatted.
Create a simple report in a Google Sheet that documents every file in your Google Drive that was created in the last 24 hours.
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).
Create multiple functions that run with the result of other functions.
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
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
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..
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.
Quickly make a daily email to myself with a motivational quote.
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.
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.
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.
Automatically uncheck a whole range of checkboxes. Or just do it yourself with the spacebar.
Automate formatting cells to align top. Either by a time-based trigger or every time you edit cells.
Apps Script function to move all blank rows if a certain column is not filled in. Learn how to automate this script →Here
Create an automatic unchecker for a daily checklist. Fun way to learn Apps Script and simple Google Sheets automation.
Send daily emails to yourself to master sales every single day. 100% completely free for Better Sheet members.
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)
Create automatic notifications when a project is assigned, a project needs review. And move a project from one tab to another tab.
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.
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.
Find out how I use Apps Script built in features to code very quickly and efficiently. 
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 sheet that can trash itself. It can delete itself instantly or on a timer. Maybe in 24 hours or a month.
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.
Automate copying a template for new form submissions with user data inserted into the new sheet.
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
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".
I'll show you how to create a visual clock in your Google Sheet using conditional formatting. 

By following a few simple steps, you'll be able to see the progress of each hour throughout the day. 

No manual updates required! I'll guide you through setting up the custom formula, applying it to the desired range, and making it work for multiple columns if needed. 

Plus, I'll share a tip on how to reverse the colors if you prefer to track the remaining time instead. 

Let's get started and make your Google Sheet even more efficient! 
Create your own AI writer with these google sheets tips and tricks and code.
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.
Create a simple script to open a particular tab. In this case we're opening to a random motivational quote each time.
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.
The entire process to create and publish a Google Sheets Add-on internally to your own domain.
Change the title of a spreadsheet from within a cell in the spreadsheet.
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.
Created a tournament template with 4 automations. So tabs can be copied by a laptop, ipad, or mobile.
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.
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.
Create buttons on your Google Site to get an NPS, or Net Promoter Score directly to a Google Sheet.
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
Member Richard asks this question. Answer: use Google Script and a Trigger.
Created a daily email with a motivational quote in the subject.
Learn how to use apps script to add emoji animations when you complete a checklist in Google Sheets.
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!
Learn to set text wrap, overflow, or clip. Great for designing better text in your sheets.
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.
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.
Editing macros in Google Sheets helps you do more in less time.
we're going to be adding a very powerful script to our CRMs or any database in which you need to move rows between tabs.
Create a Log sheet in Google Sheets
Find out how to automatically delete rows based on a cell's value. We do this in Apps Script
Learn to code in sheets. Automate Your Daily Sales Report with Google Sheets: Learn how to create a script that copies data from one sheet to another, including timestamps. Boost your productivity with this easy guide!
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
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
Discover how to Save Your Sheets Every Week Automatically
Get this epic script that automatically edits two tabs and keeps them in sync.
Create a click Edit: Google changed how the redirect is handled. It no longer automatically goes forward, so you will need to add a link for a user to click on again to go to the forwarded link.
Automate your apps script and macros you can record.
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!
generate random numbers using Google Sheets. Fun ways to figure out how to get random characters. Great to use for creating IDs or Coupon Codes. Nice easy intro to some simple apps script.
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
Record a macro, to Insert a column every day.
this completes learn to code in Google Sheets and I wanted to finish off by giving you a few more quick concepts that expands your mind of what Google Sheets can do.
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 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.
Let’s talk about the IF formula and the differences. Learn how to insert a checkbox and all the other things it can do for you.
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.