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 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; }) .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 More on this topic available here
- A functional approach to fiddling with sheet data
- Unique values with data fiddler
- More sheet data fiddling
- Fiddling with text fields that look like dates
- A functional approach to updating master sheet
- Populating sheets with API data using a Fiddler
- Header formatting with fiddler
- Formatting sheet column data with fiddler
- Styling Gmail html tables
- Sorting Google Sheet DisplayValues