You have probably read that jSon works well with javaScript because ‘it is javaScript syntax’.
Consider this javaScript (actually Google Apps Script).
var items = {itema:1,itemb:’b’};
Logger.log (items.itema + “,” + items.itemb);
We correctly get this result
1,b
So let’s try this
var jsonString = ‘{ “itema”:1,”itemb”:”b” }’ ;
var items = eval( “(” + jsonString + “)” );
Logger.log(items.itema + “,” + items.itemb);
Again we correctly get the result
1,b
Finally,
var items = JSON.parse(jsonString);
Logger.log(items.itema + “,” + items.itemb);
Also gives us the same results
1,b
In javaScript, single and double quotes are interchangeable, so swapping the single and double quotes should make no difference
var jsonString = “{ ‘itema’:1,’itemb’:’b’}” ;
Well this still works
var items = eval( “(” + jsonString + “)” );
But this gives us SyntaxError: Unexpected token in object literal
var items = JSON.parse(jsonString);
Why is it different?
The reason is that jSon syntax is actually a ‘subset’ of the javaScript permitted object literal syntax, not equivalent to it. In the troublesome area of the use of single and double quotes (or even no quotes), the jSon specification mandates the use of double quotes only for both property names and values. The reason that eval works is that you are actually writing javaScript on the fly with it (which is why it is considered a security risk), so the syntax rules of javaScript apply – you are not actually parsing jSon at all.
Why is this important (annoying) ?
On the ramblings site and on this blog I’ve shown how to parse jSon in Excel/VBA using various projects to illustrate. Since it’s a custom class I was able to apply a loose interpretation to jSon parsing and accept the full set of javaScript syntax as well – for example, the Google Wire protocol for getting data out of Google Docs into Excel was not strict jSon (although I notice they changed it a bit at the end of last year).
In porting these projects to Google Apps Script, one of the key objectives was to minimize change so that projects could continue to exist in both GAS and VBA versions with not too much difference between them. The good news is that since there is native jSon parser I did not need to write one. The bad news is that it is a strict one. Using a practical example, illustrated in passing arguments in VBA – just like in javaScript, I introduced the concept of using jSon to pass optional arguments in VBA. However, because of the syntax of VBA we have to put double quotes round a string. With a loose parser, its no problem – we just use single quotes inside that – as in this example from optionsExtend – jQuery $.extend() like function in VBA and a simple implementation of R -melt
optionsExtend (“{‘outputSheet’:’meltOut’, ‘id’:[‘id’,’time’]}”, defaultOptions)
Although this is good javaScript syntax, and can easily be dealt with by the VBA custom parser, JSON.parse doesn’t like it all – since we would need to replace all single quotes with double. That would mean that in Excel they would need to be escaped with extra double quotes and we’d have an unreadable mess.
For the GAS version then, extendOptions(a,b) will take either native javaScripts object literals or properly quoted jSon strings and deal with them appropriately. This means that in Gas, the above argument would be invalid, whereas the one below – an object rather than a jSon string would be fine. Not much difference than theVBA parser, but something to be aware of.
optionsExtend({outputSheet:’meltOut’, id:[‘id’,’time’]}, defaultOptions);
For more stuff like this, visit the ramblings site or the associated blog. If you have suggestion for particular topic, contact me on our forum
