It's been two weeks since we first launched Build on Standard Library and what a rush! We're excited to show you more of what Build can do. This week, we're officially announcing our Google Sheets query API that turns any Google Sheet into a queryable database. Simply link your Google Sheet when using build.stdlib.com or create a General Use Identity token and go nuts on the documentation page.
Before we get into the Google Sheets connector API, we just want to give a team shoutout to our favourite serverless engineer and pundit, Ben Kehoe, who threw his hat in for support.
This is the right thing.— Ben Kehoe (@ben11kehoe) June 26, 2019
Linking Google Sheets to a Standard Library Identity
As a connector API, we've custom-built the googlesheets.query API atop our Identity product to automatically authenticate you with a default spreadsheet when you use it. It relies on KeyQL to provide a simple query language atop your spreadsheet. To use it, first visit the tokens page on Build on Standard Library at build.stdlib.com/tokens. You should a page like this:
Note: All new users will automatically have a General Use Identity Token assigned. If so, you can skip the Token Creation step.
If you don't already have a General Use Identity Token (
Legacy Tokens don't count), you'll want to click Create General Use Identity (Development). You'll get a prompt that looks like this:
Enter a name, such as
My Dev Token, and click Create New Authentication Token. It'll show up on the General Use Identity Tokens list.
Please note that free accounts only support one (1) General Use Identity Token, but it's ultra affordable after that — just $15 / mo for two more. You can contact us if you need more than that, we're happy to help!
Click on the name of either an existing token or the new token you just created to proceed, you'll be brought to a token management screen:
Find the Link Another Resource selector on the middle left. Select Google Sheets and the the plus [+] button.
From here, you'll have the option to Link a New Resource:
Click the Link a New Resource button and the Google OAuth flow will be triggered. Select the appropriate account...
And you'll see a progress indicator:
Once complete, you'll see a list of Google Sheets available to you. Choose one. Please note: The Google Sheets query API requires you to provide a range of values to turn into a Database. The first row will always be used as column headers.
Select the appropriate Google Sheet and click Finish to complete the process...
Voila! Your General Use Identity Token (perhaps named
My Dev Token) is now linked to this sheet. You can change which sheet is linked any time from this page.
Using the Google Sheets Query API
Now that we've linked our Google Sheet, we can visit the Google Sheets Query API reference page. From here, scroll down to the select method:
On the selector on the bottom right, where it says Unauthenticated, select the token you just created (
My Dev Token):
Fill in the range parameter with the range you'd like to turn into a Database, like
A1:Z1000 (database will be truncated at first empty row). Now hit Run Function...
You should see a list of all results. In this example, we used a Spreadsheet from a Web Crawling / Scraping tutorial we've written.
You can now use this code in any codebase, or build with the Google Sheets API using Build on Standard Library!
We hope you enjoy this Connector API. We're excited to get a lot more content out this summer, but the developers we've been working with have found it immensely valuable to work with Google Sheets as if it's a database. We agree, and are excited to see what you build with it!