Setting up the workbook layout and formatHere we are going to create a workbook from the JSON manifest. The relevant section is below, and can be found at manifest.child("setup"). Each type of data is identifed by { "type": "someType" } and contains options {} for how to handle the API response, some formatting information. In addition, APIs often return time in UNIX format (see this post for how to deal with those in Excel), so convertTimes{} describes which columns need to be converted from Unix times, and where to put the resultant Excel Time.
{ "setup": { "types": [ { "type": "ticker", "options": { "timeFormat": "dd-mmm-yyy hh:mm:ss", "fillColor": "#F79646", "resultsStem": "ticker", "manual": false, "action": "insert", "columns": [ "high", "low", "avg", "vol", "vol_cur", "last", "buy", "sell", "server_Time", "at" ], "convertTimes": [ { "from": "server_Time", "to": "at" } ] } }, { "type": "trades", "options": { "timeFormat": "dd-mmm-yyy hh:mm:ss", "fillColor": "#F79646", "resultsStem": "", "manual": false, "action": "clear", "columns": [ "Date", "Price", "Amount", "tid", "Price_Currency", "Item", "Trade_Type", "at" ], "convertTimes": [ { "from": "Date", "to": "at" } ] } }, { "type": "depth", "options": { "fillColor": "#F79646", "resultsStem": "asks", "manual": true, "action": "clear", "columns": [ "Price", "Volume" ] } } ] } } Here's the codeWalkthroughDeleting existing SheetsAll the sheets to contain data returned from the bitCoin API will be called something like type_venue, so the first step is to delete any existing sheets for each type in the manifest
'delete all potential existing sheets Creating the new sheetsFor Each venueJob In workJob.child("venues").children Formatting the heading cellsWe can use the functions described in Playing around with colors in VBA to apply the hex color provided in the options to the headers, as well as to colorize the font to a suitable contrasting color. colorizeCell .Resize(, job.child("options.columns").children.count), job.toString("options.fillColor") For more on this see. Data driven VBA apps with JSON You can get me on Google plus, Twitter or this forum. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > excel to json and back > Data driven VBA apps with JSON >