This is something I came across today when working on a project. I needed my Google Apps Script to look at a cell or range and determine whether there is a checkbox in that Google Sheet cell. If there is, then my script would do something. Otherwise, I would throw an error alert to the user.
Off I went searching through the Google Apps Script docs hoping to find something like an isCheckbox() method.
No such luck on such an obvious solution, but after playing around with some of the other checkbox methods, I did find a solid alternative.
I didn’t feel that the docs were up to their usual amazing standard here. Thus the explanation below (Yes, I am also procrastinating from my project, but I’m also making an online note for myself).
In the first part of our tutorial, you made a copy of the raw Exchange Volume data. Then in a new Google Sheet tab called Tutorial, you made a column of UNIQUE Date Time Stamps.
Realising that this was not user-friendly you grabbed just the date data from Column Aand put it into Column Busing the LEFT function. You then used some conditional formatting to highlight every Sunday automatically.
Next, you added two columns to display the daily exchange rates for the two gateways using FILTER. You then used two colours to identify which gateway had the highest or lowest exchange rate for that day.
You then grabbed the average of the two gateways before displaying their percentage difference.
In this tutorial, we are going to make it even easier for our reader, by setting up a column to display the gateway name of the highest exchange for each day. We’ll then put in a block of columns that will display data about the day change of the average of the two gateways. Finally, we will add a SPARKLINE graph into each row so the user can, at a glance, see how the average exchange has been tracking for the past 7 days.
By the time you are done your chart should look like this:
In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:
MIN and MAX
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.
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:
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:
'Form responses 1'!A2
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:
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.
Full disclosure here, I don’t really know all that much about cryptocurrencies. I knew about Bitcoin and Ethereum but really didn’t really invest any time and money into looking at these growing forms of value exchange.
But now I’m a little curious.
I thought it would be fun to see what the value of XRP was against a fairly standard metric like the USD and keep a record of this exchange rate daily over a month or so (Mrs Yagi just read the ‘fun‘ in the last sentence and rolled her eyes).
I also thought it would be pretty cool to see how many exchange providers (Gateways) that conduct USD-XRP exchanges are around and see if there is much difference their exchange rate day-to-day among them.
Fortunately for me, the XRP Ledger provides a freely available open-source ledger that can be accessed via the Ripple Data API. The API can return JSON objects from which we can extract the data we need.
Of course, to record and display the daily exchange-rates I went straight to Google Sheets. I gathered the relevant XPR data using Google Apps Script’s UrlFetchApp Class and pushed it to my Google Sheet with SpreadsheetApp Class.
Here is a live embed of the XRP Google Sheet that is updated via a daily time trigger via Google Apps Script.
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:
Delete Rows for Small Data Sets
// Deleting Rows Based on Cell Values
varSS=SpreadsheetApp.openById("YOUR SHEET ID");
varSHEET=SS.getSheetByName("YOUR SHEET TAB ID");
varDELETE_VAL="TEXT TRIGGERING THE DELETION";
varCOL_TO_SEARCH=ANUMBER;// The column to search for the DELETE_VAL (Zero is first)
//Reverse the 'for' loop.
In the Globalssection, 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:
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.
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.
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: 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 Google Sheets reminder is triggered by a custom menu item. You can see it at the top right of the image.
When the reminderis 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:
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 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:
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.
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: