Scripts, scripts, scripts!

Ask for Help
97

Fixing broken timestamps

I’m sure I’m not the first to notice this, but the “timestamps” that Glide inserts into GSheets often get interpreted as strings. When this happens, any date/time formatting that you try to apply will fail, and you end up with an ugly looking set of characters masquerading as an ISO-8601 datetime (except it isn’t :face_with_symbols_over_mouth:)

This has been doing my head in recently, and it finally got the better of me and I decided to deal with it once and for all.

The following function accepts two parameters:

  1. The name of a sheet
  2. An array of column numbers to “fix”

I call it as part of an On Change trigger, and a typical call looks like so:

var sheetname = 'Sheet1';
fix_timestamps(sheetname, [3,7,8,15,16]);

Calling it as above will cause it to examine Sheet1, and convert any strings that it finds in the provided list of columns to proper date objects.

The function:

function fix_timestamps(sheetname, columns) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  columns.forEach(function (column) {
    var data = sheet.getRange(2, column, sheet.getLastRow() - 1, 1).getValues();
    data.forEach(function (item) {
      var obj = item[0];
      if (typeof (obj) == 'string' && obj != '') {
        obj = obj.slice(0, -1); // Ditch the Z, it's *not* GMT!
        var dateobj = new Date(obj);
        item[0] = dateobj;
      }
    });
    sheet.getRange(2, column, sheet.getLastRow() - 1, 1).setValues(data);
  });
}

EDIT: There was a bug in the original version of this script that would cause it to convert empty rows to 1970-01-01. I’ve modified it so that it won’t do that anymore. It could be made more robust, but it serves my purpose as it is.

10 Likes