jSon parsing: single and double quotes and messing around with Google Apps Script and VBA

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

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

 var items = JSON.parse(jsonString); 
 Logger.log(items.itema + “,” + items.itemb);

Also gives us the same results

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 


About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


twelve + 15 =