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”

Google Apps Script – Create Custom Unique IDs in Google Sheets

Google Apps Script, Google Sheet

Sometimes something on the surface that sounds downright easy turns out to be quite a complex procedure. My recent experience in creating a Custom Unique ID in Google Sheets using Google Apps Script was a case in point.

In a previous version, I created a simple unique ID generator by taking advantage of JavaScript’s Date object.  It was a mere 41 lines of code. You can take a look at it here:

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

Then curiosity got the better of me, and I wanted to know how to create a custom ID generator. My requirements were simple:

  • It needs to be able to take any size number format. For example, from 1003 to 100000000000003.
  • It needs to handle leading zeroes. For example 0000653.
  • It needs to be able to take optional leading and trailing letters. For example, AR1340203, 3000484030BSTN, ENT900848933IRE.
  • It needs to generate a unique ID based on the pattern provided by at least one ID in the ID Column of the Sheet.
  • It needs to be able to look through all the unsorted IDs in the ID column and find the next largest number that will become the new ID.
  • It needs to be able to create IDs on multiple sheets.
  • It needs to be customisable and easily reusable in other projects.

For this project, the code would not handle the following:

  • It won’t handle letters between numbers. For example, ERC1299374ER3900TT.
  • It won’t handle non-alphanumeric characters. For example, 13948%$&)
  • ID’s must be of the same length and the newly created ID must have the same length of characters as the previous ones. For example, A99C >>  A100C.

The Results

After an afternoon tinkering, the results turned out pretty good. Take a look at the demo below:

Custom Unique ID Google Sheets

The  Code

Continue reading “Google Apps Script – Create Custom Unique IDs in Google Sheets”

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”

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

Google Sheets, Forms, WordPress,  HTML5, a touch of Javascript

Note: This is part 2 of a larger project. Each part of the project is self contained if 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

Where We Left Off

In our previous post I showed you how to create a Google Form and embed it into a WordPress post. The end result looked like this.

Feel free and complete the survey for fun and the unadulterated joy of surveys!

In this tutorial we will add a live Google pie chart of our results that updates every 30 seconds so that our viewer, …erh…you, can see the results as they come in.

Here is what our Chart will look like.

Continue reading “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”

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

Google Forms, WordPress,  HTML5

Note: This is the beginning of a larger project. Each part of the project is self contained if 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.

Intro

Welcome to Part 1 in our Google Forms in WordPress with Live Chart Project. By the end our project our goal is to embed a Google Form survey into a WordPress post.

In this post, we will look at how to create a simple Google Form to run a survey asking my viewers what paid version of Google Suite they use or if they simply use the free Google Apps services like Google Sheets, Gmail, Google Docs, Google Drive, etc.

The end result of this stage will look like this:

Feel free and complete the survey for fun and happiness.

Continue reading “Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 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 Sheets – How to Separate the First Name from a Full Name Cell

left, right, find, length

Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined. 

This fairly simple process can be achieved with the Google Sheets formulas left, right and find. 

If you are in a hurry, here are the formulas below: 

First Names

To get the first name we do the following: 

=LEFT(A2,FIND(” “,A2))

Where ‘A2’ is the cell that we have our full name in – in our case Vasco Nunez de Balboa.

When you have multiple formulas in a cell it’s often best to go from the inside out. So let’s first look at what FIND does. 

FIND looks inside the cell for the first value that we want to search for. For us, it’s an empty space, ” “. Find then returns the numerical position of that found item.  To do this FIND takes two arguments: 

=FIND(the item we are searching for, the cell or string the item is in)

For example if we are searching for the location of the space in
 Vasco Nunez de Balboa  which is in cell A2,  we would do the following:

=FIND(” “,A2)

Which would give the result: 6

There are five letters in the first name Vasco the the space would be position 6. 

Now that we have the position  of the space, lest just grab everything in the cell to the left of that space. We do this with LEFT

LEFT also take two values. The first is cell location and the second is the number characters we want to take from the left hand side. 

LEFT(cell location, number of characters from the left)

Now that we know the first space is character 6, the formula would look like this:

LEFT(A2,6)

We then replace the 6 with our FIND formula and we are good to go.

Continue reading “Google Sheets – How to Separate the First Name from a Full Name Cell”