Google Script – Email from a Google Sheet

Often times in Accounting or Finance, there is a need to track important transactions that happen throughout the month that are outside of expected results.

The old way of thinking was to have everyone email one person and from those emails, that person would take the information down and put into an excel tracker.

Eventually, people got smarter and started putting the excel tracker file on a shared network location, and set the excel workbook to shared. While this kept the person from having to manually take down all the information – often times the excel file would crash or lock out, and you would still end up with multiple versions of the files. Even when it did work there was still a manual effort to create the email that was sent out.

Enter GoogleScript on GoogleSheets. Not only can you solve for the dilemma of sharing excel files, but you can also write a simple GoogleScript that creates the email and sends it directly from the sheet.

Here is an example Sales Tracker:

You can see it allows the sales contact to go in and enter the relevant information. When using GoogleSheet everyone can access at anytime, you can even track changes to the file.

Once you have the Sales tracker established in GoogleSheets, now all you need to do is write a simple script to send the email. Keep in mind this is a simple GoogleScript, you can actually write this more complex and add styling to it that will make it look even more professional. This will be covered in a future GoogleScript video. For now we will cover the basics.

To access script editor – go to your menu bar Tools > Script Editor. Once you are in GoogleScript Editor, the code is as follows:

You can see the function is called sendEmail. We set 6 variables (email, Subject, sh, lastRow, data, tbl) for this version of the script. Setting a variable is done by typing  – var variablename = “what you are saving as a variable”. Keep in mind that var should be purple, your variable name should be blue, and anything you are saving as a variable should be in single or double quotes and brown.

Further breaking down the sh variable – we are using built-in GoogleScript functionality to get the sheet as a variable. This is important because when we are looking for the last row in the sheet, we can reference sh instead of getting the whole thing.

Further breaking down the lastRow variable – we use the last row functionality to allow for a dynamic amount of entries for the month. Sometimes you might have 10 unusual items, 20 the next month, etc.

Further breaking down the data variable – you can see we are grabbing all data from A1:H and then rather than having a number next to H we just add + lastRow, which in this case would be 18, but can change automatically depending on the volume of data.

The final variable before the for loop is the tbl variable, which uses the first html tag of <table> which starts building the html table for the email. We start this here so we can add row by row to the email. You can see inside the for loop we have tbl += and then a command. This means if you are to print the whole variable, it would contain everything that gets added through the for loop.

The for loop essentially takes the data length of 18 rows, and loops through each row 1 by 1, and then the for loop on the column is going 1 by 1. The reason you start with row 0 is that once you have gotten a range the array does not start at 1, it instead starts at 0 and counts up. However, if it is the first time you are getting a range then it starts at 1. I have bolded this sentence because this can be confusing if it is the first time you are using GoogleScript – many languages start the array at 0.

Another way to think about the for loop is that it starts with row 1 and then goes column by column 1-7 before going to row 2. At every intersection, it is inserting the table from the sheet by referencing data (the variable from up above) by the [row][col]. We added some simple styling of <th> which stands for table header for the first row, and <tr> for the subsequent lines. This will bold the headers and leave the rest unstylized. Again GoogleScript allows for all the functionality of building complex and highly stylized tables that we will explore later.

Once it is through the data, it ends the table with the backslash on </table>. You can see similar html styling in the </th> table header and </tr> table row.

The last thing we need to do is set up the email. This is built-in functionality in GoogleScript which is clear to follow. We utilize the necessary variables we already store and place it appropriately inside the curly brackets of MailApp.sendEmail.

Now you are ready to send the email – please see below:

As you can see GoogleSheets with GoogleScripts really beats utilizing excel – not only do you solve the sharing problem with excel, but you also eliminate the manual work of creating the email and sharing. In future articles, we will show you how to make it more fancy with some simple coding changes.