Google Apps Script Tutorial for Beginners

you want to automate a task in let's say Google sheets or maybe you want to learn how to code well in both cases this video is for you I'm Michelle Griego finsih Paris and in this video I'll show you how you can automate a task in Google sheets by writing a couple of lines of code of Google Apps Script before we dive into this tutorial video do me a favor and subscribe to this YouTube channel because every Monday and Thursday I published a new video about Google Apps Google Apps Script and the whole G suite bundle so that you don't miss out on any videos go ahead and subscribe good now what is Google Apps Script well it's a flexible scripting language based on JavaScript so if you've ever written some code in JavaScript you'll be learning and you'll be using Google Apps Script in absolutely no time it's a scripting language that's built into Google Apps so you can create add-ons and and automate tasks in as I mentioned the beginning Google sheets Google Docs Gmail so you can create automation scripts where you actually go ahead and create emails paste the content send it out whatever you feel like so there's almost no boundaries to what you can do with Google Apps Script it's written in your editor in your browser actually in the built-in editor in Chrome which means that you don't have to install anything on your computer so if you have a Google account and you have a google chrome and Chrome browser installed on your notebook or on your computer that's all you need to write Google Apps Script and the code itself well that runs under Google servers so also they are no setting up of any development or production the fire environment and in my opinion it's an easy language an easy programming language to learn and so if you're thinking of learning your first programming language I think this would be a pretty good option in our demo scenario we'll be solving the following problem imagine you have a weekly report that you get from an external data source and you find yourself continually or with every report formatting it the same way the way you want it to be now the solution for a repetitive tasks like that is to automate it and that's exactly what we'll be doing so with Google Apps Script we'll be creating a menu item a custom menu item that when we click on it it formats our report the way we want it to be and that in the end saves us lots of time so this is how it's going to look this is our report and that's how it's going to look including the the custom formatting menu item that we're going to add to it okay now before we start writing code we write an algorithm or we think of the algorithm and in our case we're first going to access our active spreadsheet and stored in a variable so that we can manipulate it we're going to identify our header row as well as our entire table and store those two informations or those objects in two separate variables we're going to change the format of our header row as well as our table some formatting won't go on there and we'll create a filter for our header row now you might be saying whoa back it up algorithm that sounds like super complicated well actually algorithms are nothing else then step by step sequence of operations that still might sound a bit complicated I bet you each and every person watching this has a mourning algorithm mine is a get out of bed I take a shower I drink a yummy espresso and I ride my bike to work so you see an algorithm is nothing than a sequence of separate tasks separate operations that we have to do and if you get the order wrong then your algorithm is going to fail it's not going to achieve the outcome you want it like if I were to ride my bike to work while I'm still in bad so before I did step number one you know that's not going to work now a second thing I mentioned which you might not be quite sure what it means is a variable and a variable is a storage that contains some value also here an example that we've all come to contact at school so a plus B equals C at least that's what my teacher taught me and what does a b c stand for where it stands for any value that you want it to be for example 1 plus 3 equals 4 or my preferred example would be pumpkin plus pi equals pumpkin pie so it's not that difficult you see it's just storage for whatever value you put in there I'm now in jane examples Google sheet document and if you would like to follow along with this tutorial go ahead pause the video open up whatever you put report you have or whatever Google sheet document that you keep on having to format so you can follow along what we'll want to do is in our menu you click on tools and then on script editor and this opens up a separate tab and this is where we write our Google Apps Script so that's what I said at the beginning that you don't have to install anything why not well because Google already provides you with all the tools you need the first thing we'll want to do or what I like to do is I like to give this a name so I call it a report formatting that will save our script under that name ok so this is where our code goes and I'm not going to call it my function but instead I'm going to call it format report and you see the way I write this format and then the circum verb report in R is capital letters well that's because I said Google Apps Script is based upon JavaScript and in JavaScript the way we name our variables is by using camel case so camel case means whenever you write a new word so two or multiple words together the first letter of the following word is always written in capital okay so let's create a variable where we'll go and store our active spreadsheet which were in so once our code will run what we'll want to get is this spreadsheet here so we write let sheet for example spread sheet hap that's the object and it already gives me a list of what I can use the method we're looking for here is get active spreadsheet perfect okay so once we have our spreadsheet stored in that variable we can do things with it we can manipulate it and to do so we'll say we're creating a variables of header or headers because there multiple headers in that row and then we're accessing the value of a variable sheet which is our active spreadsheet and we're saying get range and we'll be accessing range a1 to f1 there you go let's close that statement how do I know what to write here let's go back to our spreadsheet a 1 to f1 that's where I know from that's where I get it from and then we'll store our whole spreadsheet get data range there you go the whole thing so the whole table the whole table will store that in the variable of table good and the next step is now to start with the actual formatting so let's say I want to format my headers I'll say headers set font font weight I'll set it to bold and I'll set the font color to white and the background I'll set it to my corporate identities color which is the following so I can also pass in the hexadecimal value of a number doesn't only have to be the written word like white black and so on and so forth okay so that's the formatting of our header the next thing we'll want to do is we'll format our table with let's say you want to change the font to use the super cool Roboto from Google and oops again we'll want to say set horizontal alignment to Center oops let's Center our whole table so the content of the cells of the table in itself will be centered and we'll set a border but the bear which is this one here so this method and bit down the line in this tutorial video I'll explain what a method is tells us if we want to set the border we can separately set the top the left part the bottom part and the right part of this border and that's acceptable will do so you're here on the top alright true which in programming is often a word to to do something so it's either true or false or 1 for true and 0 for false true that true as well we do not want our vertical border so we'll say false and here we'll say true and where is it I want to set the color as well so I'll use the same thing as I did here I am and I'll say spread sheet app so that's my object border style and I get to choose solid so it will give me a solid purple border around my table good the last formatting that we'll be doing is that we're going to set a filter so a filter in our header row and that is table dot create filter and let's finish that formatting so let's go ahead and save this code by clicking on the floppy disk I can good very well so so far so good now I've mentioned some things that you might not quite know what they mean and let's start with object I mentioned that that's or alter object that we're accessing or that we're manipulating what does that mean now in programming language an object can have or be two things it has data in the form of fields and we refer to them as properties or attributes and code in the form of procedures and that we call either methods or functions again it sounds a bit complicated let's break this down with a simple everyday life experience or example we have a hotel the hotel is our object and this hotel for example has a name and that will be our property so in a programming language the way to actually read what is the name what is the value of this property you were right hotel dot name and what it gives you back that we call the return value and this example would be Palace Hotel and the methods that we're using in this example is book a room so we're interacting we're manipulating the object why do I say manipulate well think that there's a property rooms and maybe property rooms available well if we used a method book a room then the property rooms available will change I'll be minus one so the way you would write this in code would be your object hotel Doc's book a room which is the name of the method and it would do something it would change something on this object and it'll most likely give you back return value and in our case it would be the information room booked so we're using the dot notation to get information from our object and to manipulate our object but that's the next thing dot notation what does that mean the dot notation is the way we access the properties and the methods of an object so you might remember the first line of code that we wrote was let sheet that's our variable so let is the keyword we have to use to create a variable which we give the name sheet and then we assign a value to it and the value is our object our spreadsheet app object and then we need to use the dot to use the method of get active spreadsheet on that object and this get have to get active spreadsheet that's just one of many other are many methods that exist on this specific object there's one to create a new spreadsheet so we will pass in the name in this method we could get the current cell create a new conditional format rule open a file and many many more now you might be asking yourself but how never memorize all those methods don't worry you don't have to and I'm surely not going to why because Google has a documentation for that so under developers.google.com you find the full documentation of Google Apps Script of all the options of objects you can use like the spreadsheet app object the Gmail app object and so on and the methods the properties that exist on these objects that you can then use to create your automated tasks your add-ons or whatever it is that you want to create so that's how you learn how to use Google Apps Script of course watching my tutorial videos but also reading up on the possibilities that Google has given us by using or using Google Apps Script now the last thing we'll want to do is create our custom menu item that appears whenever we open up our report in Google sheets and provides us with a menu item which we can then click and once we've clicked that it triggers the code that we just wrote now and if formats are a spreadsheet or a report the way that we want it to be the way that we defined it to be so that's the last step that we want to take care of now so we're back in our code editor in our browser and we'll want to create a second script and we'll name this function that we have that's a keyword that we have to write otherwise Google Apps Script doesn't know what we're trying to do and we'll call it on open I might as well give it the name of on open whenever I access this file or whatever but we're gonna keep it nice and short so the name of the function is actually up to my liking and we'll set we'll create a variable and we'll call it UI and again ask access or object and here we have a method called get UI and then we'll say you I got create menu and we give it the name that should appear in the menu we'll call it spread sheet or now let's go ahead and call it custom formatting there you go and what we'll do now is that we'll chain the next method to our previous one which would be at item and here again we have a name for Matt's report and so this will be the name that that will appear in our menu and what we have to now define is what which function should run once this button gets clicked and that would be this format report let's copy that there you go and let's paste it here hmm close that out oh no I'm missing something once we've done all of that we have to add it to our UI there you go save that no no I ran it that's not what I wanted to do so oh we have a syntax error well this wasn't planned but good thing it happened because when you code mistakes happen and one of the most common mistakes are syntax errors syntax in the programming language is like grammar in a normal written language spoken language like English so when it tells me syntax error it's actually saying hey you made a grammar error in your Google Apps Script I'm not understanding no comprendo hmm more or less like that okay this is what's wrong has to be a semicolon so let's say that good so it wasn't able to run our code anyway perfect now what we'll do to test if this works out the way we expected to is we'll go back to our spreadsheet here and we're in a browser so opening up this spreadsheet is the equivalent of just refreshing the browser so this is simulating that I'm actually opening up this file for the first time okay so now it's gonna take a second because it's reading through our code and where is it mmm-hmm and then come yet so let's refresh again let's try this again you yes go ahead oh it's too fast ah once again I'm so impatient period custom formatting so the last couple of lines of code that we added well that's what it created the custom formatting menu item which I can cling can click on and now I have my format report I'll click on that as well the script is running and this this is totally normal because this is our script asking Google sheets for permission to access my personal Google sheets account here so I'll say continue yes Jane example accepts allows and then look what happens nothing am I too impatient let's try it again running script running script now there you go so now I'm finished running our script and that this here so the displaying of this format changing of the UI in Google sheets and automatically having this format it the way we want it to be that was achieved by this code that we added here I think that's pretty cool and it goes to show that there are almost no limitations to what you can do with Google Apps Script the only limitation is your imagination if you have any questions concerning Google Apps Script or the code that I used here or if it's you're trying to follow along it's not working leave me a comment below and I'll get back to you and don't forget to subscribe to my channel because you want to become a DS repo believe me

Channel: saperis

Views: 71,810

Rating: 4.9393306 out of 5

Keywords: G Suite, Digital Collaboration, Digital Transformation, google apps script, apps script, google sheets, google sheets advanced, apps script google sheets, apps script tutorial, apps script v8, apps script sheets, google apps script tutorial, google apps script sheets, g suite tutorial, g suite tutorial for beginners, google apps script tutorial for beginners, google apps script for beginners, what is google apps script, coding with google apps script, coding for beginners

Id: Nd3DV_heK2Q

Length: 23min 54sec (1434 seconds)

Published: Thu May 14 2020