Apps Script
Markup HTML from JSON with Apps Script and JavaScript
Apps Script/JavaScript library to generate prettified HTML Markup from JSON […]
Json
Google Authentication
JSON web tokens
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available directly from O’Reilly or Amazon. So far all the authentication examples have been based on the normal Oauth2 […]
Apps Script & Java Script
Using Apps Script for xml json conversion
One of the great things about Apps Script is that you can use the content service to create your own apis, without the need for a server infrastructure. In From Xml to JSON and Convert […]
Json
XML to JSON with VBA
In Delegating xml to json conversion to GAS I showed how you could get Google Apps Script to convert XML to JSON by posting the XML and getting back the JSON response – using VBA to illustrate. […]
Google API
Web Services
Interacting with APIs This site uses a lot of calls to Google APIs in order to connect Excel to web applications. There are a number of custom classes used everywhere such as Data Manipulation Classes and Excel […]
Json
Populating sheets with Rest responses
What can you learn here? Using cDataSet Using cRest Using cJobject Putting the Rest response on the sheets get it now Now that we have the tools to generate existing or adhoc rest queries, let’s look at how the data […]
Json
Making excel tables of the results
The rest excel library uses cdataset to manage the data it retrieves from queries. In Excel, it is often useful to use tables. You can easily create an Excel table from any cDataSet. See How to make a table from […]
Json
JSON arrays of objects
JSON data can go many levels down. Whereas the cJobject is designed to mimic javaScript’s capability to deal with this, eventually you will want to populate a two dimensional table in Excel with some data. Consider this […]
Json
ScraperWiki mashup
On his oUseful blog, Tony Hirst showed how to use Open refine plus various APIs to research which candidates were standing for election in the wards in which they live. This blog has unfortunately disappeared therefore […]
Json
Interesting scraperwiki scrapes
In Getting data out of scraperWiki I showed how to get data into Excel or Google Docs from scraper wiki public data. I thought i may as well start an occasional directory of interesting scrapes I came across. Of course these […]
Json
Getting data out of scraperWiki
What is scraper wiki Scraper wiki is an environment which allows you to put some structure around data published in web pages. In addition, it has an sqlLite datastore through which many users of scraperWiki make […]
Json
Fusion data to Excel
In Flight data from Fusion I showed how to get a large amount of data from various Google Fusion tables into a javascript app. Since the Fusion API is just a REST API, you can use the Rest […]
Json
Combining multiple rest queries
What can you learn here? Using cRest Multiple sources Combining Putting together multiple queries on same sheet get it now So far we’ve been considering Rest queries to be discrete. In this section we are going […]
Json
Rest Results Explorer
What can you learn here? Populate a treeview do REST queries interactively explore the results How to get rest API results into a treeview get it now In Create a treeview from json we looked at how to […]
Json
JSON stringify VBA classes
In javaScript and other languages, creating json representations of an object is a breeze. Something like JSON.stringify (theObject) will do it for you. Aside from liberating data from your application, it makes debugging so much […]
From VBA to Google Apps Script
How to populate Excel from jSon
As covered in jSon there are many ways to get jSon data into excel. You may be tempted to try to use Excel JSON conversion or get data from google docs. These techniques are for very specific use cases – […]
Json
Scheduling updates with task scheduler
Since this is a ticker collection application we want this to run every now and again. At first I thought about using excel to schedule itself, but too many things generally go wrong with that, […]
Json
Populating the worksheets from bitcoin api
Getting data from the bitcoin API The bitcoin public api can provide different types of exchange data for a number of venues. The endpoint for each combination is different, for example these are all valid endpoints […]
Json
Creating the dashboard
Making a ticker dashboard for all venues Next we’ll create a dashboard – most recent ticker data for each venue on one page that looks like this. The dashboard is described in this piece of […]
Json
Creating a workbook from JSON manifest
Setting up the workbook layout and format Here 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 […]
From VBA to Google Apps Script
Storing objects as values
Storing other objects in cJobject If cJobject was just about getting stuff in and out of JSON, then we’d stop here, but you’ll find that I use it in pretty much all the VBA examples […]
From VBA to Google Apps Script
Navigation
Iteration As per the examples in cJobject deep dive the most common navigation of the cJobject is by iteration through the children, or by direct access as covered in How to access children. However there are a few […]
Json
How to access children
Children As mentioned in the introduction, each cJobject is either a key/value pair or is the parent of some more cJobjects. Accessing the children of a cJobject is straightforward, and as close to the JavaScript as […]
Json
Access shortcuts
Children shortcuts As covered in How to access children, cJobect children are roughly equivalent to JavaScript objects’ children. However the syntax of VBA means that we have to be a little more verbose than JavaScript. To […]
Apps Script & Java Script
Ephemeral exchange JSON editor and viewer
The Ephemeral Exchange Console allows you to create, read and edit JSON data directly in the store. This makes it a great too for checking data for apps that need to read or write JSON data. As […]
Microsoft Office & VBA
Prettify dynamic grids
Using textboxes instead of grids. Adding grids such as Flexgrid to VBA userforms is fraught with difficulties to do with versioning, security and so on. Sometimes you have to just bite the bullet and use […]
Json
Dynamic Event Handlers
Creating event handlers dynamically Normally, when creating a control on a form within the development environment, you assign your code for event handling through the control. However, when your controls are created dynamically, clearly you […]
Microsoft Office & VBA
Delegating xml to json conversion to GAS
There are still many APIS that only respond in XML. Although Excel has XML native it’s still a pain to deal with, and I really wanted to abstract the data format from the processing of […]
Microsoft Office & VBA
Urbarama mashup
This is real interesting mashup using an new Rest to Excel library entry for the urbarama API. This is a repository of interesting architectural interests. The API is quite interesting in that you can you supply a max […]
Apps Script Specific
Proxy jsonp
If you are reading this, I guess you are familiar with this error message XMLHttpRequest cannot load http://dl.dropbox.com/u/14196218/Share/somedata.json. Origin null is not allowed by Access-Control-Allow-Origin. This is of course the same origin policy that prevents you being […]
Drive
Oauth2 step by step – apps script authentication for the Drive JSON API
In Drive JSON API for apps script I posted about an apps script library to help with the complexities of using the Drive SDK instead of the Apps Script DriveApp service. It needs Oauth2. Recently, the advanced […]
Json
Simple implementation of R- melt
Hadley Wickham has added some data management capabilities to R which are pretty useful for getting data ready for tabulation or visualization. One of these is melt(). Melt can do lots of things, but one that caught my […]
Microsoft Office & VBA
Automatic documentation
Automating document ,creating Google Visualization and static tables of VBA projects, serialize instances of VBA classes and generating Google Apps Script skeleton of VBA projects. […]
Json
cJobject deep dive
You’ll probably now be familiar with cJobject and want to do some fancier things. Here are some examples. You can download a vanilla cJobject workbook from the data access section of the downloads page, or construct your own with […]
Drive
Drive JSON API for apps script
In Using Drive SDK I described why you might want to use the SDK JSON API rather than the built in Apps Script Drive Class. Before you can start with this you need to authorize your access with OAUTH2, […]
Json
Data driven VBA apps with JSON
Separating coding from defining I think of 3 different roles in workbook app definition Consume – Use the apps and consume or maintain the data in it, and provide process, informational and operational needs Define […]
Json
Rest to Excel library
What can you learn here? Library of Rest Calls Populate excel tables Add your own A library to populate Excel tables with rest query results get it now I found myself writing similar code to […]
From VBA to Google Apps Script
Create a treeview from json
What can you learn here? Populate a treeview Use a cjobject Serialize anything to a treeview How to turn a cJobject into a treeview get it now Since the cJobject is just a tree type structure, it […]
Json
Excel to json and back
JSON and Excel using VBA classes that emulate JavaScript objects, parsing and stringifying JSON and dealing with data from external services. Includes a data driven REST-EXCEL library, and implementation of Google oAuth2 authentication […]
Json
Excel JSON conversion
Freeing Excel Data through JSON There have been a number of topics on this site dealing with the serialization of JSON, for example to create input to google visualizations, Hiding data in Excel Objects , Integrating Excel with […]
Json
How to use cJobject
What can you learn here? Get started with cJobject Short code snippets Abbreviated explanations Quick examples (cDataset.xlsm) This is intended to be a fasttrack to get started with the cJobject that are used throughout the examples on […]
Apps Script & Java Script
JSON and VBA
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available from O’Reilly or Amazon VBA does not naturally support JSON, but to be able to use this API (and […]
Microsoft Office & VBA
Automatic documentation from VBA – including function arguments
In documenting VBA procedures automatically I showed how to create web pages that documented your VBA projects. I’ve updated that now to also include a popup showing the detail of function arguments too. This page was automatically […]
Microsoft Office & VBA
Adventures in serializing VBA classes – automatically documenting your modules
Figuring out how to serialize native VBA classes to jSon is something I’ve been working on in the background for some time. If you’ve been following this blog or the related Excel Liberation Site, you […]