what you need to know to automate Google Sheets
what you need to know to automate Google Sheets
Here's what you need to know to automate Google Sheets. This is just an introduction to coding inside of Google Sheets. This is not meant to be a complete course. I just want to get you excited about coding in Google Sheets. Almost anything you can do manually, even all of these menu items like coloring or alignment or inserting rows, inserting columns, sorting, these kinds of things you can do with Apps Script . And then, on top of that, you can create automations where they happen without you having to do them yourself. Either they're based on some user edit. Or they're done at some particular time. And I'm going to show you an introduction to just what you need to know to automate your Google sheets. What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Explore Coding in Google Sheets Automate Google Sheets With Zero Experience Track Anything! In a Google Sheet
So the very first thing to know is that you go up to menu extensions, app script up here. It's going to open a new tab and we already have a script written. It's a sort function and what might be the most sort of convoluted thing or maybe frankly, fearful. thing, maybe depressing, is that when you open this for the first time, it'll look like this, my function . This is a completely blank function , it doesn't do anything. What we have to do is we have to know what to do here. Now, what I like to do, the very first thing, is type in spreadsheet app, and that's a capital S and a capital A. Those are important. Look, it is pink, that's very important. If I just type in spreadsheet , it's black. Getting Started Coding in Apps Script Getting Started Coding in Apps Script Introducing SheetFools Make a Tour of the Sheet
So we know we have to capitalize it. And the second thing is, there is this, or Autocomplete and it's trying to help us. It's actually not in the way, it's actually helping us complete our script. So if we add a dot here, and dot notation means we're going to use this function spreadsheet app and we're going to use some function on it. So we have to add a dot. Now once we add a dot, look at all of the stuff we can do. There's so many things we can do here. We can create a new sheet. We can get selection . What I do most of the time, like 90 percent of the time, literally 9 times, actually 99. 9 percent of the time, is get active spreadsheet . Why is that? Is this get active spreadsheet with parentheses means we are Spreadsheet Automation 101: Dot Notation Getting Started Coding in Apps Script Activate A Certain Sheet When Opening a Spreadsheet Automation is not Magic
accessing the entire worksheet file. Every single tab here, if we add more tabs, this spreadsheet , get active spreadsheet , contains all of those. If we want to get one single tab or one single sheet, we need to go to get sheet by, and we're adding a dot here again, a dot notation to use this function , spreadsheet app dot get active spreadsheet . I'm going to say get sheet by name. There's also get sheet by ID and there's also get sheets, but for our purposes, just to get started, I'm going to get sheet by name. You can type it out or you can select it in the auto complete get. Sheet by name. There it is. You must add the parentheses and inside the parentheses, we're going to add quotes. Once you add one single double quote, it'll add the second one. Getting Started Coding in Apps Script Getting Started Coding in Apps Script Get Sheet ID and Tab GID Into Your Google Sheet Get Tab Name in Cell
So you don't have to finish it off. You just have to get in the middle of there and type in the sheet name . If we change the sheet name here to let's say sales, all caps, we need to change it here in the app script . Now we're getting the entire sheet, not the entire spreadsheet file or the workbook, but just that one sheet. And now we can do a lot of cool stuff here. If you just put a dot and look through this list of things we can do, we can copy it. We can delete a column, we can delete a row. We can insert columns , we can insert rows , all types of stuff. I'm going to sort and this sort , it's going to. Offer us. By the way, if we just add a parentheses when we're inside the Tips to Navigating Thousands of Lines of Code In Apps Script Getting Started Coding in Apps Script Introducing SheetFools How to Retype Header Automatically
parentheses, it gives us a little help. It shows us this, the column to sort by sorts a sheet by that column ascending. So let's put in two here, because two, let's go back to our sheet is gonna be the B column, but there's also this total, let's actually do it by the total. So that's gonna be the fourth column or column D four. I'm going to change this function name to just sort , and I can hit Command S if I'm on a Mac or Control S if I'm on a Windows. Or, here's this file save project, sort of like a disk save project to drive, we're going to save that. And now, if we add even more functions , let's say sort total column, we can create another one that sorts by January and call this sort total. How To Format Sheets for Notes Figure Out Frequency of Numbers How To Format Sheets for Notes Sort Column in Google Sheet AUTOMATICALLY!
January, save those both, and now we have a drop down menu of sort January or sort total column. So all of our functions here, we can pick out which function do we want to run, and we can click run, so let's see what that does. Run, execution started, it's telling us it is complete. Go back and it is ascending from lowest to highest based on the D column. Let's change a little something about this. Instead of The fourth column ascending. Let's do comma false. And let's run that again. We have to save it before we run it. And hit run. And now it's reversed. It's the highest to lowest. Isn't that cool? So now we're already creating a very simple function here. Something that obviously can be done just going here and we can sort sheet How To Sort A Filter Figure Out Frequency of Numbers Auto Sort By Priority
A to Z or Z to A right here, right? But here's the magic, why this is so intriguing and so interesting and why coding is so awesome. Let's go over to the left side in our Apps Script , click on Triggers, it's going to open this section. On the bottom right we can add a trigger , now choose which function to run, let's sort the total column. Select Event Source, here's where it gets really awesome. Select Time Driven, and now select Day Timer. And we're going to select the time of day. So now every time, let's say nine to 10 PM, click save. Now, every single day, one time between 9 PM and 10 PM, the first time it runs, it'll run at a random time between, in between those, that hour, the second time it runs and carrying on every day, it'll be 24 hours after that. Now Do It Every Damn Day - Learn to Code in Google Sheets Part 5 What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Different Kinds of Automation Copy Date to Next Cell Automatically
This is a time based function . This is pure automation. We create our function . here in our editor . And then we can add a trigger that says, Hey, trigger this function , exactly how I've written it every single day or every single hour or every single week or every single month. Isn't that cool? Let's create some more functions , function , create new tab, very simple function, spreadsheet app dot get active spreadsheet, add those parentheses, insert sheet, and that's all we need to do is just insert sheet. We're going to save it and we're going to select it here. I'm going to run. Let's see what happens. It says execution started. Get completed. And we have a new sheet. Let's run it again. And we have a new sheet again. So this allows us to create new tabs anytime we want. Activate A Certain Sheet When Opening a Spreadsheet What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Create Tab For Every Day of the Year Insert Row Daily Automatically
Maybe we want to copy this sales for the next month. Maybe we want to insert here. So like, let's say our function insert new month. We're going to create a new function here. We're going to spreadsheet app dot get active spreadsheet print . Make sure you add those parentheses dot get sheet by name sales insert column . Now we have insert column after or insert column before. If we use insert column before, It's going to insert a column in front of this. Let's say we want to do the very last one. Basically, we want to go here and insert a new column, and we essentially want to add March, April, May, each month. We want to keep adding one, but we don't want it at the very end. Copy That, From One Sheet to Another - Learn to Code in Google Sheets Part 4 Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users Copy That, From One Sheet to Another - Learn to Code in Google Sheets Part 4 Add a Button To Automate Add a Column with Date for Inventory
We want it one before. Well, how do we know how many columns we have? I'll show you. We're going to add a variable, totalColumns. equals, and we're going to take exactly the same sales, copy and paste that, get max columns. So now this number total columns, let's add a logger dot log, just so we can see what that is, total columns, is going to tell us how many columns there are. And so our columns before is going to be that total columns minus one. Actually. No, I think we want just total columns. Let's see. This is when we run it. Let's see what happens. Insert new month run. It'll log the number five. And where did it go up? Yep, exactly where we want it. Not minus one, but we want it just before this final one. Create an Auto-Update Sales Chart: Trailing 12 Months How to Add Multiple Columns with Apps Script
So now we can do April. So what if we wanted to put a date here? Maybe we want to do this every single day. Let's duplicate this. And call it daily sales. And instead of having January here, maybe it's a date equals today. Today is the January 3rd. And now we want to insert a new day. Let's keep this function and call it insert new day. Our total columns we need on our daily sales page. We want to put it in our daily sales page. Want to insert. Just before this total and we want to put the date in the second row. Let's see how we can do that. So we want to do spreadsheet app. Actually we want to just, if you notice that we have all of these are the same. How to Trigger Macros Daily Google Sheets Interface Changes Now Do It Every Damn Day - Learn to Code in Google Sheets Part 5 Insert Row Daily Automatically
This daily sales. We're going to rename it sales and we're going to do variable sales equals this. So one of the cool things about coding is that we don't have to type this again and again and again. This whole line spreadsheet app dot get active spreadsheet dot get sheet by name. Daily sales. We can just call it a variable and now start doing cool stuff with it. So we get this total columns. We're getting the total columns. We're inserting before. So now on sales dot get range . We need to know the range . We want row two. So what's cool about this get range is that it can be done by Definitely doing like A1 or A2 or B56, it's okay to do that. But we want to do it programmatically, so we can use just numbers. Row 2, comma, the column number . And the column number , we actually are going to use this total columns minus 1. Send Daily Email Updates Getting Started Coding in Apps Script Now Do It Every Damn Day - Learn to Code in Google Sheets Part 5 Get Values from Sheet for Custom Function in Apps Script
Because we're going to put it, we're going to see set value. And we need today. But we don't have a today just like we did here. We don't have today. We get that by creating a variable today equals new date. We do want to format this in a, just the text of date. So let's get that. We will have to wrap this with utilities dot format , date, time zone . Let's do GMT plus zero comma, and now the format . So it's going to be just day, day, month, month, year, year, year. And end parentheses. So now we're doing a little bit more complicated stuff. We're taking the variable sales, which is just the daily sales. We're getting how many columns there are. Announcing Sell Daily and How I Built Sell Daily How To Automatically Add Dates in Google Sheets
We're going to log that, how many total columns. We don't necessarily need this log, but it's really helpful when we're programming to see that logged here. We're going to insert a column before the final column. And then the second to last column, On the second row, we're going to set the value today. Let's see if this works. Let's hit insert new day, hit run, three. Ah, let's see what happened. I think, let's add this one, one. Unless it did, didn't get an error . Let's try again. Oh, I think I see what's going on. Total columns minus one. That should be it. Oh, I see. So this variable total columns is getting it once and setting it. So we do need. We'll probably do that. Let's see if that runs better. There we go. Now it's working. Track Every Edit (Almost) How To Create a Stop Watch in Google Sheets How to Add Multiple Columns with Apps Script
So every day it's going to add a new column and label it with today's date. I think it's a pretty cool functions here. As you can see, we can have multiple functions . You now know , how to write a function . Where to do triggers, which is automating your sheets. So now you can discover really cool stuff. I really love Apps Script . If you want to learn way more about Apps Script , consider subscribing here on BetterSheets on YouTube or go to BetterSheets. co sign up for some classes and some courses there that go really deep into automations. Spreadsheet Automation for Beginners What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Introducing SheetFools Automated Daily Reminders