In Using the Google Sheets V4 API from VBA to integrate Sheets and Excel I showed a VBA API that could be used to talk to the Sheets API, to create some integration between Excel and Office. […]
Acknowledgement Much of the JavaScript and d3.js came from Mike Bostok’s site. If you use any of this material, please be sure to maintain the acknowledgement for his work. There have been a few features added […]
A whole bunch of front end platform libraries that go together really well, but that are hard to grasp and get started with. Here’s a bunch of snippets to help show how all these things interact.. […]
As Google APIS continue to improve there are more ways to integrate them. The pages on this site reflect the technologies available at the time, so I thought I should bring together the pages that […]
What can you learn here? Using ramblings libraries Short code snippets Abbreviated explanations Complex stuff in a few lines of code get it now This section is going to cover how to do complex stuff in […]
Now we know how about Hiding data in Excel Objects, Excel JSON conversion and Dynamic Forms let’s combine all that as an enhancement to the Roadmap Generation project. The objective is to embed traceability data in the generated roadmap shapes so […]
Implement a popup form at the mouse position get it now Now that we know How to position a userform on the mouse, here’s a simple example of an implementation that uses such a form to […]
What can you learn here? scriptcontrol object call from VBA use javaScript The ScriptControl Object This object gives you access to a scripting engine that will accept scripting in both javaScript and VBScript. Since this site has […]
What can you learn here? Argument passing ByREF versus byVAL Optional and variable arguments Argument passing Nowadays passing arguments to procedures is intuitive and the mechanics of how that happens is mainly the […]
What can you learn here? Timers Scheduling tasks Progress bars Dealing with asynchronous events VBA has Event Processing capabilities. This is how it is able to deal with mouse clicks, form events and so on. […]
I covered the mathematics of color matching in the VBA implementation in Find nearest color match. This is the Google Apps Script implementation. I’ll be using the CIEDE2000 algorithm to measure the distance between colors. One thing I […]
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 […]
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. As with most VBA authentication processes on this site, authentication is handled through the […]
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. All code samples can be found on Github. Note that that VBA samples have […]
The REST API interface is pretty straightforward, but it is JSON. VBA doesn’t natively support JSON, but this library includes cJobect , which deals with all of that for you. This packages all the things you can […]
There are many Google Apps Script projects and snippets scattered around this site. I’m going to try to organize them in this page, but it will take a while. If you find something on the […]
The REST API interface is pretty straightforward, so you can recreate this with UrlFetch if you like. However this packages all the things you can do from the client in a simple library, and is […]
Ephemeral exchange has gone serverless (for what this means see Going serverless with Firebase), and has a new home. What’s changed The back end is completely different, but the front end interface is pretty much the same […]
Sometime you need to exchange transient data between platforms, but they don’t talk to each other very well. For example you could be launching a webapp with some data generated from Excel, but you want […]
Apps Script runs on Google Servers, probably the Rhino JavaScript engine running on Google App Engine. Rhino is written in Java and the implementation used by Apps Script seems to be at about ES5.1, which […]
I wasn’t completely satisfied with the distribution method for the assets associated with my first Apps Script book and subsequent video course. Most of the assets were Apps Script files, and the delivery method was via […]
The Apps Script Script service is deprecating the getProjectKey() method. At this time it’s not clear when or why, but it does mean that if you are using the project key to create Oauth2 redirect […]
Google Apps Script is the script language for Google Apps. If you are an Office user, think VBA, but in the cloud, with all the connectivity and share-ability that brings, and as an added bonus, […]
It’s a common pattern in JavaScript to do this to assign a default value to a variable : var theThing = someThing || defaultThing; In other words, if someThing has a ‘falsey’ value (false, null […]
I’ve been dong a lot of writing over on Desktop Liberation looking at techniques to run Apps Script/JavaScript code directly from VBA, In this post I’m going to talk about how useful this can be if […]
You are probably familiar with the Google Apps Script Lock Service, which is a way of preventing concurrent access to sections of code. It works well, but the problem is that it’s a fairly blunt […]
There was a question on the Google Apps Script Community the other day. It went something like this. This is an important question, because if you have a webapp, potentially used by many people – […]
In Analytics and GAS libraries I described how I had started to use Google Universal Analytics to track what usage was being made of Google Apps Script Libraries (and even desktop based libraries for Excel). A […]
As you will know by now, ScriptDB is now deprecated. A year or so ago, I published some articles on alternatives to ScriptDB on the desktop liberation site, one of which was Parse.com. This has turned […]
If you are a regular on this blog or on the Excel Liberation site you’ll know that I’m a great fan of scriptDb – the cloud base noSql database for Google Apps Script – and like […]
In December, Google released new Sheets, saying they are faster and better than the old – including working offline. I’ve made a couple of tests to compare the old and the new – a simple operation […]
Doing cross domain requests for JSON data from client jQuery apps are hard work. In the past, you would have needed a data source that had implemented JSONP, but nowadays you can use CORS (cross […]
If you use blogger.com you may want to get some data about your posts into a spreadsheet for analysis. For example, the d3 concept browser analyses the Excel Liberation site and blog each night. There […]
Encryption/decryption library for Google Apps Script. Amit Agarwal recently showed how to encrypt/decrypt Email messages using a Google Apps Script application in this post The encryption method he used is SJCL, maintained by the Stanford Security Lab, […]
I’ve never been shy of writing up something I don’t know much about, and today’s post is no different. A few weeks ago I’d never heard of Pinyin; today, here’s a couple of Google Apps […]
I recently posted about a way to navigate sites and blogs by topic using a d3.js force diagram. Following that I was thinking some more about the trend away from organizing data into predefined folders […]
Here’s the daily visits to the Excel Liberation site over the past couple of years. But what’s that over the past 3 weeks, starting 3rd June ..a 40% sudden increase that seems to be sustaining. On […]
When using services like Google Fusion API, sometimes you get errors because of over quota attempts – too many requests in too short a time, or the infrastructure is just too busy to service them. […]
The other day I was looking around for an example of how to do digest authentication in Google Apps Script (or even plain javascript). I found plenty of theoretical discussions on it, but no actual […]
API for comparing colors I continue to find the topic of comparing colors a fascinating one. As an experiment to see if I could create a REST API serving up JSON and JSONP from a […]
A little while ago, I published an item on GAS performance, showing that a complex calculation test would take about 100 times as long on GAS as the same thing in regular javascript running locally […]
As you know the language of Google Apps Script is javaScript. So you probably have a lot of useful code buried away there, that could be used in web apps. One solution is to copy […]
Just how slow is Google Apps Script ? I’m a big fan of Google Apps Script, but I’m really fed up of seeing “Execution time exceeded”. Obviously data access times can be unpredictable, and although it […]
In Google Apps Script Color functions I introduced a bunch of functions to manipulate various color spaces from GAS. This was a migration from the same thing in VBA. I extended this to include additional color […]
I’m going back to Google Apps Script for today’s post. I’ve been on a little diversion playing around with color in VBA, so now it’s time to implement some of that in GAS. If you have […]
Someone asked in our forum yesterday about Facebook Query Language – FQL. Could we get data straight into Excel from facebook? I dont use facebook, so I haven’t paid much attention to it. I had […]
I’m finding this ability to embed prettified source code a google site gadget or iframe really useful. The other day I added the ability to get and publish source code directly from a url (in […]
If you’ve been following the various posts about publishing source code from gists, google apps script, scriptdb and so on, you may have realizes there was one missing link. Publishing source code direct from a […]
It’s been a while since I played around with d3.js. Previously I’ve covered Sankey diagrams, trees, force diagrams and a few others – all straight out of excel. A couple of suggestions on the Google […]
I posted how to include and prettify snippets from GAS, Gist, and scriptDB the other day, adding the ability to select particular functions from within modules. See the Excel liberation site for more detail. With Google Apps Script, […]
In publishing gists and publishing Google Apps Scripts I showed how to prettify and embed snippets in web sites, blogs and so on. However, one last requirement is to be able to publish only particular functions. Although […]
The other day, I showed how to prettify Google Apps Script snippets and scriptDB contents, include them in web sites, blogs and google sites, and encode them in jSon and jSonP using the GAS content […]
When working with spreadsheet data, I always use sheet caching – a class in the mcpher library. This gives great performance improvements and simplification for processing Sheet Data and other attributes. You can use it […]
Usually on this blog and the associated site I cover topics to do with getting data in an out of Excel. Lately though, I’ve been playing around with using Google Apps Script as a proxy and […]
In a previous post I showed how Google Apps Script could return the contents of a script as Json so you could format that for including script samples on web sites. Today, here is how […]
GAS just keeps getting better. I was thinking the other day about how I might be able to embed sample Google Apps Script code in Google Sites, or other web sites in the way that […]
Excel liberation is all about being able to step outside Excel, whether it’s getting public data, integrating with other platforms or in the case of today’s post, interacting with and delegating processing to the cloud on […]
I don’t know about you, but I have trouble keeping all my various Google Apps Script shortcuts and keys (links to workbooks, links to content service queries, library keys and so on) under control and […]
In the builtwith.com api rest library entry for Excel I covered how to get data about a given web site using the builtwith.com API. I went against my usual rule here, by including an api that […]
I came across a great site, builtwith.com, that shows the technology being used in a given web site. You can also take a look and see the usage of various specific technologies over time. Now, […]
In Google Caching and faking jsonP, i showed how to use google cache to avoid multiple calls to the same json data. One of things i discovered was that the key value in google cache […]
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 […]
Continuing the theme of using Google Apps Script Content service and scriptDB for lots of things, today’s post cover a few tricky topics. How to convert an image to a blob so it can be […]
Lately I’ve been posting about the Google Apps Script Content Service and how to use it as a ‘serverless’ Rest API service. In addition, I covered how to use the ScriptDB as a secure repository for […]
In scraping the scraper I showed how to get scraperwiki data into both Excel and Google Apps Script. More interestingly though, I was talking about data for which others had already done the legwork to create […]
Google currency API Lately for this spot I’ve been selecting APIs that are out of the ordinary, and which allow me to incrementally improve the capability of the REST library. The wrinkle today is that […]
On this blog, and on the ramblings site there is a growing number of code samples, but of course as things evolve, they get out of date. I started to write something to embed Gist samples in […]
You have probably read that jSon works well with javaScript because ‘it is javaScript syntax’. Consider this javaScript (actually Google Apps Script). var items = {itema:1,itemb:’b’}; Logger.log (items.itema + “,” + items.itemb); We […]
In a previous post I covered how to use the Google Apps Script ScriptDB as a database from which to serve jSON responses – without the need for a server. Of course you may need to […]
I’ve written about d3.js force diagrams, mapping tweet sentiments, , d3.js trees, Sankey diagrams and various other stuff on the ramblings site all of which show how to create d3.js diagrams directly from simple Excel data. This is settling […]
At last we can now create a library of useful stuff in GAS and access it (or share it) from other sheets. Those of you that have seen this blog or follow the parent web […]
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 […]
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 […]
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 […]
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 […]
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