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 Sheets: Conditional Formatting with Custom Formula

Feature inner image credit: Samuel King Jr. 

Google Sheets – Conditional Formatting

Conditional formatting in Google Sheets is a powerful and useful tool to change fonts and backgrounds based on certain rules.

This tutorial assumes that you already have a basic knowledge of Conditional Formatting but would like to uncover the mysteries of the Custom Formula option.

In this post, I will guide you through the steps to build your own custom formulas in oder to:

  1. Apply Conditional Formatting across a whole range based on a value in one or two columns.
    1. Example 1: Conditional Formatting a Whole Range Based on One Column’s Cell Values.
    2. Example 2: Conditional Formatting a Whole Range Based on Selected Values and Formulas.
    3. Example 3: Conditional Formatting a Whole Range Based on Multiple Column Values.
  2. Apply Conditional Formatting across one column based on values in other columns.
    1. Example 4: Conditional Formatting of a single range Based on Another Column Value.
    2. Example 5: Conditional Format a single column range based on a value in another column – Multiple times.
    3. Example 6: Conditional Formatting a Single Column Based on Two Values.

Throughout the examples, we’ll look at various aspects of using Custom Formula to match, use formula functions and apply multiple conditions.

Before we hit the examples, let’s briefly go over accessing the Custom Formula in Google Sheet’s Conditional Formatting.

Continue reading “Google Sheets: Conditional Formatting with Custom Formula”

How do I Forward and send Emails for my Website Email in my Personal Gmail account?

Gmail, cPanel, web host like GreenGeeks or HostGator

One of the most frustrating things about using a domain email like imbatman@yagisanatode.com is that every time you want to check your email you need to log into your websites cPanel, navigate to the Email section, click ‘Email Accounts’ find the Access a Webmail provider like Roundcube and then wade through the clumsy interface to read and answer your website emails.

Groan!

Maybe you found that email forwarding link in the cPanel and forwarded your emails to your Gmail account on your own. Nice one! Have you ever replied with your personal email by mistake? Or do you dread having to go to back into cPanel to reply?

ugh!

What if I told you that I could make this sick, chore-ladened nightmare go away? What if I could tell you that you can receive and send your website emails from the womblike comfort of Gmail?

OMG, Yagi! Tell me more you sweet goat!

…um… okay, a bit creepy but sure.

Setup is pretty easy, but if you search for it on the interwebs, the instructions even from Google are a little disjointed and incomplete.

This tutorial is a two-parter:

  1. Setup my website email forwarding from my web host’s cPanel to my personal Gmail account.
  2. Setup the ability to send or reply to emails using your website email from your personal Gmail account. Teleport to this bit!

Let’s get started…

Continue reading “How do I Forward and send Emails for my Website Email in my Personal Gmail account?”

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”

How to display a date for one day in a week that automatically changes weekly on Google Sheets

Google Sheets – TEXT, TODAY, WEEKDAY

If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is niggling the back of your mind saying, “You can do this better!”

But the day-to-day race to get things done takes over and you move on to more pressing matter.

Until the next time you have to look at that Google Sheet and it starts bugging you again. Well, until you have a moment to fix it.

The Problem

For me each week I had a sign-up sheet for a makeup test for students. The coordinators who would add students to the sheet required that in the header rows, the date of the next Makeup Test be added for each week.

The makeup test was always at the same time each week: Monday at 15:30. I would then need to prefix this with the day and month. So it would look something like this:

Monday 28 Jan at 15:30

My original approach then was to open the sheet and change the date manually each week. Okay, I admit that on not just one occasion  I forgot to change the date much to the glee of the most persnickety of the coordinators who could happily call me out on my failure. Grrr.

I knew I could make this more efficient. I knew I could probably automate this process. Fortunately, the day finally came where  I found myself with 15 minute s of free time and this issue in my mind.

This post is the solution to the problem.

The Solution

Continue reading “How to display a date for one day in a week that automatically changes weekly on Google Sheets”

Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time

Google Apps Script, onEdit, Date, Google Sheets

Here is the scenario. You have a small business and you want to store your customers, products and sales information on separate Google sheets.

You’re probably going to have someone from your staff enter new customers, products are sales transactions.

We know if we get them to enter unique ID’s in manually that mistakes are going to get made. So how about we automate this process with unique ID’s based on date-timestamps.

uniqueID DateTimestamp Google Sheets

Why use a date-timestamp to create a unique ID?

Every year is unique. Every day, hour, minute, second and millisecond of that year creates a unique number. This mean that a new unique id will be create every millisecond for us.

