Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen

Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue?

I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task.

Reminding staff to complete a task can be time consuming and irritating. What if you could send an email to all those staff members who haven’t completed the task with a simple click of the button?

In this tutorial, we will learn how to create a Google Apps Script HTML email mail merge service that will send an email to a staff member who has not completed their task, indicated by a missing tick on a tickbox.

We will use the sample sheet below:

the badger Google Sheet

The Google Sheets reminder is triggered by a custom menu item.  You can see it at the top right of the image.

When the reminder is sent, it collects the rows of staff that have not indicated that they have completed the task.  It then sends an email to those staff members  with the reminder below:

HTML Email Reminder Google Apps Script
Get it! Badger! Ha!

As you can see, I’ve added some custom HTML to highlight the header and modify the width of the image. The email contains a number of unique pieces of data gathered from the Google Sheet Row:

  • The name of the user.
  • The deadline.
  • The name of the sheet (In this case, Tester).
  • The number of days overdue.

It also contains a link to the check sheet for the user to update once they have completed their task.

Back at The Badger Google Sheet, an alert window appears once the code is completed and also indicates any emails that could not be sent.

To get started you can Make a Copy of the Google Sheet by following this link:

Continue reading “Google Apps Script: Create an HTML email reminder service from Google Sheet data.”

Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes

Google Apps Script: getRange, getLastRow, getDataRange, spreadsheetApp – requires a basic understanding of GAS

Finding the last row of a data range in Google Sheets using Google Apps Script is a pretty common task. We often do this to find the next available free row to insert new data or to copy an entire data range to put into an array in Google Apps Script.

Generally, for fairly clean data, or a small range of data, we would rely on two approaches to get the data we need:

  • getLastRow(): this will get the last row in a Google Sheet that has data in it. It determines the last row based on the last available row value in all columns in the spreadsheet.
  • getDataRange(): this will get the range up to the last row and column with values in it.

Let’s take a quick look at this clean data set:

Continue reading “Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes”

Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click

Google Sheets, Google Apps Script, GmailApp

Ever wanted a quick way to give edit permissions to a user and then send them an email based on values in your Google Sheet?

This task crosses my table several times a week. Whether it is sending reminders to complete a Google Sheets task or share a Google Sheet to complete a task.

You might be thinking that it’s pretty easy to just go up to the big green Share button on Google Sheets and plug in all the people you need to give edit permission too. But what if you need to give edit permission to a number of people that are on your Google Sheet. Ever noticed that the Share button gets in the way when you are looking through your Sheet for those names or emails?

When you normally share a Google Sheet you have the option to send a message, but what if you want to send a custom message with the user’s name? You are kinda stuck to a brief message which sucks.

So I decided that it would be best to create a template script that I could quickly adapt to suit my need in different circumstances.  Below is the result.

Basically, this little piece of Google Apps Script allows you  to plug in a custom:

  1. Email column.
  2. Columns you want to use to create a unique email for each person you want to send your message to.
  3. Email sent checkbox location
  4. An Image (I use a button) that the user can click on to send the email and give edit permission to the Spreadsheet.

Google Sheet necessary items for GAS email and share template

The script will then get all the emails and their associated columns you want to use to personalize your email. Remove the duplicates and then give permission to edit the current Google Spreadsheet and send them an email.

If you are in a hurry to copy and paste the code into your own project the script is below with a short Quick Start Guide.

If you are looking to learn how it all works, I will go into the details after the Quick Start Guide.

Continue reading “Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click”

Google Apps Script: Basic Beginners Guide to Using Strings in Code

Google Apps Script, Javascript, strings

Hey there, Yagi here, you’ve probably stumbled across this page from a link from one of my other in-depth tutorials. This is just a quick primer on Javascript Strings in Google Apps Script for the non-coder.

Here’s a bare-bones example of how a string of text might come together in your code:

The resulting log would  look like this:

strings in Google Apps Script

 

Strings, +, \n and arrays[value]

If you are unfamiliar with basic string syntax and joining (concatenation), basically you can write text inside a single (‘) or double(“) quotation marks. For example:

To join two strings together you can concatenate them with a plus sign (+).

Just remember to put a space inside your quotation mars at the end if you are going to join another word or the words will not be spaced and theywillbealltogether.

This is handy when you want to put things on separate lines in your code but it won’t be on a separate line when it is displayed. You can use the newline escape sequence \n to return the next part of a string to a new line.

To insert a custom variable into a string, one fairly beginner-friendly approach is to put it between two plus signs.

You can see in the meatz variable above, we have put our two meat items in an array. An array always starts at zero (0). So to get chicken we would do, meatz[0].

 

Meow get on back to your main tutorial and get learn’n.

~Yagi

Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column

Google Apps Script, Google Sheets, getRangeList, 3D arrays

In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does.

I might want to use this information as part of a report or grab emails from a sheet and send custom emails to clients or staff.

I don’t always need all the data in each row, but I may need a lot of it. The two standard approaches would be to:

  1. Get all the data and then in Google Apps Script just select the relevant columns you need.
  2. Iterate through a list of columns you want to get ranges of and select each range individually making calls to your Google Sheet in each turn.

This might be useful for small data sets and, to be perfectly honest, I have used both these approaches in the past, but recently I stumbled across a method in the Google Apps Script Sheet Class called:

sheet.getRangeList([A1Notation,...,...])

Now I think that the awesome gods of GAS may not have purposed this method with the thought in mind that it would be used for collecting range values. I kinda get the impression that it was more designed for applying formatting to the selected column ranges.

However, what I have found is that it seems to be pretty fast at collecting all the ranges and then, on the Google Apps Script end, getting their values.

The resulting values of using getRangeList() can be then pushed into a 3D array of columns with row values.

For those of you in a hurry, the code is below. We will move on to an example and explanation after. Feel free to reference what you need and discard the rest.

The Code

Continue reading “Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column”

Working with Google Apps Script in Visual Studio Code using clasp

Google Apps Script, clasp, Visual Studio Code, Node.js, npm, Windows 10

I don’t mind the Google Apps Script built-in online editor. It provides word completion, error handling, and pretty formatting, making it ideal to hash out a small automation project pretty quickly.

However, one of its major limitations is that after 1,000 or so lines of code and multiple files, it can start to get a little clunky. It also makes things a bit difficult when you want to use your own debugging or testing software. Further, if you have a HTML (CSS, JS) intensive app, it is quite hard to create and test the front end. Finally, it can be just damn comfortable slipping on a pair of your favourite slippers and using your preferred Text Editor or IDE.

This little tutorial is going to guide us through the steps that will allow us to work, push and pull Google Apps Script code from the online editor to Visual Studio Code. We’ll even be able to run the program from the terminal in VS Code and manage versions.

Fortunately, Google developed a tool that enables us to develop Google Apps Script code from the command line, clasp.

Setup Visual Studio Code for Google Apps Script

Continue reading “Working with Google Apps Script in Visual Studio Code using clasp”

Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets

Google Apps Script: getRowHeight, setRowHeight

Copying and pasting data while maintaining row heights and widths can be a frustrating business in Google Apps Script. You see, there is a difference in how to set column width and set row heights.

Let’s say we have already copied our data over with something like:

sourceDataRange.copyTo(destination)

Google Apps Script provides us with a great set of enumerators for copying and pasting, the CopyPasteType.

To ensure that we maintain the same column width in our destination that was copied from our source, we need to run another copyTo() method of our sourceDataRange with the CopyPasteType.PASTE_COLUMN_WIDTHS. So all together it might look a little like this:

Okay, okay, a little extra work, but pretty straight forward. We get our column width in the end.

Now by this stage, you might be thinking, Yagi, why are you even blogging about this. Seems pretty straight forward, ya just gotta do the same with heights!

Nope. This is the point where we just have to remind ourselves that we love Google Apps Script for her flaws and all. Right? Right?!

For, I am sure, a very logical reason for the developers of Google Apps Script, copying and pasting the width has to be done row-by-row.

