Excel Liberation
Follow
Find
12.2K views | +4 today
Excel Liberation
Liberating Excel data from the desktop
Curated by brucemcpherson
Your new post is loading...
Scooped by brucemcpherson
Scoop.it!

Excel Liberation.: using Google Apps ScriptDB as a lockBox

Excel Liberation.: using Google Apps ScriptDB as a lockBox | Excel Liberation | Scoop.it

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 authentication.

When you read up on examples of using oAuth, they are usually server orientated - in PHP, java, python etc. One of the reasons for this is the difficulty in obscuring the various credentials such as your consumer key, consumer secret and so on, when in javaScript (and of course VBA)

For those of us that do not have server based development environments, Google Apps Script comes to the rescue.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Scraperwiki data to Google Docs via API

Scraperwiki data to Google Docs via API | Excel Liberation | Scoop.it

Google Apps Script version now available to get scraperWiki data into Google Docs in one line of code . For excel version, see http://ramblings.mcpher.com/Home/excelquirks/json/rest/scraperwiki 

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

scraperWiki API: The Daily REST library entry for Excel and GAScript

scraperWiki API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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. One of the great things about it is that many of the data sets scraped have been made public by their authors. Another good thing is that scraperWiki has an API, which means that all this data can be accessed by the REST library to load them directly into either Google Apps Script or Excel.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Excel Liberation.: Google Currency API: The Daily REST library entry for Excel and GAScript

Excel Liberation.: Google Currency API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 this API does not return valid jSon.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Free Geo IP API: Excel and Google Apps Script. The Daily REST library entry

Free Geo IP API: Excel and Google Apps Script. The Daily REST library entry | Excel Liberation | Scoop.it

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 the host column provides the query data. The freegeoip API will take a host name and ip address and provide geolocation data for it. Here's how to do it in bulk directly from Excel or Google Apps Script.

 

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Which VBA references you are using - Excel Liberation

Which VBA references you are using - Excel Liberation | Excel Liberation | Scoop.it
VBA references

Quite often you need to add some reference to be able to get some code to compile. This is because the code is referencing some external object that the Excel VBA does not know about. Normally you would add these references through a dialogue, but here is how to find out programatically

more...
Mohsen Qaysi's curator insight, May 29, 2013 11:55 AM

Great place to find source code in ....

Scooped by brucemcpherson
Scoop.it!

How to update modules automatically in VBA - Excel Liberation

How to update modules automatically in VBA - Excel Liberation | Excel Liberation | Scoop.it

Here's a way to share and automatically update public VBA code using Gist and github. Modules and classes required for a capability are described in a manifest. Required VBA code is inserted in your project from github repositories, and is updated on demand to latest versions.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

UK postcode geocoding API: The Daily REST library entry for Excel and GAScript

UK postcode geocoding API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 code as input, and providing various UK specific administrative district information. As usual it is implemented as a rest-library member. This a data query API, populating columns in a row in a spreadsheet based on a variable column in the data. You just name the columns to match any data you want to retrieve and go. Here's an example geocoding various random postcodes

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

How to Embed gist code samples in Blogger

How to Embed gist code samples in Blogger | Excel Liberation | Scoop.it

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 Blogger, but I came across a great post from Moski Doski, who had already solved the problem. Take a look at his post for how to do it. It's just a couple of steps

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

if this then that... for vampires

if this then that... for vampires | Excel Liberation | Scoop.it

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 on which will then cause some action to be taken (send an sms, tweet something etc.) (THAT)

It is ridiculously simple to use. You go through a dialogue selecting the THIS and the THAT from a selection of Icons and it happens.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

url shortening API: The Daily REST library entry for Excel and GAScript

url shortening API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 a value in a given column. Here's an example - you supply a list of long urls. It will give you back shortened versions.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Yahoo Geocoding API: The Daily REST library entry for Excel and GAScript

Yahoo Geocoding API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 been using Google for geocoding , but recently I am seeing a lot more quota refusals now when not using a developer key, so I'd thought I may as well resurrect Yahoo implemented as a rest-library member.

 

This a data query API, populating columns in a row in a spreadsheet based on a variable column in the data. As usual, you just name the columns to match any data you want to retrieve and go. Here's an example geocoding some of the queen's family's homes.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

CrunchBase 'who works where' API: The Daily REST library entry for Excel and GAScript

CrunchBase 'who works where' API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 about people. Today I'm showing how to get a list of people who work for a given company - this a single query API, populating multiple rows in a spreadsheet from a given name. This is executed in a single line of code in either VBA or Google apps script.  The example shows a few lines of the response to a query on "google"

 

