Introduction to Google Apps Script

good afternoon my name is Jorden ray and this is going to be my introduction to the script editor and Google Apps Script I just want to do this quick little intro just showing you how to get into the script editor and exactly what it is this app script itself is just a kind of Google's way of reimagining JavaScript as a way to extend their various apps like Google Docs Google Forms and Google sheets first I'll just show you how to get into the script editor in Docs it's under the Tools menu and then you just go down to the script editor button in forms it's a little bit different it's underneath the the three vertical dots and you go down to script editor what we're going to be using today is Google sheets to look at Apps Script I'm going to put a link to a copy of this test grade book and we're going to use a just a simple grade book as kind of the basis of our our project here and it's just going to be eight columns a through K student number first name last name email class split group average first grade second grade third grade in fourth grade and these are just kind of some made-up grades so go ahead and make a copy of that now okay part 1 variables variables simply they are the things that are going to go into our into our programs if you've ever used the built-in functions in Google sheets you have used variables meaning that this average function I'm going to make this a little bit larger too large okay this average function takes in variables and then it will average them for you in this case I want to average all of these grades to the right and when I do that it gives me an average so I put variables into the average function and it gives me an output we're going to take that concept into Google Apps Script by opening up the script editor and we should have a blank canvas here with this code GS file and Google Apps Script is just like Java Script in most ways and it takes four different types of variables okay we're going to use four different types of variables a string just call it my example string and those are just inside of quotes and it's just letters you know letters words whatever you want to do I'll show you with another you can do single quotation marks to get the exact same thing however you can't have a two variables with the same name because they will just overwrite each other so besides strings we've got numbers and those can be either integers like 1 or 5 or they can be what you would call another one just floats like 1.45 javascript will differentiate for you and we've got a raise and those are inside of square brackets and an array basically contains a number of other variables so and you can even mix them up like I've done here I've got first which is a string second which is a string third which is also string and then the number four and we can get to that by calling the variable and then talking about its index and I'll talk more about arrays later it's just an example and then we've got objects probably the most difficult part of javascript and that's inside of these curly braces and an object basically is a series of key value pairs and I'm just going to this is going to be a string called this is the key and then it's pair is going to be this is the pair okay part two I'm going to talk about string variables in this section and before we can really do anything with these examples here I've got you know I typed up some some example variables here but they don't really do us any good in the editor like this we've got to wrap everything inside of a function and a function is just the this is the verbs I'm an English teacher so I think of the variables as the nouns and functions as the verbs this is what's going to do things to our variables so I'm going to just type up a function and call this my function and the syntax for it is you call the function keyword you give it a name braces or sorry parentheses and then curly braces and our code itself is going to go inside these curly braces I'll come back to talk more about functions later on really and get kind of more into what that works I'm going to type an example string and this is example and another this is a built-in function to Google Apps Script called logger.log and this is going to allow us to do some testing okay once I save it I can click on this little play button or also alternatively ctrl R and that will run the my function that we've written and we can take a look at the log with ctrl enter and you'll see it prints this is the example string you can also get to that logger by going to view and then logs it gives you a date stamp but I haven't really found that useful and then you know make you make say it changes to that stuff run it want it this is a string okay so this is how we're going to do some of our testing with strings you know you've got lots of different functions we can actually on the end of the string if you put some square braces and you type 0 we're going to get that T because it is the first element and since this is a zero based system right this T is going to be 0 the H is 1 I is 2 and so on and so forth including the spaces right so we can call each of those things to get the various elements of our string we can also call functions like substring and there's lots of various methods that we can run on strings and all of that stuff you can deep dive into that stuff on your own this is just a kind of bootcamp --is-- tutorial okay example we're going to talk about numbers in this one and basically any mathematical function you can think of it's going to be available to you inside of these functions just run this and you're going to get 15 you can add floats and integers together fifteen point five you can subtract let's divide one point and there's another one where you can get the remainder of something so let's see 10 divided by three get the remainder of that is that right yes 10 divided by 3 get 110 divided by 6 what do we get with 6 that's true because it goes into it once so they'll use that percentage sign to get the remainder and you may have noticed that I use this a single equal sign to set the variables later on when we talk more about conditional logic you'll see things with double equals or triple equals but for right now let's just focus on single equal sign setting a variable to equal something one last thing I want to talk about with numbers is there's a whole library of math functions and Google Apps Script is nice with this autocomplete you can see here all kinds of possible things where you can get random numbers that will give you a random number between 0 and 1 a round number sign square root tangent all kinds of things to play around with now I'm going to talk about how we can put all of this stuff together into a function that we can use inside of our spreadsheet so I talked about the built-in functions that we have available to you like average and a nice little autocomplete feature here tells me all the various things as you type maybe I want to count something as if statements get some logical expressions written into the basic but I want to write my own functions for that and that is very very simple with the app script editor I want to turn let's say all of my students email addresses are the first letter of the first name and the end their last name before our you know at my school dots k12 that calm and let's go ahead and write a function that will do that for me so I'm going to go back to my script editor I'm going to give this function Declaration and then we'll call it email email writer now I talked about these parentheses earlier and this is where we're going to take in parameters or arguments into our function and this allows us to send very or send send data from the spreadsheet you know out here anything out here into a variable in our code and I'm just going to call this names and this names variable is actually going to look a little bit like this it'll look so that will be these square brackets because it's going to come in as an array and then every row in the array is going to be inside of brackets so I'm actually going to get a first name and then a last name inside of brackets like this and I'm going to show you how to like that I'm not totally sure I'm explaining this correctly but we're gonna we're gonna have to find out explaining this very well so names is going to come in we want to take the names the first element of the names because I'm actually going to send it Jim and Jose right and once I do this might be able it would be better to instead of that logger.log this time we're going to return names 0 0 plus names 0 1 ok and you see I put the space in the middle oh and I forgot this plus with the plus signs here is how we're going to concatenate all this data together and let's go ahead and run that um nope sorry I actually don't want to I'm running it inside of here but I don't have any data in names so I just want to save this here and then go back to the spreadsheet and I should be able to type email writer whoops and then inside of the parentheses I'm going to highlight Jim and Jose and you'll see it returns this Jim Jose all into one into one cell perfect and if I drag this down it should you know do the same thing for all of these names so they are written a function that's just concatenating two strings is its passing in this multi-dimensional array and then we're manipulating that inside of the function but I'd only want the first letter of the first name and I'm so I'm going to take the first letter I'm going to remove the space and then add that and let's see here now we should have J Jo's perfect be nothing Mary Hart be nothing that doesn't okay so I want to then add my at my school k12 dot what is it is it then the state initials I'm from Virginia so I'm going to do k-12 that VA that is a Tendai USA someone I can Trevor even what what that is okay um another thing I noticed I'm going to show you how to do because this gives us a string I'm going to try this - sorry - lower case this is a method that you can run on strings up and like I said you can find more of these more of these various methods that are available to strings this is a simple google search and this should lower case everything I just for some reason I like email addresses to be in lower case and here now you can see what my function has returned all right so I've written I've now turned a little bit of knowledge about functions strings and numbers into a function that I can use in my spreadsheet okay I'm going to talk about arrays now this is a little bit more complicated and you saw a little bit of what arrays can do when I use the parameters in that last function call but I'm going to go back into my script editor and I'm going to make a little bit of a um a random password generator let's call our function get password and it's not going to take in any parameters it's just going to be a function that I call and it'll just give the student a password and let me declare an array here and it's just going to be just going to be an array of words and then I'll randomly randomly get those words and words like pencil paper math school teacher student and textbook all school related words I don't know why that's just what I'm going to go with here and it's going to randomly generate um I'm going to go ahead and type in my return value now even though I haven't created it just so I don't forget I'm going to be returning a password back to the spreadsheet okay so I've got this I've got random words I'm going to pick out of the array and then eventually return a password so to do that I need to figure out how to get a random number um and I'll just call this Rand tandem random random number and I'm going to use this math dot random function and because I have a length of one two three four five six seven I'm going to call this math dot random I think I then multiply it by let's try the I always forget how this works math dot random returns a value between zero and one so we need to modify that because we don't want a number like point zero one three that's not going to help us any so let me multiply it by ten and see what we get here and the I'm going to declare a password and equal that two words the first element in the array because the zero corresponds to the first thing that's in there because it's indexed by 0 this is 0 this is 1 this is 2 so on and so forth so I'm going to do words 0 plus random number and that will give us a little bit of a password here okay so this is going to be our get password function I'm going to insert a column call it password and go ahead and call it here get password um loading data loading data taking a little bit longer here okay so I've got pencil five point five now I want to actually round it I think I would do then I need to wrap this in another function this is another number function another math function a floor means to round down it takes it to the bottom floor and let's see here hope I didn't mean to run this inside of here but we can look here pencil three okay and if I strike that down ooh it's giving me the same oh that's right sorry because I'm always taking that zero element okay but I want this to also be a random number and to do that I'm going to look up a formula okay there's a very great resource on the internet called stack overflow stack overflow and I want a JavaScript a random number between and let's see hit generate random number between two numbers in JavaScript 15 answer is 722 votes looks like this is going to be pretty helpful to us is there a way to generate a random number in specific range in JavaScript perfect if you wanted to get between 1 and 6 you would calculate math that floormat that random times 6 plus 1 where 1 is a start number 6 is the number of possible results okay perfect so that is exactly what we're looking for and it's basically what I had typed up here just on accident here so I'm going to do a random key and instead of hard-coding in that number 6 I'm going to take words dot length that length is just a property of an array that tells me how many words are in this array so I could add words to this and it will still generate the it'll still give me a random word though I then in this key value I'm going to put in this random key instead of hard-coding in that zero save it again and we should have different words for each of them now and I've just kind of a simple little random password generator this is not the most obviously not if it is super secure way but it's a it's enough to kind of give an example of how to use an array and some of those math functions okay where to go from here I mentioned objects at the beginning and I'm not going to use this as a you can watch hours and hours of videos about objects without you know getting into all of the the things that are they're capable of doing but I'm going to use this to talk about this last little section about where else you can go with Google Apps Script I've written two little custom functions and I'm going to show you how we can then harness the other web technologies to kind of further extend the these various things like putting add-ons on the add-on bar and having cool little pop-ups inside of Apps Script underneath help you can click on API reference and tutorials and they're great resources for for getting started with Apps Script they take you right to the documentation pages and what we want to do I'm actually going to go back to the guides I'm going to open up add-ons for Google Apps and I want to do uh let's go to overview what I want to do is let's say in Google sheets I want to build a little sidebar here I can open up and you know further use some some tools you know that help to be coded later the first thing I'm going to do is I'm going to create an on open function and then uninstall function these are built in simple triggers for Google Apps Script and you can read more about them triggers and events and inside my on open function I wanted to add something to the to that add-ons bar so I need to first and foremost get the UI so I'm just going to call this UI equals spreadsheet app and when I hit this dot immediately you get a autocomplete field here that's very very helpful is there are a lot of services and methods that you can call from Apps Script but the first thing I want to do is get the UI and then I'm going to create an add-on menu I'm going to add an item and the app the caption will be sidebar controls that's you'll notice the quotation marks here because this is a string and then it wants a function name which is also going to be a string so we've also got to put our function name inside of these quotation marks so I'm going to call it open sidebar and then I've got to finally call the add GUI method now these are all basically what this is without getting too detailed into the explanation because as you learn more JavaScript and get more used to the interface you'll start to understand these things a little bit better but this is basically a very complicated object with functions built right into the object and then those are end up being called methods i hear them referred to as methods and because of java scripts dot notation i can space this out a little bit more nicely and i think i need to then return UI or maybe actually i think that is enough I actually don't even need to save this as a I don't need to save the UI I'm just going to go ahead and call the spreadsheet app service and then the various methods to add to add it to the UI and then one other thing uninstall is going to be is all it's going to do is run the on open instead of you know writing the same thing again I'm just going to call that function from inside on install okay so once I save that I'm then going to publish I'm going to test as an add-on test with latest code installed and enabled and then the doc I'm going to select is going to be this demo video grade book save that and then test and it will open up a new test version of my grade book and underneath add-ons eventually it'll add it in here and I've got this demo video and then with my sidebar controls now the thing is when I run it I don't have an open sidebar so I'm going to go back in to my code and let's create that now open sidebar this is where we can start to use HTML to HTML and CSS to you know extend our project here so I'm going to go to file new HTML file and I'm just going to call it sidebar and inside I'm just going to go ahead and type right inside the body of the HTML gives me some HTML you know out of you know just a little template at HTML but none of that is important because it's all templated inside of an iframe all of these are going to be templated inside of an iframe now with various restrictions on it though it's generally pretty good so really I can just put it inside of a P tag and then call it this is where the controls go and then close my paragraph tag save it and I've got some HTML so in this open sidebar now I want to I want to open that and we're going to declare an HTML variable and then call it HTML service this is the set of methods to to turn that little file into what we want to use and then the file name give it is sidebar obviously this sidebar here is referring to this sidebar HTML so we're going to create HTML output from file I'm you used to have to do a east I have to set the sandbox mode but now it's going to automatically it's going to automatically iframe it no matter what so the only other thing I'm going to do is set the title and I'm just going to call it side bar or do they call it in the side bar controls all right and I think that should be enough ok so now that I've written this HTML service I got to put it onto the page and all I'm going to do there is spreadsheet app again I'm going to get the UI the user interface and then I'm going to create oh no sorry it's it's open this is why this is so helpful to have the show there is show sidebar and then in there I'm going to put HTML whoa the variable HTML that we created and that right there should be enough get us started and here you'll see sidebar controls have opened and this is where the controls go inside this here you can put script tags and then you can asynchronously use put some JavaScript onto the page hmm let's call it a example and then I'm going to an error and then I'm going to delete this and call this so now I don't have anything inside my paragraph tags here but I've got the example element and I'm going to put this as the text and here you'll see that I'm whoops I don't need to open my console add-ons demo video sidebar controls open it up and then this is the text so I'd love you to play around with that give me some feedback on this video where can I be a little bit more explicit in my explanations this is the first time that I'm ever really trying to explain JavaScript to anybody and since I'm pretty much all self-taught by free code camp comm which I can't say anything nicer about I mean it's a great resource for learning JavaScript they really they start you off with HTML and CSS and then working with bootstrap and then jQuery before you ever even get into JavaScript but play around with these things and you know let me know where I can expand this little intro video so if you've made it this far appreciate your watching and I would love your feedback Cheers

Channel: Jordan Rhea

Views: 118,164

Rating: 4.8816948 out of 5

Keywords: javascript, google apps script, coding, teaching, teacher tech, edtech

Id: _APsUaXd980

Length: 35min 40sec (2140 seconds)

Published: Fri Oct 28 2016