In this tutorial, we'll create a simple Android app and hook it up to a backend API built on Google Sheets without writing a single line of code. This means that our app is able to send and receive data from our own API in the cloud, with the API reading from Google Sheets like a database.

Once you've gone through it, you'll be able to create Dropsource apps that take user-submitted data and do all sorts of crazy or valuable things with it. This is a game changer for entrepreneurship and testing ideas cheaply.

Tools of the trade

Below are the tools you'll be using in this guide. All have generous free trials:

Dropsource is a drag-and-drop, no-code, mobile appe builder that works right in your browser. You can even publish the app directly from Dropsource.

Google Sheets will be repurposed as a database-in-the-cloud to power my Dropsource app because it's easy to edit and free.

A dropsource app can't talk directly to Google Sheets - it needs to be turned into an API first. That's where Sheetsu and Stoplight come in.

Sheetsu is what actually turns a Google Sheet into an API which apps can integrate with. Stoplight is a swiss-army knife for APIs that we'll use to aid integration between Dropsource and Sheetsu.

The high-level low-down

Here's the high-level summary of what I'll show you how to do:

  1. Create a Google Sheet which will be your app's database
  2. Go to Sheetsu.com and turn your Google sheet into an API
  3. Go to Stoplight.io and get a spec doc for your Sheetsu API (needed by Dropsource)
  4. Go to Dropsource.com and visually create your app with no coding
  5. At Dropsource, add your Sheetsu/ Google Sheet API as a data source
  6. Test your app
  7. Tada! You're a no-code full-stack engineer!

For this guide, I'll be building an Android app that takes a number input by a user and then gets a compliment for my wife from a Google Sheet which corresponds to that number. Why do you think I had enough time to write such a detailed post?

Anyway, on to the details...

Create your Google Sheet for use as a database

Create a Google sheet with an "ID" column with unique integers and one other column of information you'd like to display in the app. My sheet looked like this:

I made my Google Sheet this way because I want my app to be able to say to my API, "hey API, give me compliment #3" and then for my API to respond with the "compliment" associated with that ID in the Google Sheet.

Turn your Google Sheet into an API

Once you've created your Google Sheet, visit Sheetsu, sign-up for a free trial, and click the template to "Create JSON API"

Sheetsu asks for the URL to the Google Sheet - paste it in there.

Now click on Query Builder on the bottom right:

You'll see a screen similar to this:

Now click on "search in spreadsheet" and input "ID=3" (or a number which is in a row in your Google sheet) as a query. Then click "Try it out" and you should get a JSON response from Sheetsu similar to this:

Congrats! You just built your first spreadsheet API! Basically, this API responds to requests containing a query for an ID number, and returns matching rows. For my API, it responds to an ID with the corresponding compliment. My app is going to call this API and display the response to the user.

Important: Make sure you copy the URL that it constucted in the long field before the blue and green buttons:

Next up - we need an API spec of our new API to give to Dropsource so it knows how to use it. Stoplight.io lets us make an API spec quickly and for free.

Signup for Stoplight.io (classical) free version and click "+ New" and choose API in the top left:

Then give your workspace a name and choose "Create from scratch":

Here you'll need to input some information about the API from your API's URL. Name and summary are up to you, but

API protocol + host:   https://sheetsu.com
API base path:         /apis/v1...(everything before the next /)

                    Correct: /apis/v1qrs
                    Incorrect: /apis/v1qrs/3hddn883nhdh

Hint: Remember that URL you copied before in Sheetsu? You'll paste a section of it into the "API base path" here. Only paste the "/apis/..../" part inclusive, do not include the trailing letters and numbers.

Go ahead and create your API! Then click "API Settings" on the left and choose "Prism":

Then scroll all the way to the bottom and turn "API discovery" on:

Scroll back up a bit and you'll see that Spotlight has created a proxy of your API:

Click on the proxy link and open a new tab. You should get an error "{"error":"Not such route /apis/v1...}"

