Fiddling with text fields that look like dates

Let's say you have a field in a sheet that says January 2017 that you want to treat as text, but it looks like a date. In the Sheet UI you simply add a single quote (') in front of the field, so it now says 'January 2017, but still displays as January 2017.  

That's all fine, but coming across values like this  in Apps Script can mess things up. When you read them you don't know they were escaped or look like a date, so when you write them back it will lose the escape quote and it'll become a date again, as the Sheet interprets the value as if it had been entered without an escape quote. 

I always work with sheet values using a Fiddler as described in  A functional approach to fiddling with sheet data and More sheet data fiddling .This class - Fiddler can be found in my cUseful library.  This example delegates  the business of column mapping, reading and writing values and looping to a Fiddler.

Here's the key, and it's also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j


Reading the sheet

Very simple with Fiddler - just create an instance and pass the sheet object that needs to be manipuated.
var fiddler = new cUseful.Fiddler (sheet);

Manipulating the values and writing back

In my example I have two columns  "title" and "description" that I always want to remain as a text value, in addition to various other manipulations that I won't bother to show here. 

I can use the .mapRows method of the Fiddler to change values in these columns for each row, then dumpValues() to write the updated values back to where they came from. I also prepend ' to the fields whose "textuality" I want to preserve.
  fiddler.mapRows(function (row) {
    // do whatever manipulation I want to do on each row
    // ...

    // then preserve text type of these two fields in case they were interpreted as dates.
    row.title = "'" + row.title.toString();
    row.description = "'" + row.description.toString();

    return row;
  })
  .dumpValues();


For a lot more on fiddler, and videos too, see A functional approach to fiddling with sheet data and Unique values with data fiddler
 

For more like this, see Google Apps Scripts snippets. Why not join our forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments