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 […]
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. […]
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 […]
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 […]
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 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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 – […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 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 […]
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 […]
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 […]
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 […]
If you are familiar with this blog and the Excel Liberation site, you’ll know that there is a pretty significant focus on getting stuff in and out of Excel. Ease of JSON and javaScript like […]
jSon /VBA functions have always been the most popular topic on the Excel Liberation site and forum. The interesting part about this though is not so much about parsing and stringifying jSon data (which after all is […]
In using Google Apps Script as a jSONP proxy, I covered how you could use GAS to get over javaScript same domain restrictions. If you need to access jSon data from javaScript, jQuery and so […]
Thanks to Tony Hirst blog, where he demonstrates the Ergast API I have another entry to make in the Rest-Excel library. This time, it’s all the F1 races to start with, along with the possibility […]
Back to the theme of ‘do it in one line’ today. Thinking about some the jSon – Excel capabilities on the ramblings site , and the structure of the cJobject class in which they are […]
Abstracting an Excel data source. In almost of all of the downloadable examples on the ramblings web site, I use a set of data abstraction classes to separate workbook data from its workbook location, and […]
Following the post earlier on rest-jSon-Excel in one line of code , I found that you can do the research and add a new library entry in about 10 minutes. Here is a query to populate […]
Excel-jSon-Excel in one line of code On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a […]
There is a simple API to retrieve google books data. There was an interesting question on our forum yesterday asking about how to quickly populate a spreadsheet with book data based on the ISBN number […]
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 […]
Yesterday I posted an update to the data abstraction classes for Excel. I realized that there is also a method to convert that data into a JSON string. The following code would convert a worksheet […]
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. […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines