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 […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]

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. […]

No Picture
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, […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]

No Picture
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 […]