Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1

Google Sheets: FILTER, ARRAYFORMULA, UNIQUE, WEEKDAY, LEFT, MIN, MAX, IFERROR, Conditional formatting, workflow.

In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:

  • FLITER
  • ARRAYFORMULA
  • MIN and MAX
  • IFERROR
  • SPARKLINE
  • UNIQUE
  • WEEKDAY
  • LEFT

We are then going to make our Google Sheet more presentable by doing some neat tricks with Conditional Formatting.

However, probably the most important lesson is to consider throughout the whole process is the workflow of this project. I will try and get you into my headspace and talk about:

  • How I came to do things in a certain way.
  • What I tested before applying to my Google Sheet.
  • The order I did things to create the test.

When I am learning something, I find the most benefit in learning by following a project. As you follow along you don’t have to read everything. Actually, I encourage you to get the basic idea of the stage before giving it a try. Then come back to the tutorial for troubleshooting.

I hope you enjoy the following project…

Continue reading “Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1”

My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?

Google Sheets – Intermediate, Arrays, Form Data

Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this:

Form Response Tab Google Sheets

You probably don’t want to mess with this tab because the Form is still live.

Instead, you decide to create a new Google Sheet tab that you want to automatically transfer all the data into, including the current form response data and any new form responses you might get.

The Common Mistake

A common, though mistaken, approach to this is to do a cell-by-cell transfer of data. For example, we would grab the first data cell of our “Form responses 1” Sheet tab and in a new sheet tab cell we would write:

We would then drag that cell across to the right to cover all the columns. Then all the way down to the bottom of the page to cover the current responses and any new responses added.

That might look a little like this:

Cell-by-cell display of data from another Sheet Tab

The problem

This looks like it might work, right? Let’s test it out by adding in a new form response. For me, it will be the 6th response and will appear on row 7 of the ‘Form  Responses 1’ sheet tab.

Continue reading “My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?”

Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Google Apps Script, SpreadsheetApp, Google Sheet, Iteration

A not too uncommon task for Google Sheets users is to delete rows based on a cell’s values in a certain column. Often you don’t want to change the row position of the values so sorting the values and then deleting them in blocks is out.

All you want to do is iterate through the rows in the Sheet searching your selected column for the text or value that will trigger your code to remove the assigned row. Then delete the row.

Rinse and repeat until all rows have been checked.

This task can be automated with Google Apps Script in a number of ways depending on the size of your data set.

Small Data Sets

The most logical approach to delete rows in a small data set might be like this:

In the Globals section, lines 5-7 get the Spreadsheet, Sheet and Range Data. We use the SpreadsheetApp Class to get this information. For your own use, you would change the SS and SHEET variables to suit your project. The RANGE variable is the full range of data

Next, we have the parameters we will use to decide what rows we will delete. The DELETE_VAL variable is the text value in the row you want to delete. When the code sees this, it will delete the row.

The COL_TO_SEARCH variable tells the code which column to search. In your Google Sheet, you will have a letter across the top to determine the columns. We want to change this to a number. In coding, numbers start at zero. So, if your column to search is, say column “D”, then you would count:

0:A, 1:B, 2:C, 3:D

deleteEachRow()

Continue reading “Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet”

Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

Google Apps Script: Time Triggers, ClockTriggerBuilder, ScriptApp

One of the most powerful things about Google Apps Script is the ability to automatically complete tasks with time-driven triggers. You can set up your code to run any one of your functions in your script by the minute, hour, day, week, or month. You can even set it to run based on a specific date.

Have a look at the time-driven trigger options below:

    • Specific Date and Time: Based on: YYYY-MM-DD HH: MM format
    • Minute: Every minute or every 5, 10, 15 or 30 minutes.
    • Hour: Every hour or every 2, 4, 6, 8 or 12 hours.
    • Day: At a time within an hourly bracket. For example:
      • Midnight to 1 am,
      • 3 pm to 4 pm
  • Month: On a specific day of a calendar month at a specific time. For example:
      • Every 3 day of the month at between 3 am and 4 am.

There are two approaches to setting up these time-based triggers. The first one is by simply using the Trigger creator inside the G Suite Developer Hub. This allows you to select the function that you want to add a trigger to for your project and fill out a simple form to determine when you want your trigger to run.

Gsuite Developer hub time trigger

Alternatively, you can create a time trigger programmatically using the ClockTriggerBuilder class.

In this tutorial, we will build on a previous project we’ve called The Badger, that contains a task check off Google Sheet. If the user does not check off their sheet by the due date, then we can send them an HTML email reminder.

Google Apps Script email remind from Google Sheet The Badger 2

Don’t worry, I will provide the starter code and the Google Sheet for you in a minute. However, if you want to learn how I created the email reminder for The Badger you can follow the tutorial here:

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

If you want to see how I created the Spreadsheet, you can check out this link:

Google Sheets: How to create a task completion check sheet.

Setting Up

Continue reading “Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list”

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 Sheets: How to create a task completion check sheet.

Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet 

Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile them later.

The following tutorial is a simple tick box-based Google Sheet that the user ticks when they have finished writing their quarterly report. I use something like this in my day to day to keep track of a number of reporting processes.

This is very much a follow-along guide for Google Sheet Beginners to help develop some basics skills and think about how to design Sheets for the workplace. Feel free to read what you need or skip to the end for a link to the google sheet for you to make a copy of.

Here is what we will complete by the end of the tutorial.

Task completion check sheet google sheets

If the user’s tick box had not been ticked in Column ‘C’, Column ‘D’ will automatically report Overdue in red and Column ‘E’ will report the number of days overdue.

There is a space set aside for administration to note the reminders that they have sent to the user and the last date the reminder was sent. Admin can easily copy the emails and send a message to those staff who have not completed their report by the deadline.

Let me walk you through the workflow for creating this:

Continue reading “Google Sheets: How to create a task completion check sheet.”

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 Sheets: How to hide formula error warnings where there is no data or the data divides by zero

Google Sheets, IFERROR, ISBLANK, IF

Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right?

Formual errors referencing empty values Google Sheets

What if I told you that you could hide all those messy errors? What if I told you that you could truly have that pristine nirvana that you have been imagining that your Google Sheet project would truly be?

Drink the Kool-Aid and strap in, my friends, as your very own guru in a goat takes you on a journey of enlightenment into hiding formula error when:

Continue reading “Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero”

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: 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”