{"Spreadsheet APIs"}

OpenAPI Spec Google Spreadsheet to Github Jekyll Hosted YAML

I have been playing around with different ways of using Google Spreadsheet to drive YAML and JSON data to Jekyll data projects hosted as Github repositories. It is an approach I started playing around with in Washington DC, while I was helping data stewards publish government services as JSON-LD. It is something I've been playing around with lately using to drive D3.js visualizations and even a comic book.

There are couple of things going on here. First, you are managing machine-readable data using Google Spreadsheets, and publishing this data as two separate machine readable formats: JSON and YAML. When these formats are combined with the data capabilities of a Jekyll website hosted on Github Pages, it opens up some pretty interesting possibilities for using data to fuel some pretty fun things. Plus...no backend needed.

To push this approach forward I wanted to apply to managing OpenAPI Specs that can be used across the API life cycle. I pulled together a spreadsheet template for managing the details I need for an OpenAPI Spec. Then I created a Github repository, forked my previous spreadsheet to YAML project, and modified it to pull data from a couple of worksheets in the Google Doc and publish as both JSON and YAML OpenAPI Specs. 

My OpenAPI Spec Google Sheet to YAML for use in a Jekyll project hosted on Github is just a prototype. The results don't always validate, and I'm playing with different ways to represent and manage the data in the Google Sheet. It is a fun start though! I am going to keep working on it, and probably start a similar project for managing an APIs.json index using Google Sheets. When done right it might provide another way that non-developers can participate in the API design process, and apply OpenAPI Specs to other stops along the API life cycle like with API documentation, SDK generation, or testing and monitoring.

See The Full Blog Post

That Thing You All Are Doing With Slack Integrations, @Blockspring Is Doing It With Spreadsheets

One common thing you hear from the growing number of integrations and bots that are leveraging the Slack API, is all about injecting some specific action into the platform and tooling, we are all already using. Startups like Current, who are providing payments within your Slack timeline, use the slogan, "transact where you interact". I began to explore this concept, in what I call API injection, and is something I'm sure I'll be talking about over and over in the future, with the growth in bot, voice, and other API enabled trends I am following.

The concept is simple. You inject a valuable API driven resource, such as payments, knowledge base, images, video, or other, into an existing stream, within an existing platform or tool you are already putting to use. It is not a new concept, it is just seeing popularity when it comes to Slack, but really has been happening for a while wit Twitter Cards, and chatbots who have been around for some time. 

I'm seeing another incarnation of this coming from my friends over at Blockspring, who I've showcased for some time, for bringing valuable API resources to spreadsheet users. Blockspring just released Google Sheets Templates, which enables your spreadsheets to "do useful things", and "get data, use powerful services, and do things you didn't think were possible in a spreadsheet." Instead of Slack being the "existing tool", it is Google Spreadsheet, but the approach is the same -- bring useful API driven resources to users, where they are already existing and working.

As I continue to watch the latest bot evolution, a stark contrast has emerged between the types of bots we've seen between Twitter and Slackbots--a contrast I feel is being defined by the tone and business viability of each platform. Twitterbots are much more whimsical and about stories, poetry, and other information, where Slack is very much productivity, and business focused, making it a prime target for the next wave of startups, and VCs.

I think there is a big opportunity to deliver valuable API resources into the timeline of Slack users. I think there is an even bigger opportunity to deliver valuable API resources into the worksheet of the average business spreadsheet user. The problem is that these opportunities also means there will be a significant amount of crap, noise, and pollution injected into these channels. I'm just hoping there will be a handful of providers who can do this right, and actually bring value to the average Slack, as well as the every day spreadsheet worker -- you know who you are.

See The Full Blog Post

When Are We Going To Get A Save As JSON In Our Spreadsheets?

My last rant of the evening, I promise. Then I will shut up and move back to actual work instead of telling stories. I'm working on my Adopta.Agency project, processing a pretty robust spreadsheet of Department of Veterans Affairs expenditures by state. As I'm working to convert yet another spreadsheet to CSV, and then to JSON, and publish to Github, I can't help but think, "where is the save as JSON" in Microsoft or Google Spreadsheets?

I can easily write scripts to help me do this, but I'm trying to keep the process as close to what the average person, who will be adopting a government agency data set, will experience. I could build a tool that they could also use, but I really want to keep the tools required for the work as minimal as possible. 

It would just be easier if Microsoft and Google would get with the program, and give us a built in feature for saving our spreadsheets as JSON.

See The Full Blog Post

Project Idea: Codenvy-Like Containerized Spreadsheets

I wrote a story about a company I’m advising for last week called Codenvy, who is delivering modular, scalable, cloud development environments using their web IDE and Docker. I'm currently working my way through the spreadsheet to API, and API to spreadsheet solutions I track on, and it is making me think that someone should deliver a Codenvy-like containerized spreadsheet environment.

With this type of environment you could forever end the emailing of spreadsheets, allowing people to craft spreadsheets, complete with all the data and visualization goodness they desire, and clone, fork, share, and collaborate around these containerized spreadsheets. You could have a library of master planned spreadsheets that your company depends on, and manage user permissions, analytics, and even scalability, performance, and distribution of vital spreadsheets.

Using this platform you could easily spawn spreadsheet driven APis using services like API Spark, and you could come full circle and have spreadsheets that obtain their data directly from valuable API driven resources, to really blow your mind. Personally I'm not a big spreadsheet lover, but I do recognize that it is where a majority of the worlds data resides, and the user interface of choice for many business folk--making it a good candidate for the next generation of containerized API data sources, and clients.

Just another one of my random ideas that accumulate in my Evernote, that I'll never have time to build myself, and hoping you will tackle for me. As I review all of the existing spreadsheet APi solutions available currently, I’m sure I'll have more API centric, spreadsheet ideas bubble over t o share with you.

See The Full Blog Post

Using Excel As An API Datasource And An API Client For The Masses

I’ve been tracking on the usage of spreadsheets in conjunction with APIs for several years now. Spreadsheets are everywhere, they are the number one data management tool in the world, and whether API developers like or not, spreadsheets will continue to collide with the API space, as both API providers, and consumers try to get things done using APIs.

APIs are all about getting access to the resources you need, and spreadsheets are being used by both API providers and consumers to accomplish these goals. It makes complete sense to me that business users would be looking for solutions via spreadsheets, as they are one potential doorway to hacking for the average person—writing macros, calculations, and other dynamic features people execute within the spreadsheet.

I know IT would like to think their central SQL, MySQL, Postgres, Oracle and other database are where the valuable data and content assets are stored at a company, but in reality the most valuable data resources are often stored in spreadsheets across an organization. When it comes time to deploying APIs, this is the first place you should look for your datasources, resulting in Microsoft Excel and Google Spreadsheet to API solutions like we’ve seen from API Spark.

I’m seeing spreadsheets used by companies to deploy APIs in some of the following ways:

  • Microsoft Excel - Turning Microsoft Excel spreadsheets directly into APIs. by taking a spreadsheet, and generating an API is the fastest way to go from closed data resource to an API for anyone to access, even without programming experience.
  • Google Spreadsheet - Mounting public and private Google Spreadsheets is an increasingly popular way to publish smaller datasets as APIs. Since Google Spreadsheets is web-based, it becomes very easy to use the Google Spreadsheet API to access any Spreadsheet in a Google account, then generate a web API interface that can allow for reading or writing to a spreadsheet data source via a public, or privately secured API.

Beyond deploying APIs I’m seeing API providers provide some innovative ways for users to connect spreadsheets to their APIs:

  • Spreadsheet as Client - Electronic parts search API Octopart has been providing a bill of materials (BOM) solution via Microsoft Excel, and now Google Spreadsheets for their customers--providing a distributed parts catalog in a spreadsheet, that is kept up to date via public API.
  • Spreadsheet as Cache - I’ve talked with U.S. Census and other data providers about how they provide Microsoft Excel and Google Spreadsheet caches of API driven data, allowing users to browse, search and establish some sort of subset of data, then save as a spreadsheet cache for offline use.
  • Spreadsheet as Catch-All - Spreadsheets aren’t always being used just about data, you can see Twilio storing SMS, NPR using as crowdsourced engine, making spreadsheets into a nice bucket for catching just about anything an API can input or output.

Moving out of the realm of what API providers can do for their API consumers with spreadsheets, and into the world of what API consumers can do for themselves, you start to see endless opportunities for API integration with spreadsheets using reciprocity providers:

  • Zapier - There are five pages of recipes on the popular API reciprocity provider Zapier that allow you to work with Google Docs, and 57 pages that are dealing directly with Google Drive, providing a wealth of tools that non-developers (or developers) can use when connecting common APIs up to Google Spreadsheets.

I’ve seen enough movement in the area of Microsoft Excel and Google Spreadsheets being used with APIs to warrant closer monitoring. To support this I've started publishing most of my research to an API Evangelist spreadsheet research site, which will allow me to better track, curate, tag, and tell stories around spreadsheets and APIs.

As I do with my 60+ API research projects, I will update this site when I have time, publishing anything I've read, written, and companies I think are doing interesting things spreadsheets and APIs. I'm pretty convinced that spreadsheets will be another one of those bridge tools we use to connect where we are going with APIs, with the reality of where the everyday person is, just trying to get their job done.

Disclosure: API Spark is an API Evangelist partner.

See The Full Blog Post

Route SMS Messages To Google Spreadsheets Via Twilio API With TwilioSheet

If you follow Twilio blog or Twitter account you can always find a good API story from the API leader. It also makes me happy to see trends I’m seeing from other provider re-enforced by the API heavyweight. This time is providing spreadsheet integration with common API resources, like Twilio SMS.

Twilio has a pretty slick tool they call TwilioSheet that allows you to receive SMS messages in a Google Spreadsheet, and created a pretty nice walkthrough of the entire setup. Providing this type of functionality helps, as Twilio says, "make it easy for developers and non-developers alike to receive SMS messages in a Google Spreadsheet”—emphasis on the non-developers.

Whether we like it or not, the spreadsheet is the #1 database solution in the world, and provide a huge opportunity when it comes to bridging the world of APIs with the wider business landscape. This is something that API reciprocity providers like Zapier have bridging for a while now, and something that API providers like Intuit are looking to bank into their API platforms.

When you see Twilio doing something like providing providing spreadsheet integration for their API platform, you have to stop and consider whether or not it is something that might work for your own API platform. Spreadsheet integration by default with API your driven resources is a great way to expand the reach of any API, bringing these valuable resources within reach of the actual, everyday problem owners.

See The Full Blog Post

Exhaust From Crunching Open Data And Trying To Apply Page Rank To Spreadsheets

I stumbled across a very interesting post on pagerank for spreadsheets. The post is a summary of a talk, but provided an interesting look at trying to understand open data at scale. Something I've tried doing several times, including my Adopt A Federal Government Dataset work. Which reminds me of how horribly out of data it all is.

There is a shitload of data stored in Microsoft Excel, Google Spreadsheet and CSV files, and trying to understand where this data is, and what is contained in these little data stores is really hard. This post doesn’t provide the answers, but gives a very interesting look into what goes into trying to understand open data at scale.

The author acknowledges something I find fascinating, that “search for spreadsheet is hard”—damn straight. He plays with different ways for quantifying the data based upon number columns, rows, content, data size and even file formats.

This type of storytelling from the trenches is very important. Every time I work to download, crunch and make sense of, or quantify open data, I try to tell the story in real-time. This way much of the mental exhaust from the process is public, potentially saving someone else some time, or helping them see it through a different lens.

Imagine if someone made the Google, but just for public spreadsheets. Wish I had a clone!

See The Full Blog Post

Secure API Deployment From MySQL, JSON and Google Spreadsheets With 3Scale

I'm doing a lot more API deployments from dead simple data sources since I started working in the federal government. As part of these efforts I'm working to put together a simple toolkit that newbies to the API world can use to rapidly deploy APIs as well.

A couple of weeks ago I worked through the simple, open API implementations, and this week I want to show how to secure access to the API by requiring an AppID and AppKey which will allow you to track on who has access to the API.

I'm using 3Scale API Management infrastructure to secure the demos. 3Scale has a free base offering that allows anyone to get up and running requiring API keys, analytics and other essentials with very little investment.

Currently I have four separate deployment blueprints done:

All of these samples are in PHP and uses the Slim PHP REST framework. They are meant to be working examples that you can use to seed your own API deployment.

You can find the entire working repository, including Slim framework at Github.

See The Full Blog Post

API Deployment From MySQL, JSON, Github and Google Spreadsheets

I'm doing a lot more API deployments from dead simple data sources since I started working in the federal government. As part of these efforts I'm working to put together a simple toolkit that newbies to the API world can use to rapidly deploy APIs as well.

Currently I have four separate deployment blueprints done:

All of these samples are in PHP and uses the Slim PHP REST framework. They are meant to be working examples that you can use to seed your own API deployment.

I'm also including these in my government API workshop at #APIStrat this week, hoping to get other people equipped with the necessary skills and tools they need to get APIs in the wild.

You can find the entire working repository, including Slim framework at Github.

See The Full Blog Post

Mobile Editing of Google Spreadsheets in 45 Languages

Google has enabled many of the same mobile editing capabilities in Google Docs, for Google Spreadsheets.

You can now edit Google Spreadsheets in 45 languages on Android, IPhone and IPad devices.

Google has aslo added eight separate print sizes in Google spreadsheets, including tabloid, statement, executive, folio and A3, A5, B4 and B5.

Some new features that continue adding to Google Docs viability as a powerful publishing platform.

See The Full Blog Post

Visualize Big Data with BigQuery and Google Spreadsheets

Being the big data geek I am, I'm pretty excited that Google has integrated BigQuery with Google Apps Script and Google Spreadsheets.

This means you can take power of BigQuery for storing and querying of very large data sets and combine them using Google Apps Scripts with any of your Google Spreadsheets.

With a simple query Google Apps Script you can embed and process a query that pulls counts, fields and other information you use in spreadsheet columns and data visualizations. You can upload VERY large sets of data like the M-Lab dataset in BigQuery which contains 240B rows!

I think this is an excellent example of the power of cloud computing. You can take a web application like Google Spreadsheet and truly harness the computer power of the cloud and make sense of large data sets.

There is more information on their recent blog post BigQuery, meet Google Spreadsheets or visit the BigQuery website.

See The Full Blog Post

Google Cloud Platform - Spreadsheets as Data Stores

I attended many great sessions while at Google I/O. I really started to see the where they are going with using Google Spreadsheets as data stores.

I have used as data stores for quite a while, but they are really pushing for them to store enterprise data, application data, web site data, and much more.

Way back in the day when i was doing non-profit work I went and conducted a needs assement at a large educational non-profit. To build a specification for developming a new administrative system.

Went I first went in I was told there there is a central database system by the management. After conducting my multi day technoloy needs assessment I found out that the real staff utilized many many spreadsheets as data stores to get their work done. They rarely used the central system.

I find this to be common across many organizations.

This is why I think Google Apps has huge potential for the spreadsheets to be majorly adopted as data stores for the masses in non-profits, business, and enterprises.

I'll talk more formerly about this later.

See The Full Blog Post

Google Spreadsheets as a Data Store

I just finished BETA testing the DocSyncer tool which will keep mycomputer synced with my Google Docs account.

At first I had settings turned on to upload .html pages, which was a huge mistake....instantly it started publishing thousands of files of my local workstation, be careful.

Once I did get my 800+ word and excel documents published synced up without any effort I started looking through them and searching on topics. It was very interesting to find some items I don't even remember I have.

The I pulled out a simple JavaScript for pulling a JSON feed from a Google Spreadsheet and connected it to a list of businesses I had for the Portland area. Instantly I had published these business listings on the web using my Google Spreadsheet as a data store.

Very cool stuff, think it will be a while before everyone realizes the potential here.

See The Full Blog Post