It’s been over 5 years since my book ‘Going Gas‘ was published, and Apps Script evolution means that a number of sections of it is now pretty out of date. I’ll use this format then […]
I’ve been doing an annual update called ‘a year in Apps Script’ for a number of years now, but I didn’t get round to the 2021 post. This year I thought I’d do something different. […]
Here’s an alternative snippet for how to iterate through a collection of files. Lets say we want to look at every workbook in the current directory, except the currently open one, and show each sheet […]
This tutorial will get you started with Database abstraction with google apps script and show you how to write a small app that uses a sheet as a database. https://docs.google.com/presentation/d/10rEyXjHxo42YV3wRW46o-CgrAZxFqUxyftHPkjLtbt8/embed?authuser=1&hl=en&size=l Open do something in 5 with gas-1-use […]
I usually do an annual post on what’s been going on in the world of Apps Script, like this one but since we’re coming up to the platform’s 10th anniversary in August this year (here’s a […]
For some years now I’ve been writing this annual summary around Christmas time, on how my year in Apps Script has been. I’m very late this year, but it’s been a pretty busy one both […]
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’ve considerably cooled on the whole blogging thing over the years, preferring to post either to communities, github, to my tutorial site or my YouTube channel, or to work on more substantial resources, such as books or […]
I’ve been using a Chromebook for a few years now, and find myself going for long stretches without needing to turn on a Windows machine or an Apple Mac. At first, I was a little […]
2016 The real world of 2016 has been full of madness, badness and sadness. It’s impossible to reflect on its events without thinking of the divisiveness, nastiness and mean-spiritedness that we’ve all seen, and I […]
What is App Maker App Maker provides a ‘low code’ way to create professional looking G Suite custom apps without having to try too hard. You can drag and drop widgets on a form canvas, […]
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 […]
So this just happened. BigQuery integration with Sheets!. This is great; now you can use a spreadsheet as a federated datasource for BigQuery, and therefore query (and join with other massive datasets) your spreadsheet data […]
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, […]
I’ve more or less stopped using blogger this year – looking back I’ve only made 6 posts since my end of 2014 post summarizing that year. Instead, I’ve been continuing to focus on desktop liberation […]
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 […]
If you develop shared code for VBA, you’ve probably got some massive workbooks that contain multiple projects that you wish you could separate, but it’s too complicated because they are sharing classes and functions. The […]
Since we have a lot of operations going through the database abstraction libraries nowadays (over 3 million since I released this in summer last year), I thought I’d give a few hints. I’ll […]
I’ve just completed my first Polymer app and have mixed feeling about the experience. Regulars to this blog and the associated site know that I’m a great fan of Google stuff and like to try out […]
I posted the other day in A year in apps script and my bucket list my take on what happened in the Google Apps Script world in 2014, and my hopes for 2015. As many of you […]
2014 has been an eventful year for Google Apps Script with some great new capabilities arriving (add-ons), as well some useful ones (ScriptDB) being deprecated. In this post I’ll cover my highlights and lowlights of […]
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 […]
I couldn’t let a Eurovision weekend pass without doing some kind of visualization. I’ve always been fascinated by how the relationship between countries is manifested through the way that they vote for each other, regardless […]
A few months ago, I posted something about instrumenting desktop apps with Google Universal Analytics to track usage, just like you can with web based analytics. In the meantime, I’ve added analytics to the 3 […]
I learned last week that I had been bestowed the honor of membership of the Google Developer Experts (GDE) program. It’s a great feeling to have your work recognised in this way, even though I […]
Last year, I started to experiment with Google Universal Analytics to measure usage of Excel Apps that people download from Excel Liberation in order to get insight into what’s being used, how it’s being used and […]
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 […]
If you are reading this there is a 90% probability that it’s close to the beginning of 2014. I know this because most blog page views happen with the first couple of weeks of posting, […]
I figured it would be pretty nice to be able use one of the cloud based noSQL databases directly from VBA, so that I could share data easily between various platforms. On Excel Liberation , I […]
If you take data from outside Excel – especially using REST APIS, there’s a good chance that you’ll have to deal with Unix Times. If you are familiar with the Excel Liberation site, you’ll know […]
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, […]
Last week, I posted how to instrument Excel workbooks so that usage of them could be tracked using Google Analytics. Now we have a week’s worth of data, let’s see what happened. Instrumentation Recap First […]
In Rest-Excel library, I have examples of a large number of REST APIs which get data from a wide range of data sources. I have a similar library for Google Apps Script. I have to […]
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 […]
In REST access to Google Apps Script list management functions we looked at accessing the Google Apps ScriptDB and some custom functions using the rest to Excel library In that post, I mentioned that it would even be […]
In Google Apps Script lists and validation I showed how to use custom functions from Google Spreadsheets to perform common spreadsheet tasks like lookup, index, filter, sort, match and apply dataValidation using cloud based lists. Of […]
When the scriptable data validation feature in Google Apps Script came out recently, I started thinking about how we don’t really use the cloud based nature of Google Docs to its potential in a number […]
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 […]
It turns out there is a kind of API – suggestqueries.google.com – that you can use to programatically get what Google autocomplete shows when you use search. You know the kind of thing… It’s well known […]
I’ve been playing around with creating a promise based framework for VBA for a while now, mainly just to see if it was possible. Here’s a practical implementation that gets the data from a large […]
A long time back now, I showed how to publish and prettify source code and scriptdb contents using Google Apps Script. This allows you to embed code or data samples in blog posts and so […]
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 […]
Being able to create Sankey Diagrams in D3 directly from Excel continues to be one of the most popular topics on the Excel Liberation site even though I first posted this a long time ago now. […]
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 […]
In Flight Data from Fusion I introduced integration between Google Fusion and D3.js. This next version fixes up a few issues to do with Google Fusion API quotas and limits (see exponential backoff and jquery promises), and […]
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. […]
With Tableau, you can do loads of visualizations very simply and publish them on “tableau public”. I never tried Tableau before, so I downloaded the tableau public software to play around with. The other day, […]
When we explore a web site, the usual view is that of a tree structure of pages. I wondered if it could be done instead using topics. Which topics are mentioned on a given page? […]
Today’s post is a quick function for creating biased randoms from a list of items and weights. You might need this to create some test data, where the outcomes are not completely random. Example […]
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 […]
Today’s post pulls together a number of concepts I’ve been playing around with lately. As usual my source data and post theme is based on data associated with color schemes – pantone, paint colors etc […]
Yesterday I showed how to do some data wrangling in VBA and Google Apps Script that Tony Hirst on his oUseful blog had done using scraperwiki, Open refine plus various APIs. Today he posted a quick analysis using […]
I just saw a great post by Tony Hirst on his oUseful blog where he nicely laid out how to use Open refine plus various APIs to research which candidates were standing for election in […]
Color scheme UI I posted about a color scheme explorer app the other day. Given a specific color, it will find – in a given color scheme such as dulux paint colors, the nearest 5 […]
Lately I’ve been doing a lot of posts to do with messing around with color. I’m using a selection of technologies such as scriptDB, Parse.com, jQuery promises, Google Apps Script, Google Spreadsheets, Google Visualization API and […]
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 […]
In documenting VBA procedures automatically I showed how to create web pages that documented your VBA projects. I’ve updated that now to also include a popup showing the detail of function arguments too. This page was automatically […]
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 playing around with color in VBA I introduced a bunch of functions to manipulate various color spaces from VBA. Digging into the whole topic a little more, I wondered if it would be possible to […]
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 […]
In a previous post I covered a way of automatically including and inserting VBA code in Excel from gists. You would normally use this if you wanted to include a bunch of functions and classes […]
Here’s a little bit more on the subject of VBA string manipulation. I showed in a post the other day how things take longer and longer when concatenating large strings in VBA. One reason I […]
We had a question on our forum the other day about whether it would be possible to inherit the cell colors from the color of the originating cells in Excel in the d3.js Sankey diagram […]
I find myself doing a fair bit of VBA at the moment, It was a little adjustment coming back, but I suddenly noticed that string concatenations were taking a long time. For example, this would […]
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 […]
Figuring out how to serialize native VBA classes to jSon is something I’ve been working on in the background for some time. If you’ve been following this blog or the related Excel Liberation Site, you […]
A while ago, I posted something about interesting scraperwiki data, that’s to say public web scrapings that people had created at some point. In getting scraperwiki data into excel, I showed how you could get a […]
I saw a great blog post on Reshaping Horse Import/Export Data to Fit a Sankey Diagram from Tony Hirst a few minutes ago. In it he shows how to mashup various bits and pieces using […]
You may have used the doughnut chart in Excel in order to produce a chart like this This is fine, but the problem is that the position of any category changes depending on the data. […]
I guess it’s time to look at what’s been going on with the website and blog for 2012, after Martin Hawksey got me wondering after publishing his 2012 stats. Excel Liberation Blog Aside from a […]
In Color Ramps for Google Apps Script I covered how to created heatmaps and other color ramps. Picking a good contrast font color One of the problems with a heatmap is that your font color may […]
I’m finally awarding Mozilla Open Badges through a complete badge environment that handles questions, marking, badge assertion, persona authentication and Mozilla backpack awards. I introduced this in a previous post, but now that I’ve added the […]
I’ve been looking at the Mozilla Open Badges initiative lately, as a way to improve engagement with a particular site or Blog. My idea is that you could assign a set of earnable badges to a […]
Real Angry Birds In the toy store yesterday, I came across something odd. Physical games based on virtual ones. We all know how to play Angry Birds on our smartphones, Ipads etc – but here […]
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 […]
Enterprise Architects often like to refer to Winchester Mystery House as an example of what you get if you build something with no plan, blueprint or end game in sight. This is a house with windows opening […]
In plotting functions in Excel I showed how to create plots from equations without needing to create a data table. Using the same template, here is the Excel version of the well known Batman Plot. Using […]
Roberto Mensa share this post today on G+, reminding me about the Google instant graph capability, and of course I wondered if the same thing could be achieved in Excel. Excel doesn’t really know how to […]
I’ve been messing around with the google Drive API lately, and haven’t really found out how to use it to host images for web sites satisfactorily. That was one of the things that prompted this […]
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 […]
Creating d3.js tree diagrams directly from Excel, shows how to take a simple list of Excel data and create d3.js diagram that looks like this, I had a question on the Excel Liberation forum the […]
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 […]
Today I’m going completely off topic. I was fascinated by a G+ plus by John Walkenbach where he referred to this gizmodo post. This was about how people used to mail recorded message tapes to each […]
Today’s entry is pretty cool. I came across the urbarama site, which describes itself as “a collaborative world atlas of architecture and civil infrastructure”. What’s more it has an API, which will return details on […]
All of you who run a web site or a blog will probably be able to relate to this. The first month you see a drop in visits. It happened to me for the first […]
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 […]
oAuth arghhh.. When I was looking around for a way to easily implement oAuth, and be able to use it from multiple scripts, or even languages (I was originally trying to figure out how to […]
In various posts and articles I’ve been exploring uses of the Google Apps scriptDB datastore. One of the things I’ve been mulling over is how to simplify the whole oAuth experience when dealing with REST queries that need […]
In this post about using the scraperwiki API I covered how to get a list of publicly available scraperWiki data straight into Excel and Google Apps Script. Now you can get the associated data too with just […]
ScraperWiki API I’ve been taking a look at scraperWiki lately. In case you haven’t come across it, it’s a framework to allow you to scrape structured data from web sites using various data manipulation tools and code. […]
In messing around with scriptDB, I covered some usages of scriptDB when it first came out, along with a method of siloing data to keep it organized. Here’s a usage that might be useful for […]
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 […]
Geo code from ip and host names Today’s API is a straightforward query per row, meaning that data is retrieved from the API using the contents of a column as the query. In this case […]
Get rxNorm drug data into Excel and Google Apps Script Aside from being a little specialized, today’s API is another quite unusual one. Normally apis allow you select the format of the output through a […]
Unlike Google Apps Script, which has a fine cloud based library capability, sharing and keeping code current for non-professional developers who use VBA is challenging. The use of add-ins for finished products with specific capabilities […]
Random Neil deGrasse Tyson quotes There’s probably no practical use to today’s API of the day, except that I am always amused by what he has to say. The other thing I wanted to look […]
Geocode and get administrative details and geoHash link from a UK postcode directly into an Excel or Google Spreadsheet Following on from the Yahoo GeoCode entry here’s another geoCoder – this time using UK post […]
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 […]
In Starter implementation of R-melt for Excel I introduced a one liner in Excel/VBA to transform this into this You could consider this to be a kind of un-pivot – exploding tabulated data back into transactional […]
I came across ifttt.com (if THIS then THAT) the other day. Essentially it’s a simple way creating a trigger based on some event such as tweet mentioning you, or a facebook upload (THIS) and so […]
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 […]
Shorten URL API directly into an Excel or Google Spreadsheet Today’s API is from “ttb.li” which is a url shortening service. This is a query by row API, populating columns in a spreadsheet row using […]
Geocode with Yahoo API directly into an Excel or Google Spreadsheet Strictly speaking today’s API doesn’t really qualify as ‘API of the day’, since I’ve written about this before comparing a few geoCoding APIs. I’ve […]
In passing arguments in VBA using jSON I covered how you could use javaScript Object syntax to pass complex arguments to VBA procedures. Today I’ll cover how to pass complex arguments where there are optional values. […]
Data from CrunchBase API directly into an Excel or Google Spreadsheet Today’s API is another query type from “crunchbase” which is a free database of corporations, people and investors. Yesterday I showed you how to get […]
Data from CrunchBase API directly into an Excel or Google Spreadsheet Today’s API is from “crunchbase” which is a free database of corporations, people and investors. There are a number of types of query available in […]
Today’s API is “FAA airport status” – a dataSet query API, populating rows in a spreadsheet using the Airport IATA code as a key Here’s the Excel code (included in cDataSet.xlsm downloadable from the ramblings site) Public […]
In various posts such as this one, I’ve covered new entries to the REST library but it’s been kind of haphazard. Since I’m adding these regularly I figured I’d make a quick post like this for […]
Going back to the theme of ‘doing complicated things in one line of code’, here’s a mash up of cDataSet, Color Ramps, Mapping and various other things. Lets say we want to create a thematic […]
I haven’t posted anything on new entries to the rest-excel-gas library for a while. As I mentioned in this post the library itself is available as a rest query. There have been quite a number of new APIs added […]
I just added a couple of things to cDataSet.xlsm and noticed that I had done this over 100 times. During the same time period, visits to https://ramblings.mcpher.com/ where this is hosted have gone like this The top […]
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 […]
What no server? That’s right – you can serve up data from your Google Apps Script directly. In my post the other day, I showed how to use the Google Apps ScriptDB as a place to store […]
I’m taking a break from d3.js and playing around with the new Apps Script DB. Those of you who read these posts or follow the rambling site may know that I have been building up a library […]
I’ve recently started to use scoop.it to pull together updates from this blog and the related site, along with anything else I find interesting or useful. It really is a nice way to curate things 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 […]
I noticed this morning that Huffington post released a REST API exposing all their polling results. I’ve added that to the Excel-Rest library so you can get the results of all their polls directly into […]
I’ve posted a few examples of d3.js lately, such as mapping tweet sentiments on a force diagram, d3.js tree diagrams, and sankey diagrams. I haven’t had a consistent approach to data structure so I thought […]
Mashing up capabilities I set myself a little challenge of pulling together a few different things I’ve been working on. From a list of queries, get associated tweets, along with their tweet sentiment rating using the Rest-Excel […]
Last week I posted how to create Sankey (flow) diagrams straight out of Excel. Let’s continue on that d3.js theme today with how to create d3.js tree diagrams straight out of Excel First of all […]
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 […]
On the ramblings site you can see how to create Google Maps straight from Excel, including adding circle overlays. By default, the code generated to plot these circles looks like this. if (sz) { […]
First of all thanks to Tony Hirst for blogging about this in the first place to put me on the track, and of course to Mike Bostok who created d3.js I figured it should not be too […]
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