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

Four Digits to Time in Google Sheets with Google Apps Script

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

 

The  Google Sheets Formula Approach

Let’s take a look at the ‘easier’ Spreadsheet Formula-based approach first.

Warning. However, before we continue, this approach does not place the time value in the same cell as the cell the value was entered. For example, if you entered the 4 digit value ‘1445’ in cell A4, you could not use this formula to put it back in cell A4. You would have to put it in another cell like B4 or wherever you want it placed.

Step 1. 

Ensure that the column you will get the 4 digits from has its format set to text. If you don’t do this you will find that some of the four digit numbers with leading zeroes will have their zeroes removed and cause problems for the formula later.

You can set the format to text by selecting the menu Format > Number > Plain Text.

Time Value To Time in Google Sheets

Step 2

Next, select the cell you want to put the newly changed time into. In our example we will use the corresponding cell in column B.

In this cell, add the following formula:

=LEFT(A2,2)&“:”&RIGHT(A2,2)

Let’s break this down. LEFT returns the characters starting from the left and going to the right:

=LEFT(reference cellnumber of characters from the left)

For example if we have the four digit value “1423” in cell A2 and we only want the first 2 digits. we would call =LEFT(A2,2) which would return the value: “14”.

Next we use the concatenation symbol “&” to add the colon like this: &“:”&. 

With our “1423” example, you will have “14:” by this stage.

Finally, we add the right-hand side of the 4 digits with, you guessed it, RIGHT. The RIGHT formula works the same way as LEFT, and takes similar values:

=RIGHT(reference cellnumber of characters from the right)

Continuing our “1423”, the RIGHT formula, =RIGHT(A2,2), would display, “23”.

Combining it all together, “1423” is transformed into “14:23”.

LEFT and RIGHT formulas Google Sheets

As mentioned earlier, the limitations for using this approach are pretty clear; You can’t use this formula to modify the same cell you add information to (Unless you want to add it inside the LEFT and RIGHT formulas) also you cannot set the cell format to time (It remains as a formula).

The Google Apps Script onEdit Approach

Using Google Apps Script, we can edit the cell that you are editing (onEdit), and it will return the 4 digits to a time formatted cell. For example, you type in ‘1640′ and hit <enter> , it will return ’16:40′, or what ever time format you have set up like, ‘4:40 pm’.

Again, before we start we need to change the format of the column we will edit to ‘text’. We do this to ensure that any leading zeroes are included. You can set the format to text by selecting the menu Format > Number > Plain Text.

Let’s take a look at what we will create:

Four Digits to Time in Google Sheets with Google Apps Script

The Code – Basic

Let’s look at the basic code:

Set Globals

In lines 3-6 we set our global variables.

Line 4 sets the column we want the code to check for a 4 digit value. Line 5 sets the name of the sheet tab we want to check.

onEdit()

Once we have set up our Global variables, we go ahead and look at our onEdit(e) function (line 7). onEdit(e) is a simple trigger function that is executed when the user enters a selected cell. It takes the argument ‘e‘ which will be the event object, or in this case,  the values you will enter in the cell.

Get the Spreadsheet and Active Sheet

Line 8 and 9 call the spreadsheet ( ss ) and the specific sheet inside it (sheet).

Simple Validation of the Data

The next thing we need to do is to check that the sheet we are working on, the active sheet, matches the sheet we want to work on – identified in our global variables as SHEET_NAME(line 13). To get the name of the active sheet we call sheet.getName().

Line 15 selects the cell we are working on, the active cell.  On line 18 we ensure that the cell is set to text for editing.  We do this by adding .setNumberFormat("@"); to the selected cell, where “@” means that what ever is in the cell is text. There are more format patterns available here.

Our final bit of data validation on line 20 is to make sure that we are only editing the column we want to, in our case, column 1 (column A). We do this with another ‘if‘ statement ensuring that if the selected cell’s column (.getColumn) is the same as our COLUMN_TO_CHECK, then we are good to proceed.

Convert 4 digits to Time

This is where the magic occurs. First, on line 21, we get the values inside the active cell we are working on. Then, we separate the first and last two digits with a colon. We do this by invoking Javascript’s substr() (Sub String) method which can help us look at certain characters in a string.

Sub String  can take one or two arguments. The first argument (substr(start)) is where we would like to find our starting character. In our first use of the Sub String, we want the first value. For example, if we want the first (zeroeth) value of the text, ‘0922’, the starting value would be ‘0’.

The second argument is the length substr(start, length), this argument is optional and, if omitted will, automatically set to the remaining length of the string. For example if we want the first two characters of this string we would do the following:

To get the last two characters of a string, we don’t need to invoke the length argument. Instead, we simply call the length of the string minus the value from the right we want to start from. For example:

Back to our code and we can see that we have concatenated or joined (‘+‘) the two Sub Strings and added the colon (“:“) between them to get a time value.

Once we have our new value in our time variable, we set the value into the cell.

Finally, we can now set the format of the cell to hours and minutes (“hh:mm“).

The Code – With Data Validation

Perhaps we need to make some special rules to ensure that the users puts in 4 digits that are with time parameters. If there is an error then we will return an error.

Let’s see what we added:

Don’t include header

Line 6 adds a HEADER global variable. Because we are adding outputs for error handling, we don’t want the header title to be caught in an error so we need to remove the header from the cells to edit. On line 16 and 17 you can see that if the active cell is in a row that is greater than the height of the header we will only validate the cell.

Is it a number

Next, on line 24 we check to see if the characters inside the text string are numbers using regular expressions.  The cell returns the standard NaN, or Not a Number, if the text does not meet this parameter on line 47.

Has 4 digits

Line 26 checks to see if the data the user enters is not exactly 4 characters long. If it is greater or less than 4 digits, then it will return an error, line 28. If there are 4 characters it will move to the next condition…

Cell is not larger than 24 hours. 

On line 29 we check to see if the characters we are entered are not greater than  2400. To do this, we first must change the 4 character text value to a number using Number(cellValue) creating the cellNumber variable.(line 25). If the value is great than 2400 – because that is not a 24 hr time – then it will display an hour.

To further check we are using time values, we need to ensure that the the last two digits are smaller than 60 (only 60 minutes in an hour, right?) on line 32.

 

If all parameters are met, then we are good to proceed with adding the data.

Let’s take a look how the data validation looks.

Data Validation for onEdit - Google Sheets

 

 

Enjoy.

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *