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

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 – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Google Apps Script – UrlFetchApp, SpreadsheetApp, Ripple API, Time Triggers

I have been very fortunate of late to have the patronage of the Ripple XRP cryptocurrency community via XRP Tip Bot and Coil. This is no small part due to the support of user Recreational Rex from Twitter.

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.

Continue reading “Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example”

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

GSuite Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail

Gsuite: Groups for Business, Gmail

Note: This post is for those who have Gsuite admin permissions. 

GSuite groups are a great bulk email tool. They can be used:

  • To send announcements to a group.
  • As a collaborative email for teams to communicate with each other.
  • As an internal or public facing support email.
  • To send emails and notifications to groups that may require a response only to the sender.

Surprisingly, all these reasons for using group emails all have different priorities and expectations for receiving and replying to emails.

Continue reading “GSuite Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail”

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