For more like this visit ramblings.mcpher.com

more...
No comment yet.
Rescooped by brucemcpherson from Google Apps Script
Scoop.it!

Simplifying Migration from VBA to Google Apps Script - Google Apps Developer Blog By Bruce Mcpherson

Simplifying Migration from VBA to Google Apps Script - Google Apps Developer Blog By Bruce Mcpherson | Excel Liberation | Scoop.it

If you are new to Google Apps Script and the JavaScript programming language, migrating legacy automation code written in Microsoft's Visual Basic for Applications (VBA) can be a daunting task. This blog post describes a Google Apps Script library which mimics the behavior and calling structure of common VBA functions, allowing you to more easily convert your existing applications.


Via Martin Hawksey
more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Getting data out of scraperWiki - Excel Liberation

Getting data out of scraperWiki - Excel Liberation | Excel Liberation | Scoop.it

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 data from their endeavours available. In a previous blog post, I showed how to use the scraperWiki API to pull data about public projects published on scraperWiki. Now let's look at getting data out of the scraperwiki datastore directly into Excel using the scraperwiki API

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Using Google Apps ScriptDB to track activity

Using Google Apps ScriptDB to track activity | Excel Liberation | Scoop.it

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 tracking things - You could for example, implement this to track who is opening what workbook.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Official Google Enterprise Blog: Google BigQuery new features let you do more with your data

Big Data can be a challenge for businesses and developers. There is so much information available today that it can be difficult to gain insights and make business decisions based on that data. Last month, Google BigQuery integrated several partner solutions, making it easier to import data from other cloud and on-premise solutions and visualize your data with rich interactive dashboards. Today, we’re giving you new ways to work with your data by adding two new features to BigQuery.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

How to add references by guid - Excel Liberation

In Which VBA references you are using we looked at how to get details about references active in a project. Let's look at how to add those references to a project using code. This approach is needed in How to update modules automatically in VBA since references are dependencies that can be automatically updated in the same way as code.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

rxNorm Drug API: The Daily REST library entry for Excel and GAScript

rxNorm Drug API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 URI parameter, but the rxNorm pharma database needs you specify it in the GET headers of your request. More about that later. This a single query API, populating multiple rows in a spreadsheet based on a query you provide. You just name the columns to match any data you want to retrieve and go. Here's the results of query on the drug Amoxil.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Excel Liberation. http://ramblings.mcpher.com: Neil deGrasse Tyson quotes API: The Daily REST library entry for Excel and GAScript

Excel Liberation. http://ramblings.mcpher.com: Neil deGrasse Tyson quotes API: The Daily REST library entry for Excel and GAScript | Excel Liberation | Scoop.it

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 at was a slightly more complex usage of the restQuery capability, but at the same time to talk a little more about how this exposed interface could benefit from using jSon arguments rather than the traditional list imposed on us by VBA syntax.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

1 in 5 APIs Say “Bye XML”

1 in 5 APIs Say “Bye XML” | Excel Liberation | Scoop.it

You can see from the article that more and more APIs are not even bothering with XML versions. See ramblings.mcpher.com for comprehensive jSon/VBA integration to meet the need for more jSon everywhere - but in Excel

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Excel Liberation. http://ramblings.mcpher.com: R melt for Google Apps Script and Excel/VBA: reshaping/unpivoting data

Excel Liberation. http://ramblings.mcpher.com: R melt for Google Apps Script and Excel/VBA: reshaping/unpivoting data | Excel Liberation | Scoop.it

You could consider this to be a kind of unpivot - exploding tabulated data back into transactional rows as described by Hadley Wickhams in his implementation of reshape/melt for R. Here is both an Excel/VBA and a Google App script version.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

jSon parsing: single and double quotes and messing around with Google Apps Script and VBA

Dealing with double and single quotes in javaScript and jSon.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Simple implementation of R- melt in VBA and GAS - Excel Liberation

Simple implementation of R- melt  in VBA and GAS - Excel Liberation | Excel Liberation | Scoop.it

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 eye in particular was the ability to turn a tabular data representation in to a transactional one - a kind of unpivot.

 

thanks to Tony Hirst http://blog.ouseful.info/2012/08/08/olympic-medals-visualising-ots-using-an-r-treemap/ for the idea.

more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

Passing complex arguments with default values in VBA - like jQuery $.extend()

Passing complex arguments with default values in VBA - like jQuery $.extend() | Excel Liberation | Scoop.it

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.

more...
No comment yet.