Google Sheets Macros - A detailed tutorial

in this video tutorial you will learn how to record apply edit and delete macros in google sheets [Music] to record macros open up your google sheets in this case we see we have five separate sheets and we're going to be on this sheet here and we'll be recording our macro here so we click on tools macros and then record macro and we're presented an option to either use absolute references or relative references so absolute means i'm going to let's say select the first two rows mark the content bold so no matter where i apply this the first two roles will be marked as bold when i'm applying this macro with a relative it's okay so i have this cell selected and to the right the two cells are going to be made both the values so wherever i click to apply this macro the two cells to the right will be applied but to really drive this concept home i'm going to demonstrate both so now i'm going to use the absolute references i'm going to do the following i'm going to say view freeze i don't know the two rows on top and let's go ahead and select these two mark the content bold and make this 18 pixels like so and you know what let's also go ahead and add alternating colors um whoops my bad uh not only on those two rows obviously but on the whole table so here you go alternating colors choose your color done save and let's name this absolute um formatting about as i've already used this name in another sheet it already suggested i could give or save this under specific number so that i could use this keyboard shortcut right here in combination with the number to really really quickly apply that formatting i'm not going to do that in this case as i'm going to run it manually okay so that was the formatting of an absolute macro let's now in february try something else out we want to try out let's say um i don't know views and watch time is so important that we want that to be bold um whenever we click in here um we want these two to be marked bold something like that so that you know whenever i click into a cell i wanted two cells next to it for instance to be marked bold so what we could do now is again tools macros and you see that's the one we we recorded before that's still there and i'm just going to hit on record macro again this time i'm going to choose use relative references and um currently this cell here is selected and i'm just going to go ahead and make this bold and i don't know maybe make this yellow yellowish like so there you go click on save and let's call this uh relative formatting okay save so let's see how we make this work we're gonna apply first our absolute formatting so i clicked on the march sheet and now i want to apply this macro macros and absolute formatting let's see the magic happen ah authorization required hmm what does that mean well you see under the hood what actually happens when you record a macro is that a lot of apps script is put together actually um putting into writing into code what you are executing what you are clicking the colors you are applying and when you want to use this now the first time this script it's acts or it's requesting permission to run this code is requesting permission so we go ahead and allow this and click on allow here now this requesting permission has a side effect that our macro was halted stopped it wasn't totally executed that is why now i again have to click on tools macros and then say absolute formatting that only happens when it's asking you for permission and there you go looks perfect looks exactly the way we wanted it to be let's verify january looks exactly the same and march okay good now what about the relative reference i chose here i said that whatever cell you select the two cells next to it will receive that formatting so let's try that out in april i'm going to click here in video publish time tools macros relat yeah relative formatting there you go okay uh let's test this out like so so i click in here and then i choose tools macros and relative formatting and exactly so you see it's relative to whatever cell you had selected when you were recording this macro if you want to tweak a macro you would have to delete it and record it again now there's another way that you can deal with that if you don't want to go through this hassle and that is by accessing the code which is backing this macro that you recorded so i open up the script editor what we're seeing here is apps script so google apps script the code used this is comparable to vba which you might know from excel word and so on and so forth and nicely enough we've to see or we recognize two functions absolute formatting and relative formatting and if i wanted to change something here i can easily do so in this code now if you're looking at this code and saying oh my goodness i have no idea what this does no problem i've got you back um because i have a video tutorial on google apps script for beginners so if you want to make sense of this want to know how you can you know record your macros and maybe tweak them if you have to then do by all means check out that video now i'm gonna do something simple which is really really visible um in one of the macros i changed the font size to 18 pixels how about we make that super big like 38 pixels like so to see how how it changes so i'm going to go ahead and save this and i'm going to go back to my document and may may i haven't applied the macro yet so let's try this out it was just to verify again it was the absolute formatting macro so let me execute this now on this sheet tools macros absolute formatting and i'm expecting the first two rows to be huge which is two they um are 38 pixels that's exactly the value i passed in here so that's how you can edit existing macros by accessing the script editor and tweaking a little bit with apps script if you no longer need a macro delete it tools macros and manage macros gives you an overview of the macros you have available let's say the absolute formatting you don't need that anymore by the way you can also edit the script from here you'll access the script editor there you go ahead remove and update so now it's gone so if i check again tools macros i only have the relative formatting available but what if i need that macro back well there's an interesting feature called import i can click on import and then i see functions that have been available previously so in this case the absolute formatting macro and i can simply say app function and if i now check my macros again it's back so if you maybe delete a macro you need it back after a while no problem just click on import now super super important macros only work within the boundaries of that specific sheet so if you record macros here in this specific file they will only be available in this file i'm curious do you prefer absolute or relative macros let us know in the comments section below and while you're here don't forget to hit the subscribe button so that you don't miss out on any of the future video tutorials we will be releasing

Channel: saperis

Views: 13,102

Keywords: google sheets macros, create google sheets macros, record google sheets macros, google sheets, macros, macros in google sheets, how to create macros in google sheets, how to google sheets macros, can you use macros in google sheets, google workspace, chanel greco, saperis, Program macros in google seets

Id: KY29yNi7C0c

Length: 8min 43sec (523 seconds)

Published: Tue Aug 24 2021