Google Apps Script equivalents for common VBA functions
As part of creating a framework for dual maintenance of Google Apps Script and VBA versions of the same application, one of the key things was to produce a set of functions in GAS that […]
As part of creating a framework for dual maintenance of Google Apps Script and VBA versions of the same application, one of the key things was to produce a set of functions in GAS that […]
In previous posts I covered a couple of conversions of Excel VBA projects to Google Apps Script, with particular emphasis on maintaining compatibility between the solutions to allow for dual maintenance. Previous conversions have been a GAS […]
Passing arguments in VBA can be a pain. Consider foo(a,,,,b,,,,c,e) Of course in some cases you can use named arguments, but the called function needs to know how to do that – it doesn’t work […]
In javaScript, everything is an object. Classes don’t really exist because they don’t have to. To create an class like structure, you just do something like this var employee = {}; employee[‘id’] = 1; employee[‘grade’] […]
I had some questions about authentication from users of the Excel Rest library. Typically Rest queries are authorized through the use of a developer key issued to registered users, but some sites need authorization as […]
I only recently became aware of Google Moderator. It’s a very simple tool to get sentiments on topics, ask questions, get suggestions and so on. If you have a Google Site, it’s very easy to […]
In a previous post I covered how to navigate REST responses using a simple treeview control as well as how to create a Treeview from any jSon object in a few lines of code. Recursion […]
If you open an Office Document, you might occassionally find your self with an error that tells you that your “object library is invalid” , “excel has had a catastrophic failure” or you have an […]
In a post the other day i covered how to use the scriptcontrol to call javaScript directly from VBA. Considering that javaScript has plenty of useful methods for manipulating arrays and strings and so on […]
Those of you that read this blog, and the ramblings site, will know that the topic content is about equally split between VBA and javaScript. Until today, I had no idea that there was a […]
Yesterday, I showed how to get all the f1 circuit names and locations using the Ergast API along with the Rest-Excel library. Now we have that data to play with, why not visualize them on […]
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 […]
If you are working with Google Apps Script, one of your priorities is definitely going to be a simple timer. Everything takes longer than you are used to, and you have to be very careful […]
I got this idea from Tony Hirst’s great blog. The OpenCorporates API gives rich information many companies. Using Google Refine to reconcile, it cleans up many sources of data to a queryable datasource. So here […]
There are many Google Mapping Examples in the Excel and Maps Integration section of the Ramblings site. So much so that it can be quite hard to get started in a small way. There is […]
For those of you that have been following VBA to Google Apps Script, here is another one. This time, it’s a conversion of the Excel color ramp library. You can download the code for and […]
Today’s post is about how to use the jSon to treeView capability in a previous post, and apply it to explore results returned by Rest- Excel library. The library, itself a jSon structure, is visualized […]
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 […]
On the ramblings site, you will find lots of downloadable examples and explanations dealing with connecting Excel to Google Maps and Earth. Following numerous requests on our forum, here’s how to filter google markers according […]
Conversion effort A few weeks ago I wrote about starting my effort to convert a complex VBA project to Google Apps Script. I vaguely remember thinking at the time that this was going to be […]
In a previous post, I introduced the concept of local caching of Google Docs Spreadsheet Values to be able to optimize Google Apps Script without needing to change the structure of your code. This gave […]
It’s well known that reading all the values at once from a Google Spreadsheet is much more efficient than doing it one by one. You can work round this if you are starting from scratch, […]
Lets say you have an Excel workbook consisting of tabs, all in the same format, that various people completed. You want to copy them all to a consolidated sheet from time to time so you […]
You are ready to move to the cloud, but what about all those legacy vba apps? Are they really going to influence your decision such that you have to stay where you are? Moving to […]
Color Ramps In a previous post, I showed how to automate the\ creation of a heatmap in Excel. It occurred to me that this could be generalized to any color ramp. Here is an extensible […]
tagCloud in Outlook In a previous example, I showed how to create a tagCloud of various Excel data sources, with a downloadable example on the ramblings web site It occurred to me it would be pretty […]
The tagCloud class Today’s post will demonstrate how to generate tagClouds from various sources in Excel. As an example we will use some tweets retrieved by the rest- Excel library A ‘Mitt Romney’ type query generates this tagCloud […]
Combining multiple rest queries in a single Worksheet In previous posts, I covered how to get tweetsentiments into excel and how to get Google Finance data into Excel. Today we’ll look at how to build an application […]
Today’s post is about how to create a palette to use for a heatmap, and how to apply it to either an Excel Table of values to be compared, or to create surface chart and […]
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 […]
Interpreting sentiment from phrases. I came across http://tweetsentiments.com/ the other day, which is a service that tries to analyze the sentiment expressed in a phrase. The are using a technique called Support Vector machines. You can read […]
What’s trending on twitter to Excel In a previous entry, I described how to add new entries to the rest-Excel library. Here’s another new library entry. WhatTheTrend provide an API for twitter watching. You can use this […]
Excel Roadmapper from simple table data Some time ago, I showed how to develop a VBA roadmapper on the ramblings website. I’ve noticed that most of my visitors to that topic simply download the finished workbook […]
Managing stock portfolio in Excel In a previous entry, I described how to add new entries to the rest-Excel library. Up till now we’ve just looked at populating static data in a worksheet with data […]
Sorting chapter/bullets numbers Quite often you need to sort data that has some kind of chapter numbering, such as 1.1 1.1.2 2.1.1 2.12.2 A specialized form of this would be ipnumbers, for example 192.1.3.2 172.12.180.1 […]
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 […]
Rest – jSon – Excel in one line of code My recent post on how to Excel-jSon-Excel in one line of code, seems to have generated a lot of pageviews, so clearly there are a lot […]
Eliminating repetitive coding of Excel web service integration I find myself writing more and more web service to Excel integrations. Here are just a few examples of webservice-Excel integrations from the excel ramblings site. I […]
What are generated VizMap applications These are mapping applications integrating Google Maps, Google Earth, Google Visualizations and Twitter. Here is an example screenshot. They are data driven in the sense that the applications are generated […]
Twitter, Google Visualizations, Google Maps, Google Earth, Excel – all together. In various posts such as Flying around with Google Earth and The Royal Family and their homes as well as on the ramblings site I showed how to […]
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 […]
Ramblings web site has a new look Just spent a little bit of time on Google Sites cleaning up the ramblings web site. I think navigation should be a little cleaner.
In last weeks blog entry, I showed how to generate a Google Earth or Google Maps based application out of Excel. These applications now have the capability to fly around each marked entry in Google […]
Another VizMap generated appplication – the British Royal Family and their homes In the Excel ramblings site, I cover how to generate Google Earth and Google Maps Application straight out of Excel, without needing to […]
Changing the markers on Google Earth to something in your data Yesterday I introduced the capability to generate google maps and google earth applications straight from Excel. Linkin Park Marker on Red Square from Google […]
Google Visualizations on Google Map infoWindows and Google Earth Balloons A few weeks ago I wrote about generating data driven mapping applications straight out of Excel, with no javaScript required (by you anyway). It has […]
Creating Data Driven Mapping applications In a previous post I looked at the the various mapping packages and how they compared. Having concluded that Google Maps was the winner, I started to work on an […]
Geocoding Excel While looking at how to connect Excel to Google Maps, I implemented a capability in vba for each of the main Apis for geoCoding capability through a simple http: request. You can read […]
Excel and Google Maps. Following my previous posts comparing the geoCoding capabilities of Yahoo, Bing and Google Maps I have now implemented Adding markers to google Maps straight from an Excel Table. As usual this is […]
I have already looked at geocoding with Bing Maps -vs- Google Maps and Google Maps is a clear winner. Yahoo Placemaker is pretty good at first glance even though it claims not to be a […]
In a previous post I covered how you could use the Google Maps Api to complete and parse addresses for subsequent mapping directly from Excel. In the name of fairness, I implemented the same thing […]
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 […]
Excel Data Manipulation Classes I notice from Google Analytics the the cDataSet pages are by far the most popular on the site. These classes are used throughout all the projects on the site, and are […]
Mapping visualization layers from Excel The Google Maps API is a wonderful thing, allowing you to convert an unstructured and incomplete address and get back a whole bunch of structured and mapping data. Google is […]
Looking at Google Analytics Data for my site, I notice that there are 2 distinct populations. Those that look and read through a project article (5-10 minutes) and those that stay a few seconds on […]
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 […]
Sometimes you have a range of dates and you need to come up with a scale that makes sense for the dates you are trying to represent in a timeline. For example if your dates […]
In various posts I have covered ways to get data in and out of Excel and other PC based systems. The latest capability I have looked at is being able to get data live into […]
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 […]
Classes for Excel Data Abstraction In a number of articles and topics on my website i have referred to a collection of classes that I generally use to abstract Excel physical location from the data contents. […]
Diminishing returns for more effort You will be very familiar with the law of diminishing returns, where you get a lot of payback for your initial efforts (sometimes called ‘low hanging fruit’), then you have […]
I came across a problem today, where I wanted to create a Google Intensity Map based on a selection of people in my Outlook Global Address List. I thought it would be kind of easy, […]
Analytics with no Javascript anyone? My last post covered how to inject Google Analytics into pages where it was not really possible to introduce javascript. by using a gadget. But what about the case where […]
A generic analytics gadget. Following on from my last post on adding Google Analytics Codes to track usage of your gadget, it occurred to me that you could create a gadget whose sole purpose in […]
What you need It took me a while to find out how to do this, but it’s really simple.Implementing this means that you can track usage of your gadget, whether embedded in a web page, […]
Google Groups Since I get a lot of correspondence from people who have downloaded stuff, I thought it might be useful to create a group with this tagline. ‘This group is for those with Excel or […]
Using google code to host projects I moved the gadget and associated code to googlecode.com hoping to host it there. If you have never done this, it is somewhat complex. It can be found here. […]
Roadmapper update I finally got round to implementing the roadmapper as a google visualization gadget. All the source code and gadget package is available here along with the write up, so I wont repeat it […]
Google Gadgets This is the standard way to do charts in Google Docs, as well as being easily embedabble in web pages. In a previous post I started thinking about the migration path to Google Docs […]
Ive been using Google Docs as well as Excel for a while now, and certainly Excel is more comfortable a user experience. However it’s clear that Google Docs offer a great opportunity to offer services […]
I’ve been looking at how to use cookies to pass data between excel sessions and of course outside Excel. Having implemented a method of hiding data inside cells and Excel shapes, I figured this would […]
Roadmap Project Recap Some time ago I started to play around with a method to make Roadmaps from Excel data. Since working through the development of it on my Excel Ramblings site and in this article […]
Can we use cookies inside Excel This is a follow on from my thoughts around using JSON both to store data inside Excel shapes and cells, and also to pick up data from external sources. […]
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. […]
What next Thinking about what to work on next for the Excel Ramblings site, I wondered how I could pull together the work on JSON along with google visualization serialization to open up access to […]
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