Lesson 7: Automate Your Sheet
Total video watch time - 32:03
Total video watch time - 32:03
Install Google Sheets Add-ons
Manage add-ons
Use an add-on
Record and Use an a macro
Learn how AppsScript can further automate your sheet
Add-ons are created by 3d party developers and serve to add functionality that does not exist currently. Like Slides, Docs, and Forms, add-ons are also available in Sheets. In this video we will take a look at how to install and delete add-ons.
Please note: If working within your school account, your domain administrator may restrict your ability to installl add-ons. For this course, you will a personal account to install and use add-ons.
Make changes to the case of text in block selections: uppercase, lowercase, first letter capitals, invert, sentence and title case.
This Copy Down add-on allows you to perform calculations automatically when a Google Form is submitted. When turned on, any formulas in row 2 or 3 (you choose) of the form responses sheet will be automatically copied and calculated for new submission rows. Additional checkboxes allow you to determine whether to leave formulas live, or paste back their results as values.
The CopyDown add-on is essential if you plan to merge emails and documents upon form submission. This will be covered later.
The ARRAY formula provides the same functionality as the Copy Down add-on without the need to install an add-on. Array formulas allow you to perform calculations automatically when a Google Form is submitted.
If you find yourself constantly repeating the same tasks in Sheets (such as removing duplicates, trimming whitespace, sorting, and applying formatting, etc.), Macros will be useful for automating these time consuming and frequent actions. Macros are used to record your keystrokes and save them so they can be reapplied later with a couple of mouse clicks or a keyboard shortcut. Macros may sound intimidating, but as you will see they are very easy to create and use.
Please note: The first time you run the macro, you’ll be prompted to grant it permission to run. My tutorial did not show this as it wasn't my first time using a macro.
You can code automation into spreadsheets using AppScript. This video will show you how to access and use AppsScript to create, save and run automated functions within a spreadsheet. You'll even learn how to set triggers that will make your program run automatically.
Some of you may be thinking, "I'm gonna skip this one, I don't know how to code". Guess what, I don't know how to code either, but that hasn't stopped me from using Apps Script! You can find pre-written code on the web then copy, paste, and tweak it within Apps Script.
If after watching this video, you'd like to learn more about Apps Script or try out a script in one of your spreadsheets, check out the additional resources section.
IMPORTANT: Before opening the practice task, make sure you are viewing this page with your personal Google Chrome account. This will ensure that a copy of the task is created in your personal Google Account. If you open the task in your school account, some features may be blocked and you will not be able to submit the assignment to Google Classroom.
Click the Practice Task button below, then select the "USE TEMPLATE" button to create an editable copy of the task in your Google Drive.
Follow the steps in the INSTRUCTIONS tab
Click the Solution File button to check your work. (Please note: For this module, a copy link is not provided as it would result in an additional Google Form being created in your account. You can view the solution using the Solution File button. If you do want your own copy, go to File-Make a copy)
When you have completed the task to the best of your ability, find the "TURN IN" tab in the Practice Task for instructions on how to submit your work.
GSuite Marketplace - View add-ons that work with Google Sheets
Introduction to Google Apps Script - A free course offered by the amazing Ben Collins.
Apps Script Code to Automatically Sort Your Sheet - Find a code to copy and paste into Apps Script, with instructions, so that each time a Form is submitted, the response spreadsheet is automatically sorted by a column you specify.
Apps Script to Automatically Place Events in a Spreadsheet on a Google Calendar - Find a code to copy and paste into Apps Script along with instructions,.