That's because we're not calling our full API URL. Remember that full URL from Sheetsu you were supposed to save? Copy everything after the last / and add it to the URL in your tab's URL box, and then hit enter. Your URL and result should have roughly the same format as this:

I cut off the "ignore case" part of the API call but you can leave it. Note that your random letters and numbers should be different, but the basic structure - and ending with an ID=X - should be the same.

We're almost done with Spotlight. Next, go back to your main Spotlight tab and click on "Design" under "Modules" on the left.

Under the heading "API design" on the top left of the screen is a white column which should now contain the requests we just made to the Spotlight proxy of our API. Click on the one with the word "search" in it:

Then on the right look in the "Query string" section and it should say "ID    Integer". Spotlight could tell by the request we used before that our API performs a search for some information using an ID number. Nothing to do here - just nice to confirm it figured out how our API works with one request : )

Now on the top right, click "Export" and choose "OAS (Swagger) json"

And alas your API spec - a file describing your Sheetsu API which Dropsource can understand - will be downloaded to your computer.

Note to developers like Sheetsu and Dropsource - how about you guys document your user's APIs for them so they can skip these steps? Given that, you know, you make APIs and all..

Why did we do all of that? Basically, we told Spotlight about our Sheetsu API, then told Spotlight to create a proxy version of it, and then sent a request to it. By spying on the API request, Spotlight now has enough information to give us a file we need for Dropsource to understand how to use our API with our app.

Curtain fall on Spotlight... enter Dropsource.

Create your app visually in Dropsource

Go to Dropsource.com and sign up for a free (premium) trial. These instructions are based on Android, but iOS is similar.

Complete the tutorial to get the hang of how Dropsource works. Then do the following:

Add a text field element, a button element, and at the bottom, a dynamic list element. Then drag a dynamic list title element into the dynamic list, and then drag a text view into both of those. (Note - on iOS, dynamic lists are called "tables"). Feel free to change the input text hint and button text in their respective "property" panes on the right. This is what I ended up with:

You can see the nested stuff in the element tree more clearly:

Now we're going to make it so that when a number is input into the field and the button is tapped, two things will happen:

  1. A variable will be set to the number the user input
  2. The app will call our API and run a request with ID=[variable input by user]

Click on variables on the left and add a new one. Call it "ID_to_call" and set it as a type Data types => Java => integer. This variable will hold the ID number input by the user which will be sen to your API. You want your variable to look like this:

Click on the button Element, "properties", then "events", then next to "Tapped" hit "Add" (it may be "manage" if you clicked it previously).

Hit the plus and add a new "Set value" action, and set it it like this:

For the right side field, we're setting an element in the following way:

Then add a "Run API" action and set it like this:

Click on API, click the +:

..and then upload the JSON file you downloaded from Spotlight. Select the request with "search" in it and hit "Add":

Now you'll hook up the variable, ID_to_call, to the API request thus making it dynamic based on user input. In the "parameters" section of this new API's GET request, select Query(1) and bind the ID to the variable we just created, ID_to_call.

Move over the "response" section. We want the response of this API request to always fill a text view. Dropsource considers the response an "array" or a list of things, even though our response will only be 1 item as long as our spreadsheet has only one row per ID.

To make the response of our API fill in the app's text view, and thus close the loop form user input to API request to displaying info to the user, click on the Data source bar and drag it over to the dynamic list element on your app page, and then click on the "compliment" bar (or whetever you named that 2nd column in your spreadsheet) and drag it to the text view. You can confirm things hooked up right by noticing the green dot and clicking on them:

Launch your app with your new API!

You've done it! Now click "build" in the top right, click on the notification to try it out when done, and choose "web" version. Once in your app, simply type a # that corresponds to a row in your spreadsheet, tap the button, and the text box should fill with the next column's value. You can even change your spreadsheet at any point and the API won't skip a beat.

Keep on Learning!