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

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

Json

cJobject JSON serialization

Aligning cJobject serialization with javaScript function names zIf you are not yet familiar with the cJobject, take a look at How to use cJobject , using javaScript like objects in VBA and jSon. If you have already been using cJobject for […]

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

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

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

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

Database abstraction

JSON API for data abstraction classes

This is the dbab JSON API for  Database abstraction with google apps script, which is Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j.   One great thing about Google Apps Script is that you can expose an app as a web app, so that […]

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

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

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

No Picture
Json

Dynamic forms and jSon

Following previous developments on hiding data in excel objects, Excel jSon conversion and dynamic forms I figured it was time to combine all these threads, and have implemented all that as an integrated solution as […]

No Picture
Json

JSON deserializer

Completing the JSON deserializer/serializer I was missing being able to deal with arrays and could only deal with strings in the first version of this, but have finally figured out how to deal with this. […]