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


Reading the sheet

Very simple with Fiddler – just create an instance and pass the sheet object that needs to be manipulated.

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;

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

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