If you have data in a Google spreadsheet, you can connect it to your Dropsource app using Sheetsu. Sheetsu allows you to make API requests to the data stored in a Google sheet, so you can retrieve and send values between your speadsheet and your app. In order to connect your spreadsheet to Dropsource, you first need to create a Swagger (OpenAPI) specification documenting the endpoints you plan to use via Sheetsu.

Setup your Spreadsheet

If you don’t already have one, create your new spreadsheet in Google Sheets and add some data. In the first row of your spreadsheet, enter column names, with rows of values in the space below.

Your API requests will use the column names to identify data.

Setup Sheetsu

Sign up / in to Sheetsu. Open your spreadsheet and copy the URL from the address bar – paste it into your Sheetsu dashboard and click Create API from existing spreadsheet.

Author your Specification

Sheetsu will automatically provide default endpoints to your spreadsheet to retrieve, search, add, update, and delete data. To connect this to your Dropsource app you will need to document the endpoints you plan to use in an OpenAPI (Swagger) specification.

You can author your specification manually, or using a tool such as Stoplight. Use the Sheetsu Query Builder for your API to try out endpoints and see the request / response structures (you can paste these into Stoplight as example data to generate the schemas for each of your endpoints).

You can refer to the example Sheetsu API spec in the Dropsource GitHub repo.

When you author your spec:

• Include the host which should be sheetsu.com and a basePath value which should have the format /apis/v1.0.
• Add each endpoint you plan to use in your Dropsource app – specify the path for each one, which should include the text string that appears after /apis/v1.0 in your Sheetsu API URL, e.g. /94441cadfd3a.
• Indicate application/json for the consumes and produces properties.
• Include the relevant response status codes for each endpoint (e.g. 200, 201, 204).

Your spec should outline should look something like this, with additional detail for the paths:

Each path should define the request and response structures, such as this example to retrieve a whole sheet:

Setup Auth

You can authenticate requests to your data if you need to. In your Sheetsu dashboard, select the project Settings and switch on auth using the Authenticate API control.

Copy the API key and secret – you will enter them into your Dropsource app. Sheetsu uses basic auth, so indicate that in your specification at the root level:

Apply the auth inside each endpoint in the spec:

You will enter the API key and secret as a username and password to authenticate requests from your Dropsource app, and this will restrict access to all of the data in the spreadsheet.

Connect your Dropsource App

Once you have your spec ready you can connect the API to Dropsource. In the Dropsource editor, import the spec in the API tab on the right of the editor.

Upload your JSON spec file, or import it by URL if it’s online. If you used Stoplight, you can export your specification and download it onto your computer first.

Dropsource will add the API to your project.

Authenticate your Requests

If you have auth switched on for your Sheetsu API, you will need to pass the API key and secret from your app. In Variables on the left of the editor, add two Device Variables, choosing Swift/Java String type for each.

Give the variables the values you copied from your Sheetsu settings using Set Value Actions that will run before you attempt to make a request to the API, for example in Lifecycles on the left of the editor, using the Application Launched Event.

In the API tab, open the Set Authentication control for your Sheetsu API.

Select your key variable for the username, and the secret variable for the password.

Dropsource will pass the values for each endpoint you have set to use auth in your spec.

Make Requests

Once you have your auth setup configured, you can go ahead and make requests to your Sheetsu API. To retrieve the whole spreadsheet, add the GET /your-project endpoint and bind the results to a dynamic Element such as a Table or List, with the values from each column displayed in Elements inside the dynamic parent Element (e.g. Text Views inside Table Cells or List Tiles).

Add a Run API Request Action to an Event in your app in Properties > Events, such as a Page Loaded or On Create Event, or an Element interaction Event such as Tapped.

Test your app and run it in the web simulator to see your spreadsheet data written into the UI via the Sheetsu API request – click Test, then in Builds, open your app in the simulator.

Search, Add, and Edit Data

You can add requests to search your data, to add new data, and to edit/delete data using the same process as the request to retrieve the whole sheet, but by also passing parameters.

Search your Spreadsheet

To search the spreadsheet, use the GET endpoint with /search in it. You can pass values to find in any of your columns via the Query string, specifying a value statically, from data returned from an existing request, or from user input, e.g. from a Text Field in your app.

Your search query can include wildcards, so for example, you could set a Page Variable initially to have the value * and pass it as a parameter to one of your columns, which will initially return all rows.

Then you could update the variable, e.g. when the user taps an Element displaying a value from the spreadsheet, to filter the search to return all rows with the same value in the specified column.

This allows you to dynamically filter the data displayed from your spreadsheet:

Add Data to your Spreadsheet

To add a new row to your spreadsheet, use the POST endpoint. Specify values for each column via the Body parameters – again, these values can come from user input with your app Elements.

Edit Data in your Spreadsheet

Use a PATCH or PUT endpoint to update a row in your spreadsheet. Like the POST endpoint for adding new rows, you can pass new data values in the Body parameters.

You can alternatively use a PUT request if you want to clear any values in the row that you do not supply as parameters.

Delete Rows from your Spreadsheet

You can delete a row in your spreadsheet and optionally destroy the empty cells using a DELETE endpoint. For the “destroy” version, which clears any empty cells from the sheet following deletion, specify the row to delete using the Body parameters, for example by binding to an ID value from a list item (displayed from a previous GET request and selected by the user while the app runs).

Use your Spreadsheet Data

Sheetsu provides a quick way to connect your spreadsheet data to a Dropsource app, allowing your app users to carry out a variety of processes on the data, including searching it. If you need help authoring your specification, try starting with the sample version in the Dropsource GitHub repo, altering the column names to suit the names in your own spreadsheet. If you have trouble integrating your Sheetsu API with your app, check out our API request troubleshooting guide.