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”

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”

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML

One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the same unit. Before I can run my code I need to determine what modules are running and what units we are up to for me to run my automated code.

To do this I created a dialog box when the code is run from the add-on bar. In a few clicks, I can then choose the relevant modules and units and then run the selected code.

Let’s take a look at what the dialog box looks like:

Dialog Form in Google Apps Script

Upon “Submit”, the dialog box returns an array  of objects of checked values from the radio buttons that can be uses in the server-side Google Apps Script.

In this tutorial we will look at the following :

Continue reading “Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets”

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post a Google Sheets user wanted to change a four digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, Google Apps Script

Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.

For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Continue reading “How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.”