Wow! Wow! Wow! Yagi! Just hold it one damn minute! You could  have multiple results each millisecond that would each be the same number!

Well, true if we were running a loop generating and publishnig our date-timestamp, we could have multiple numbers. However, we are generating this unique ID as an onEdit function when the user adds some information in Google Sheets to a cell and an adjacent cell  returns our unique number.

The user’s input and then the calls to and from the server to the Google Sheet will be sufficiently slow enough not to have a number generated multiple times a millisecond, so we are safe there.

We have the main idea of what we are planning, let’s move onto the example.

Continue reading “Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time”

Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project

Google Forms, Google Sheets (IMPORTXML), XML Path, WordPress

Note: This is part 3 of a larger project. Each part of the project is self contained should you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practice workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. You can access the beginning of the project here: 

Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project

Embedding a Live Google Sheet Graph that Updates Every 30 Seconds into a WordPress Post – Part 2 of Google Forms in WordPress with Live Chart Project

Where We Left Off

After creating and embedding a Google Form into our WordPress post, in our last tutorial we added a graph of all the results from the survey that updates every 30 seconds.

In this tutorial we are going to add a country selection to our form and then embed a country heat map into our post.

Geo Heatmap Google

Creating a Country Drop-Down List in Google Forms

The Countries List

Getting The Country Data

First off, we need to find a list of countries. I’m going grab that from https://www.listofcountriesoftheworld.com.  I could probably just copy the list and paste them in but I might want to use the list of countries again as a reference for other calculations in my sheet so I am going to go to my Google Sheet that is connected to my form and create a new Sheet tab named Countries.

Continue reading “Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project”

Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets

One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace.

Find and Replace is the penicillin of the the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff.

Yeah! Yeah! We all know about Find and Replace.

I know! I thought so too, but then I started to really use and identify how I could use it to quickly:

  • Change template sheets.
  • Fix bulk errors in formulas.
  • Change parts of cells.
  • Replace values in the whole spreadsheet, one sheet or a selected range.

Before we get started, you need to know the short cut for the Find and Replace tool. This will come in handy in all sorts of programs and apps.

  • PC – Ctrl + H
  • Mac – CMD (⌘) + H

I’m going to go ahead and continue the examples using PC, because, you know, Mac.

Continue reading “Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets”

5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets

It’s time to get evangelical peep! Release yourself from the yolk of the menu bar and it’s insidious demands on you coordination and time!

Break free from the right click menu! For is it not anything more than a proxy menu bar?! A veritable wolf in sheep’s clothing with no other design but to bring you back to the fold of the menu-using, mouth-drooling reprobates  who stand in the way of efficient progress! Can I get an Amen! I said, can I get an Amen!

No? Fair enough…

Let’s just get on with it then, shall we? After all, this is about efficiency, right?

Here are the 5 main shortcuts that I use in Google Sheets each and every day to save me a tonne of time. I’m going to give you a bit more than just the Keyboard Shortcut, I’m going to demonstrate how I use it with some clear examples.

Continue reading “5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets”

Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet. 

To me, this seemed like a pretty common task that one might face, so in response I decided to create a template function to easily do this and reuse on the same project or in other projects. 

But first, let’s take a look a the problem a bit more. 

We want:

  1. the freedom to select any number of columns from one sheet.
  2. to select the same number of different columns from the sheet we want to paste to.
  3. to paste to the bottom (append) the receiving sheet.
  4. to take into account varying header row heights.
  5. to have a “Completed” or “Copied” column in our origin sheet so that we don’t continue to paste all the data instead of simply what has been updated.
  6. have the data with formulas that we only want to paste the values for.

The Example

Let’s say we have a sheet that is updated regularly and we want to be able to paste data from columns A,B and C of this source sheet and append it to columns C,E and A of the destination Sheet. 

  • Copy Sheet 1 Col A -> Append Sheet 2 Col C
  • Copy Sheet 1 Col B -> Append Sheet 2 Col E
  • Copy Sheet 1 Col C -> Append Sheet 2 Col A

Take a look at our two Google Sheets 

SourceSheet - Google Sheets
DestinationSheet - Google Sheets

You can see in the Source Sheet (Sheet1) that we have 3 data columns and one column market To Copy. The To Copy column will be updated with “Copied” once the code is run successfully.

In the Destination Sheet (Sheet2) you can see that we already have some data there. We don’t want to overwrite it. We simply want to append our data to the bottom of it. 

Continue reading “Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another”