Month: July 2019

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

Google Apps Script: isChecked(), switch, filter, map

One Checkbox to Rule them All

Now that’s a fantasy novel I could be my gums into.

Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of tickboxes and paste the same range etc. But can you really trust your users not to mess that up?

People are used to select-all checkboxes in their computerised lives. It’s always better to work with familiarity to provide a better user experience rather than try and teach the user on the fly how to do something your way.

With this in mind, I went about creating a select-all checkbox for Google Sheets. Here is a little demo of how it all works. All the black background Tick boxes are select All boxes. These boxes have been assigned a range of other checkboxes that will be either checked or unchecked depending on the main select-all boxes state.

Google Select All Demo with Google Apps Script
Select All Demo

As you can see, the select all checkbox only changes the tick boxes in the assigned range. It does not affect any other non-tick box data in the same range.

To get this up and running on your own project, all you need to do is copy and paste in the two code files in your Google Apps Script editor. The first code file is the function that runs the check-all code. The other file stores all the select-all checkboxes and the ranges that they will affect.

Let’s take a look at the code and then run through a quick use guide before finishing off with an example.

Those of you who want to dive into the nuts and bolts of the code, there will be more explanation of each element of the code at the end.

The Code

Continue reading “Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets”

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

Google Sheets: Intermediate Chart Building, workflow

This is Part 3 of this Google Sheets Intermediate Project Course. However, this part of the course can also stand on its own.  If you are looking for part one or two, you can get it here:

Part 1 || Part 2

In this tutorial, we will dive into how to build charts. We’ll look at how we prepared our data for our chart. We’ll then cover some more detailed ground so that we can get our chart just right.

We will create a chart displaying the day change of two USD-XRP currency gateway agencies Bistamp and Gatehub.

The data we will be using to build our chart can be found here:

XRP Google Sheet

Contents

Back to Top

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

Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range

Google Apps Script: checkbox, Google Sheets

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.

isChecked()

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).

isChecked() takes a range or a cell and returns:

Continue reading “Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range”

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

Google Sheets: ARRAYFORMULA, IFERROR, SPARKLINE, Conditional formatting, workflow.

This is Part 2 of this Google Sheets Intermediate Project. If you are looking for part one, you can get it here:

This project compares two currency exchanges rates, USD-XRP, in two different exchange gateways Bitstamp and Gatehub.

Contents

Back to Top

Recap

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 A and put it into Column B using 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.

By this stage everything looks like this:

Google sheets - Percentage Difference with Symbol
Tutorial – Google Sheet

Back to Top

Stage 2

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:

Google Sheets XRP Daily Exchange Rate
Back to Top

Highest Exchange Rate Gateway

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

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”