Google Sheets - Email HTML Tables & Schedule Apps Scripts

alright so in this video I'm gonna try to show you how we can schedule some automated emails to go out whenever you want them to go out maybe you want this to go out every week or every day or every month at a certain time whatever the case is gonna be and while we're doing that we'll also see how we can incorporate some HTML tables into our emails so the first thing I need to do as usual open the script editor this is our new v8 engine dismiss the message go ahead and name this project I'm gonna start by creating the actual HTML for this email so I'll go under file new HTML I'll call this file email I'm gonna try to make our email closer to this but I'm not gonna go too crazy matching every single detail I kind of hate doing design but if you want to be more detail about it be my guest for me I'll just do a little div in here and this will be pretty much the container outside just to have a container I'm probably not even gonna use that div but I'll have that anyways now inside of this I'm gonna create a div one and actually I'm gonna do two right below that as well and the reason I'm doing through here is because I want this first one here to act as this whatever this colored line is on top and the second one is gonna be this entire box so now inside of this second box I'm gonna need to put this current inventory text which will go inside of the second box right here and I'll call that an h1 I'm just gonna type my inventory because what I want to happen in the end of the day I want this to be replaced with whatever text is here in our spreadsheet so I'll just type something a little different so we can see the difference as we go through this right below that h1 we need that little red thing whatever that is so I'll just do another div for this and I'll do sand on some day so again that should be replaced with this go below so below that we're gonna need to place this table now before I do that table I'll do another deep here just in case keep it empty for now now I need this table so let's create that now in this table we're gonna have the section for our table headers so I'll just start with something like tea had just in case we need something like that and in this we'll do a row and in this row we're going to do the columns one two three four four columns there and I need to repeat that for every single column you could put this on different lines because we only have four columns I kind of prefer it this way that's fine so that's the top section of our table which is this now we need to take care of these lines so let's start by I'm just gonna copy and paste this because it's going to be easier to change that up so this is going to be tea body and we're gonna have a row and each row is gonna have our table data cells so T DS and I'm not gonna do it for all of these three I'm just gonna do it for one of them so just that so finally below all of these we're going to need this thing the total line so that will be the footer section of our table and this should be that total and then this should be empty and this should be empty and this one is that total whatever that number was in here so this I didn't spell right let's correct that so there is our table do we have anything else that's pretty much it for our HTML I suppose we're gonna have to do some styling here but we're gonna worry about that later so with this let's try to go to our code side and see how we're gonna make all of this values go to this table so I'll go to my code side in here let's just connect to our spreadsheet so we're gonna get our active spreadsheet and store it in this variable now let's get the worksheet here called invoice so we're gonna get that worksheet by the name of it and the name of that worksheet is invoice now in this worksheet some things are gonna be static meaning they're always gonna be in the same place so like this current inventory is gonna be in this cell this is gonna be in this cell now this headers are gonna be in that same place now these items though are probably gonna shift up and down because we may add more items or remove some items which means this total as well is gonna shift up and down so up to this line 6 it's gonna be pretty much the same position for all that so let's just try to get all this stuff that's gonna be in the same place so for example like this is b3 cell so let's get the value in that so again we're gonna go to this worksheet invoice get the range b3 and get the value in that range so b3 was the cell similarly I'm gonna do it for this so I believe that was before then we need description quantity unit price total value now I could read each one individually but instead I'm just gonna read this entire range and just get parts out of it so the range is from be six through six and this time is gonna be get values because well it's not a single cell it's a range and now out of this let's just separate all the columns that we need so I'm doing headers zero actually this should be zero zero because it's the first row it's gonna come as a row out of this range so we need zero zero so that means the first row which is just gonna be one row anyways but it's not gonna know about that and in the first row I need the first column which is again zero so that's coming out of this array of headers and similarly this is the first column this is the second column this is the third column this is the fourth column from that array which are these values here so that's it for the easy part we got all the static values now we need to get all of these values now for that we have to first figure out how far down we have to go from this b7 and e7 range so I'm gonna do that by first finding the last row here which is going to be this row 10 and by that I'm gonna know to go one less to go to the last one so we'll find the last row by taking the worksheet and doing get last bro so this last row will be the row for the total the one before that will be the last item so with that our range is gonna go starting from this b7 so let's go get the range so it's row seven column B so seven and second column four column B and then we have to provide the number of rows and the way we can find how many rows we need to do it's gonna be if this is 10 which is our last row then to get to this one we should basically do nine but it's not gonna be nine we need to get the number of rows we need to go down not the number of the row we need but number of rows that are in this range which is gonna be three so to get to that three what we're gonna do we're gonna take this 10 and do - the 7 which is the first line in this so if we do 10 minus 7 that will get us the 3 which is gonna be the number of lines in this and we're gonna do that by taking this LR which is gonna be that 10 minus 7 and finally we need number of columns so number of columns in here is 1 2 3 4 and we'll get values out of that table so that should get us to our table range dynamically and finally we need to get this total stuff so this and this so I'll read the range again and this time we're not gonna use this notation we'll just use numbers so we need to get starting from the last row in that last row we're gonna start from column two which is basically this b10 and then we need to get one row and four columns and get values and then out of this total line we're gonna need the text from here and text from here that's similar to how we got this and this is gonna be in column number four which with zero index becomes three zero one two three so I think that gets us all the variables from the spreadsheet we need to work with so now we need to worry about basically placing all this in this HTML template and for that we're gonna use HTML Surratt's so let's just create a variable for it and the filename is going to be this email now to that email we need to pass some variables so we can do that by taking this object now and assigning some additional properties to this and those properties are gonna be basically all this information we need to send to that email so I'm gonna start with that h1 and that h1 is gonna be this h1 value that we have here we need to repeat this for pretty much all the other variables that we need to send over there you so the last one I'm gonna do is the actual data and if you remember we got this table range values which is gonna be basically this entire array so I'm just going to send it over to my template site now once we're able to send all of this over to our email site we should be able to access all these variables in here so I'm gonna go here and use brackets like this that's a way for us to access and print a variable with this equal sign in here so if we wanted just to run JavaScript we wouldn't do this equal sign but since I want to print that in here I have to do this equal sign as well so here I need to print this age one that I'm passing in here and for most of the stuff it's gonna be very similar so like this one for example you so I think I got all of those now I'm gonna skip this part for the time being and move to this end total part which is gonna be easy again before we get to this let's try to just get this on this site and try to send this email to see what happens so to do that I'm gonna take this HTML template and evaluate that and then we need to get the actual email text so we can do that by using this get content and we'll save this into a new variable and this should contain all our HTML text right now we should be able to log this out and see that if necessary which I should probably do I'm also gonna go with the step of sending the email out so there are a couple of ways you can send your emails out you can either use mail app which is gonna be very similar you can pretty much replace mail app with what I'm gonna use you're gonna get the same results I'm gonna use Gmail app because I find I can actually get better results out of that with timing of emails and everything so with Gmail app we should be able to use this method send email which as you can see has the person who's gonna receive this email I'm just gonna send it to myself so by the way to all the people who send emails to this email that email is not being read so I'm gonna go ahead and do the title line know the subject for this email and finally we need the body now this body only accepts regular text bodies so that's not gonna accept our HTML stuff so here you usually just type what they would receive if there is no HTML support you could also just build the text for whatever text you're gonna send them in that case I'm just gonna keep this simple something like this let me send all of these to a separate line and then finally the interesting part here we need to set an object with our HTML body which is gonna be the actual HTML body with build on top here just like that so with all of these set let's try to run this and see what happens so far so that's this function called my function we should probably name that but it's good enough for me it's called my function I'm gonna go ahead and run this all the permissions stuff is going to come up so we have to do that so that went through I didn't get any errors let's go and check our email so here we are this was sent see it says your monthly report so if I open this email let me zoom in a little bit that's pretty much what we got so it's not nicely formatted or anything but you can already see we got all those column names we got our total which is not formatted very well but other than that we got this which was again coming from here now we don't have this part because we didn't do the actual lines which is what we're gonna work on now now we probably don't have to send our emails all the time we should be able to also view that log see remember because I did this console lock to see the actual HTML body which is this so it shows the actual HTML with all the replacements that were done using our script so now let's go and figure out how we're gonna fix this part so first I don't want this to send emails for now so I'm just gonna comment this block and we'll work on this top part so now I have to work on this part where we did this one table range values and that was send to our HTML template again that's gonna be an array I'm gonna go back to my HTML side in here and what we'll do we'll create a little loop in here so I think we should be able to use for each loop that should be fine we'll try that and see what happens so the variable was called table of range values so now what I can do I can open these brackets and this time I'm not doing that equal sign because here I'm not printing out anything yet so I'm just gonna take that variable and do for each on that and this should be uppercase e JavaScript is case sensitive language so be careful assuming that v8 engine works on this part as well we should be able to just do an arrow function and in this arrow function we're gonna do what we're gonna do in this curly brackets now within that curly brackets I want to be able to just print out all this lines now the thing with that is that I need to break back to HTML so that means what I need to do I need to just close this brackets in here and then reopen them like this and I'm just gonna go back here and just do a couple of lines so basically this is my for each loop it starts here and it ends in here and in the middle because now we break out of that javascript thing we should be able to put our HTML now i've just realized that each line is gonna be a row so our HTML is not gonna only include columns it also includes this bro tanks so should place that in this loop okay now in this loop we should have access to this our variable which is basically the variable that's gonna be the row and in that row basically the first column is going to be the position zero second column position one etc and I need to use again these brackets to print things out and I'm gonna do it right here and instead of doing this we'll take that our and do 0 which will indicate the first column in that row and then I'm gonna repeat this for every other column but we're gonna change the column number and again in a race counts start from zero so this is the first column second third fourth which is pretty much our columns here 1 2 3 4 ok now let's try to run this and see what we get so I'm gonna go to my code run it and look at the log so this is that part this is our quantities now let's look at this here we go see we got like item 1 which is the totals coming from here and then we got the next row it's not formatted very nicely but it doesn't really matter and we got that third row which is basically this so that's good we actually got all the lines we needed out of that particular table now the only thing is that all these numbers are not formatted in a very nice way so we could format all those numbers in JavaScript but I think we could just get around this by instead of doing get values for this we'll just use this other method here so let's try that and see what happens and I'm gonna do get display values which basically means the actual thing that you can visually see in there so let's just save this and run the same code to see what happens Hey look at that now we got all the formatting of our numbers in here that looks much better now we probably also need that formatting well not for this that looks pretty good but we need that formatting for this part as well see this total number so I'm gonna do that for that total that total was the total sum and we got that total sound from this total line this one so that means I'm gonna change this to get display values as well let's just run this and see what happens here we go we got our formatting that looks good so the numbers are formatted well now that's good now we need to probably make our email a little nicer looking so let's go back to our HTML and try to do a little bit of CSS so one thing I want to do I want to do kind of this line on top that line is gonna be for me this div and because we're dealing with emails I'm gonna do some inline Styles here you definitely don't want to do this if you're actually creating some other HTML we'll set a height that's good enough for that and then I need this current inventory thing so this the way it looked like we need that a lot bigger and different color so that's this I'm just gonna copy and paste this thing and instead of background color that will be color because that's the text color and we don't need height but we need font size and I'm not sure what that font size is gonna be but I'll just do something again I'm not gonna match exactly all the sizes and everything I just want to have something that illustrates how this is gonna work so that's the font size for that we need font and the color for this thing so let's do that so the color for this was whatever that is so for this I'm just gonna do pink if you want to match color you can do a hex color here so just open Photoshop or whatever else you use and get a hex color and use it in here so this will basically just look like something like that I'm gonna just keep this and we'll also make this bold we can do that using font-weight property in CSS so all this is CSS so if you want to learn more about this you have to learn about CSS I want to use this line to space this things down a little bit I'm gonna use margin-bottom for that all right let's get to our table headings here so instead of doing every single heading maybe I can just pass the color to the entire TR the whole row so something like that all right let's see what else we got here we have this part I'm gonna skip this part for the time being let's do this so that's this thing okay so now we need like the stuff so you can see how this is like we have this grayish color and then we have this other a little less gray but it's still some shading and then the next one is kind of darker so we're gonna have to I guess take care of that and we're gonna do that in this look and the way we'll do that in this for each lobe we can actually pass two parameters Rho and the index and the way it's gonna work is that this is still gonna be this row but this index is going to be 0 1 2 3 4 for each line and basically we have to figure out is it one of the lines we need to color or not so the first one we want to color that's 0 then 1 we don't want to color then 2 we need to color something like that so it's gonna be pretty much zero needs to be colored too for something like this and the way we can figure that out let's just open this in this window so if we get something like 0 1 etc if I take that and do a modulus that means basically getting the remainder of 2 see what happens is that for 0 its 0 for 1 its 1 then 0 1 0 1 so we get this 0 1 thing that's gonna happen so we can check if it's zero then it's gonna be one color if it's 1 it's gonna be a different color now we're gonna do this using this modulus thing now we have to do it in JavaScript though not in here in our spreadsheet so right here I'm gonna just hit enter to continue with a little bit extra JavaScript and I'm gonna define a color pretty much right here and I'm gonna do any statement here I'm gonna say if if we take that modulus of I so this is how you do the modulus in JavaScript by using this percentage so I remainder of 2 so if that equals zero and we can do triple equals here then I want the color to be equal to gray I'm going to just keep this all in one line else I'm gonna make the color equal to light gray we could have done this with a little more prettier code but I'm just gonna keep it with this this is good enough and the way we can use that now we can use that variable later in our code so we want this row to be styled differently so I'm just gonna copy something that gives us background color I don't need the height right now and here where we have to do the color we're gonna again print our variable instead of that purple I'll just do that variable color that we've just created in here keeping everything else the same let's save this try this again no errors good sign let's go check our email there's our new email let's look at that there we go we got our varying color pattern here using our JavaScript now too dark but again I'm not matching colors you can go back and match colors if you want I'm just gonna add maybe a little padding to each one of these lines just to space this things out a little bit and maybe also remove this spacing within the cells that creates this white line so let's go to our table and do the border spacing on the table something like this now you might also want to to the right than the left so that's all gonna be in here so where was that aligned that was to the left the rest we want to the right right something like that so I'll just go here do a little padding for that too I'll repeat this for all of the rest something like that so hopefully that gives you an idea so I'm gonna just run this one more time or whatever it is it is so now those lines disappear these are aligned a little better we have a little nicer padding etc so now you can play around with this to make it look exactly the same but overall we demonstrated what we need to do to make this happen from that spreadsheet now just to test this out let's just go back and add another item to our spreadsheet to make sure that it's actually going to work out you so as you can see it works just fine we got our numbers we got our background colors looks ok so finally let's get to scheduling this email to go out so for that we're gonna go on top here rename this function to something better we don't really have to do this but you should so that's the name of this function I'm gonna save this now I'll go to file actually add it and find current project triggers and that gets us to this screen and as you can see there are currently no triggers so now what I'm gonna do I'm gonna just click on this add trigger and then now it already picked up that function but if you had multiple functions in that script editor you would have to select the right one which is this thing and finally we want to change the event source as time driven and make sure we choose when we want this to be sent out so if I go here see I can run it on a specific date or time or maybe I want this every week or every month so I'm gonna go with this and now we're gonna choose the date so the 5th of each month okay and if you want to get notifications of errors if something goes wrong with this code you can do notify me immediately or daily whichever one you want to do so tons of different ways and finally you could probably set up the time too so let's say we want this to go out 9 to 10 a.m. on that day and at that point you just hit save and you're done so that should have now time-based scheduled emails going out every month on that particular day with that setup we just did and if you wanted to remove this at some point just come back to the same place open this and delete the trigger which is what I'm gonna do right now and that's pretty much all I wanted to cover in this video thanks for watching please subscribe and I'll see in the next one

Channel: Learn Google Spreadsheets

Views: 46,297

Keywords: Email, html, table, Google Sheets, script

Id: fx6quWRC4l0

Length: 35min 41sec (2141 seconds)

Published: Wed Apr 15 2020