Here’s how you would do it the long way.

Scroll down to The Shortcut if you just want to copy and paste the functions into your code.

Continue reading “Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets”

Google Apps Script: How to Connect a Button to a Function in Google Sheets

Google Apps Script: UI, Google Sheets

Ever wanted to use a button in Google Sheets to execute a function in Google Apps Script? Well, guess what? The process is super easy.

  1. Write your function in Google Apps Script editor.
  2. Create an image or upload an image and add it to your Google Sheet.
  3. Right-click the image, select the ellipse and Assign script.
  4. Type in your function name.
  5. Click the button to test. You should be up and running.

The Example

Let’s look at a super easy example of connecting a button to a function.

Imagine we want to have a button that, when clicked, reads the selected cell and displays it as an alert.

We’ll start with the code first.

The Code

Line 1, sets the function, button(). Our first variable, ui, gets the User Interface class (getUi()). The second variable, cell, then calls the SpreadsheetApp service again all for the value of the currently selected cell.

Now that our variables are set, we call the ui class and send an alert on our screen. Inside the alert (Line 5) we add some text plus our cell value.

When the function is run, you will need to accept permissions for the first time. Then, you will see that whatever cell you clicked, the function will read that cell value and display it as an alert on your screen.

With the function complete, we’ll move over to create the button.

Continue reading “Google Apps Script: How to Connect a Button to a Function in Google Sheets”

Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it?

Google Apps Script – AuthMode

I mean, when I run it normally, it works just fine!

This is an interesting problem I came across recently when reviewing one of my Google add-ons.

The Problem

The issue was that when I ran my Google Apps Script add-on code normally – in my case, connected to a Google Sheet – the add-on would appear just fine in my add-on menu. However, when I tested the script as an add-on ( run >>> Test as add-on…), as installed for current user but not enabled, my app would not appear in the add-on menu.

If I were to publish this app in this condition (and I may have…eep!), the user would be able to see my add-on in the add-on menu but not be able to access the link to run the app’s function without going into Add-ons >>> Manage add-ons… then click on Manage >>> Use this add-on. The user would have to do this each time they want to apply my add-on to a new Google doc for the first time. Not a good user experience.

The reason is that the first time the app is run in a new document, the Authorization Mode is set to none:

AuthMode.NONE

When the user opens the document for the first time, the onOpen(e) trigger function is loaded along with any global variables that are in the script.

This is what Google Apps Script has to say about this:

Google Apps Script onOpen AuthMode None
Source: Add-on Authorization.

So the problem was that one of my Global Variables did not abide by the scope of AuthMode.NONE.

How about we look at some example code to see how to identify the problem and work towards a solution.

Continue reading “Google Apps Script – Why isn’t my add-on showing up in the add-on menu when I test it?”

Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet

Google Apps Script: onEdit, Google Sheets

Sometimes you want to be able to automatically move a row from one sheet to another based on the value of a certain cell.

The Example

One of the first things that come to mind, and I am sure it does for you dear reader, is when I took advantage of the Great Chicken Transformation back in, oh, 2019, I believe.

Folk kept turning into chickens, while other folk were wanting eggs. It just so happened that I had the farm to make it all happen.

First, though, I needed to keep a tab of every person I knew and if they turned into a chicken. If they did, then they were destined for the pen.

…Note to self: it may be late at night, but dam Yagi, your analogies are tight!

Google Sheets and Google Apps Script to the rescue.

So first off I set up a sheet named: Plague. Here I put all the people I knew, so I could watch em good and propper.

Next, I set up a sheet named Farm. These are for the people who turned into chickens. No harm in profiting from a few newly formed egg layers, right?

Whenever a new transformation occurs, I find the person on the Plague sheet and then select “Yes”  to say that they have turned into a chicken and will now be spending their days on the farm. Upon editing (onEdit) this cell to “Yes”, the row is copied and pasted to the Farm sheet.

Just like this:

move to another sheet onEdit Google Apps Script

Continue reading “Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet”