Google sheets entry validation and update script

Question

I have a field in a Google spreadsheet which will contain a valid timecode for film and tv. this is of the format 01:00:02:12 ie hours minutes seconds and frames.

I want to just be able to type as few digits as needed so 3 seconds would be 3 0 0 and then be validated and entered/formatted as 00:00:03:00

I have it working very simply from one column to the next eg

A1 I enter 300

A2 is set to =TEXT(A1 , "00:00:00:00") which of course displays 00:00:03:00

but I don't want extra columns so how can I make it such that when I exit a field after typing 300 it runs a validation that then replaces my 300 entry with 00:00:03:00 as needed?

Unknown Unknown · Accepted Answer · 2016-06-17T09:35:34

Here is a script that performs such substitution in column A (only):

function onEdit(e) {
  if (e.range.getColumn() == 1 && e.value.oldValue == null) {
    var str = ('00000000' + e.value).slice(-8);
    str = str.slice(0,2) + ':' + str.slice(2,4) + ':' + str.slice(4,6) + ':' + str.slice(6,8);
    e.range.setValue(str);
  }
}

Explanation: given a number like 54321, it will form the string 0000000054321, the keep only the last 8 characters 00054321, then slice them in pairs and insert colons in between.

The script does not perform any validation. Here is a more verbose version, which checks that the timestamp makes sense and refuses to change anything if it doesn't. (I don't know how many frames in a second you want, this one uses 24.)

function onEdit(e) {
  if (e.range.getColumn() == 1 && e.value.oldValue == null) {
    if (!(e.value >= 0 && e.value < 1e8)) {
      return;
    }
    var str = ('00000000' + e.value).slice(-8);
    var hours = str.slice(0,2); 
    var minutes = str.slice(2,4);
    var seconds = str.slice(4,6);
    var frames = str.slice(6,8);
    if (minutes > 59 || seconds > 59 || frames > 24) {
      return;
    }
    str = hours + ':' + minutes + ':' + seconds + ':' + frames;
    e.range.setValue(str);
  }
}

Google sheets entry validation and update script

1 Answers