A Simple Walkthrough Of How To Deploy A 4th of July Fireworks API Using A Google Spreadsheet And APISpark
This is a guest post by Guillaume Laforge (@glaforge) of Restlet. He did a very cool API deployment project over the holiday weekend, as was good enough to share the details of his project as a story. I've been trying to make time to do more of these simple how-to API deployment guides like this, so I'm always happy to guest post these when they help users understand that they can deploy APIs--no developer necessary.
For the 4th of July, to celebrate the United States’ Independence Day, Restlet built a fun little website and API to list all the fireworks in your area. You can see all the states where fireworks have been found, and lists of towns in those states where those fireworks were taking place. Furthermore, there’s a Google Sheets form that you can fill in to submit fireworks in your area that were not mentioned.
So it all started with this idea of listing all the fireworks. We also wanted to let people submit ones in their area, as it’s difficult to find each and every possible event in the whole country. That’s why we thought it’d be important if we could use Google Sheets to store the data, as we can let people fill in the gaps in our listings thanks to a Google Form that automatically saves data in your spreadsheet. Thankfully, APISpark provides a Google Sheets wrapper which allows you to expose spreadsheets as a Web API.
Let’s replicate this little fireworks project together!
I’ll start with creating a Google Sheets document and associating it the following form, which let anyone submit new fireworks, with details about the city, state, date and time:
Your website visitors will be able to fill in a form like so:
And your spreadsheet will be updated as new entries are flowing:
The sole modification I’ve done here is to rename the sheet to “fireworks”, but you can also do that later on when configuring the Google Sheets wrapper… speaking of which, it’s time to create it and import that spreadsheet in APISpark!
Once you’ve created an account on APISpark (if you haven’t done so yet), login, then, from the dashboard, create an “Entity Store” and select a Google Sheets wrapper:
You’ll be asked to select a Google account (the one with which you’ve created the spreadsheet), then select the spreadsheet document you’ve created for that purpose.
You’ll see that the import of the spreadsheet succeeded, and that some normalization has been done to the name of the properties of your entities, corresponding to the questions of the form (as the column names of the spreadsheet contained special characters like spaces of question marks).
As those property names will be appearing in the resulting Web API we’re going to create next, I’ve gone the extra step of going through each property to rename them to more succinct names (state, city, date, time):
Now is the time to deploy this first “Cell” so that it can be made available and imported into our future API (just click the “Deploy” button).
If you click on the little cog button in the top right hand corner, near the “Deploy” button, you can export a Web API from this data store directly, so that the API is pre-configured to import it:
I’ve checked the “Read-only API” checkbox (as modifications are done only through the Google Sheets interface), and I’ve selected “No authentication required” for the “Default permissions” as we don’t need authentication here.
Your Web API is ready, and you can see the resource and representation for corresponding to our fireworks sheet:
Go back to the dashboard, and click the “Create file store” button to create a file store, selecting the Github flavor:
In the “Settings” tab, you’ll fill in your credentials to access Github, and add an “Import” pointing at your Github repository containing your web assets:
When configured, you’ll have to deploy your cell to make it usable. Then, heading back to your Web API cell, you’ll be able to add a new import, in the “Settings > Imports” section:
Now the API will contain two imports: one for the Google Sheets wrapper, and this new one for the Github file store.
This page will be served at https://fireworks.apispark.net/v1/html/index.html by APISpark. I’m using JQuery to call my API, and you will see this simple output in your web browser:
Nothing really fancy, but “Q.E.D.” That’s what we had to demonstrate: a Web API delivering data off of a Google Sheets document, but also serving the web assets to offer a UI frontend to interact with that API. No need to host that website elsewhere on some server in the cloud, everything can be done and served through APISpark easily. Having setup Google Sheets for the data makes it easy for non-technical persons to contribute new content and data for your database of fireworks. APISpark then takes care of both exposing a reusable Web API, as well as serving a website to share your nice listing of fireworks to the world!
Disclosure: Restlet is an API Evangelist partner.