# Scripts, scripts, scripts!

**URL:** https://community.glideapps.com/t/scripts-scripts-scripts/18965
**Category:** Ask for Help
**Created:** [November 26, 2020, 6:15pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965 "2020-11-26T18:15:21Z")
**Posts on this page:** 1
**Showing post:** 97

<div class="post-metadata">

### Author: ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 17, 2021, 3:41pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/97 "2021-01-17T15:41:31Z")

</div>

**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](https://en.wikipedia.org/wiki/ISO_8601) datetime (except it isn’t 🤬)

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.

---

_[View the full topic](https://community.glideapps.com/t/scripts-scripts-scripts/18965)._