desktop liberation
Follow
Find
15.4K views | +5 today
desktop liberation
Your new post is loading...
Your new post is loading...
Scooped by brucemcpherson
Scoop.it!

Excel Liberation.: Transforming data for d3 tree from Excel

Excel Liberation.: Transforming data for d3 tree from Excel | desktop liberation | Scoop.it

Creating d3.js tree diagrams directly from Excel, shows how to take a simple list of Excel data and create d3.js diagram. I had a question on the Excel Liberation forum the other day about how to automate the transformation of the input data.  This very concise piece of code is all that is needed to transform data for d3.js input.

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

Excel Liberation.: Using Google Apps Script as a wrapper for multiple APIs

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 oAuth credentials, and using the Content Service as a proxy for other APIs that need authentication.

Another thing I've been playing around with in recent posts is mashing up multiple APIs, for example doing some geocoding then passing the result of that off to another API.

So where are am I going with all these threads? Content Service, acting as a quasi server, allows us to either combine, or preprocess API calls. That means that we can create a "completely new REST API" by combining multiple APIs, doing the necessary preprocessing and translations in Google Apps Script.

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

Excel Liberation.: Urbarama API mashup - The daily rest library API

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 closeby projects given a geographic reference point. For more detailed information and for downloads see the excel liberation site.

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

Excel Liberation.: Interesting scrapes from scraperwiki

Excel Liberation.: Interesting scrapes from scraperwiki | desktop liberation | Scoop.it

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 tables of data they had scraped from unstructured web pages and made publicly available. I thought maybe I should publish an occassional directory of interesting scrapes I came across.

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

Client oAuth calls using a Google Apps scriptDB lockbox

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 do it from VBA), I came across lots of different oAuth implementations, mainly using server based code, but all of which had some challenges in either keeping the secret key information secure, yet available, or in actually implementing a native oAuth solution because of cross domain challenges, lack of libraries and so on. I wondered if there might be an easy way to solve this using google Apps Script as a proxy for accessing REST queries that required oAuth.

Here's how to, without needing a server,

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

Excel Liberation.: using Google Apps ScriptDB as a lockBox

Excel Liberation.: using Google Apps ScriptDB as a lockBox | desktop 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 | desktop 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 | desktop 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 | desktop 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 | desktop 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 | desktop 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 | desktop 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 | desktop 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!

Let Google Apps Script do the work - Excel Liberation

For this site, I usually create a Google Apps Script version and a VBA version of the projects and libraries discussed. This is not so hard, since there are utility libraries in both languages, so it's just a case of tweaking the language syntax. I also played around with calling javaScript from VBA, and passing arguments in VBA using javascript like objects.

But what if there were more than two languages to support? Could we do most of the work once and pass queries and results back and forwards? In this post I showed how to hand off the business of credential storage to ScriptDB and of doing rest queries needing oAuth using google Apps script as a proxy.

Moving on from that, I looked at how we could focus API mashups to one language, and create an entirely new pseudo api based on that mashup, with the results being delivered as a jSon response.

But what else? In Urbarama mashup I showed how to combine 2 APIs to create a simpler UI - but I still had to implement it in both VBA and Google Apps Script. As a new pattern for the future, for these type of applications, why not write only the Google Apps Script version, and return the result (whether to VBA or an entirely different language) as a jSon reponse.

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

Excel Liberation.: Going off topic - international roaming charges. Plus ça change (plus c'est la même chose)

Today I'm going completely off topic. I was fascinated by a G+ plus by John Walkenbach where he referred to this gizmodo post about the mail call devices from 40 years ago. This was about how people used to mail recorded message tapes to each other in order to avoid expensive long distance charges.

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

Excel Liberation.: First month over month drop in site visits.. this is hard work

Excel Liberation.: First month over month drop in site visits.. this is hard work | desktop liberation | Scoop.it

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 time today. Looking at the month of September, I see the first ever drop in visitors to the Excel Liberation site

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

Google planning on an integrated OnePassword type of service?

Google planning on an integrated OnePassword type of service? | desktop liberation | Scoop.it
Google employee and XML pioneer Tim Bray has been known for delivering awesome tweets from his personal Twitter account regarding issues happening at the company. Today, Bray took to his popular &#...
more...
No comment yet.
Scooped by brucemcpherson
Scoop.it!

How to Enable / Disable 'Show Pictures' in Internet Explorer using VBA

 

Internet Explorer provides users with the option to disable the loading of pictures from a webpage. Disabling this feature allows your browser to move much faster because images, which can take a while to display due to their significant file size, are not loaded or shown. If you plan to extract data only, you may be interested just in the text information of a webpage. VBA does not have any way to set these options of an internet explorer object directly. This is because they are actually controlled from a registry entry. Here is how to toggle this setting, by tweaking the registry contents.

 

This condensed article is from an original by a guest contributor, Petros Chatzipantazis of spreadsheet1.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 | desktop 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 | desktop 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 | desktop 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 | desktop 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 | desktop 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.