retrosheet: a Google Sheet behind a Retrofit-style API

What retrosheet is and the problem it removes

retrosheet turns a Google Spreadsheet into a JSON endpoint, and the repository description is blunt about the constraint that makes it possible: without any login or authentication. The library is written in Kotlin and published as a Kotlin Multiplatform artifact, with the repository description listing Android, iOS, JVM and JavaScript as the supported platforms, and the README carrying a badge for each of them.

The problem it removes is the backend. Almost every prototype with a form or a small table needs somewhere to put the data, and the usual answer is a server, a database, an API key, an auth flow and a deployment. Google Sheets already provides a spreadsheet with a query language, a form with a public write endpoint, and a sharing model that needs no application code. Retrosheet puts a Retrofit-shaped interface in front of that.

The README's list of benefits names the same motivation in its own words: free analytics through Google forms, the ability to migrate to your own REST API with minimal code changes, an easy real-time interface for the data, and faster development of a proof of concept or an MVP.

That last point is the honest frame for the project. This is a tool for the phase where the data model is still moving and nobody has decided what the API should look like. The library's interface is deliberately shaped like the API you will eventually write yourself, which is what makes the later migration claim credible rather than marketing.

The implementation underneath is Retrofit-shaped but not Retrofit. The topic tags name interceptors, OkHttp and Retrofit, and the code uses Ktorfit, the Ktor-based retrofit implementation, with Ktorfit annotations on the API interface and a KSP processor generating the implementation.

The three annotations that carry the whole design

The API you write is an interface with Ktorfit's HTTP annotations, plus three from this library. The README states their roles directly: @Write is used for adding data, @Read for reading data, and @Update for updating an existing row.

A typical interface looks like this:

import de.jensklingenberg.ktorfit.http.*
import io.github.theapache64.retrosheet.annotations.Read
import io.github.theapache64.retrosheet.annotations.Update
import io.github.theapache64.retrosheet.annotations.Write

interface NotesApi {
    @Read("SELECT *")
    @GET("notes")
    suspend fun getNotes(): List<Note>

    @Write
    @POST("add_note")
    suspend fun addNote(@Body note: Note): String
    
    @Update
    @POST("add_note")
    suspend fun updateNote(@Tag updateKey: String, @Body note: Note): Note
}

@Read carries the sheet name and a query as arguments, here "SELECT *" against the sheet named notes. That is not a coincidence: the library uses the Google Visualization query language, and the README links the Google documentation for it, so the read path is a real query rather than a full-table fetch with client-side filtering. The @GET value is the sheet name, and the base URL points at the spreadsheet.

@Write and @Update both target a form endpoint, with @POST naming the form. The difference between them is where the write lands and whether the response carries an update key.

The annotations are processed at build time by KSP, which the build example shows as the com.google.devtools.ksp plugin, and the converter is supplied to the Ktorfit builder as RetrosheetConverter(config). So the call you write is a normal suspend function, and the machinery underneath is an interceptor plus a converter that rewrites the request into a Sheets or Forms call and parses the answer back into your data class.

Setting up: the wizard, or doing it by hand

The library is on Maven Central, and the README's dependency block is short enough to copy as is:

repositories {
    mavenCentral()
}

dependencies {
    // [latest version - i promise!]
    implementation("io.github.theapache64:retrosheet:3.0.3")
}

There are two setup paths. The quick one is the wizard at a64.in/retrosheet/wizard, which the README says can set up the backend in about three minutes: create a Google Form if you want to write data, a Google Sheet if you want to read data, or both, publish as usual, fill in the form in the wizard, press Generate Code and paste the result into your project. There is a video demo linked from the README for the parts a screenshot does not convey.

The manual path is where the real understanding comes from, and it starts with a config object that names your sheets and their columns in order, and your forms:

val config = RetrosheetConfig.Builder()
    .setLogging(true)
    // For reading from sheet
    .addSheet(
        "notes", // sheet name
        "created_at", "title", "description" // columns in same order
    )
    // For writing to sheet
    .addForm(
        "add_note",
        "https://docs.google.com/forms/d/e/1FAIpQLSdmavg6P4eZTmIu-0M7xF_z-qDCHdpGebX8MGL43HSGAXcd3w/viewform?usp=sf_link" // form link
    )
    .build()

val ktorClient = HttpClient {
    install(createRetrosheetPlugin(config)) {}
    ...
}

Two things in that snippet carry the design. The sheet registration lists the columns in the same order as the sheet, so the library knows how to map a row to your data class, and a change to the sheet means a change to this list. And the config is attached to a Ktor client through a plugin, so the behaviour is installed into the HTTP stack rather than hidden inside a wrapper object.

Reading: publishing a sheet and pointing the client at it

The read path depends on a sheet that is reachable over the web without a login, which is the same assumption the whole library rests on.

The steps in the README are: open a sheet and copy its shareable link, then edit the link by trimming everything after the last slash. The example shows the sheet identifier and the edit parameters, with the latter struck through, leaving the base path. That trimmed URL is what you set as the baseUrl in Ktorfit:

val retrofit = Ktorfit.Builder()
    // Like this 👇🏼
    .baseUrl("https://docs.google.com/spreadsheets/d/1YTWKe7_mzuwl7AO1Es1aCtj5S9buh3vKauKCMjx1j_M/")
    .httpClient(ktorClient)
    .converterFactories(RetrosheetConverter(config))
    .build()

The three pieces are the base URL, the Ktor client that carries the plugin and its config, and the converter factory that teaches Ktorfit how to turn a sheet row into your type. Once those are in place the README marks the setup as done, and the interface method is a normal suspend call.

Two limits follow from using the Sheets query language. The query is expressed in Google's dialect, and the library links the query language guide, so the vocabulary of SELECT, WHERE, ORDER BY and the pivot functions is what you have, not SQL. And the result set is whatever the query returns, with the column names in the config deciding how a row becomes a field of your data class, so a mismatch between the declared column order and the actual sheet is a silent source of wrong values rather than a parse error.

Writing and updating through a Google Form

Writing goes through a Google Form rather than the sheet directly, and the reason is that a form is the only Google endpoint that accepts input without authentication.

The setup in the README is a form with required fields, a response destination pointing at a sheet, and optionally the sheet renamed and its columns rearranged. The form link is what you register in the config with addForm, and the name you give it there is the name you use in the @POST annotation. So "add_note" in the interface maps to the form you registered under that name, and the sheet it writes to is the response destination you chose in the form's own settings.

Updating is the part with a real prerequisite. The Google Form has to allow response editing, which the README describes as going to Settings, General, and enabling Allow response editing, and it repeats the warning at the end: without that setting the update feature will not work.

The mechanism is a key rather than a row identifier. When you want a row to be updatable later, your @Write function returns a String instead of the object, and that String is the update key:

@Write
@POST("add_note")
suspend fun addNoteForUpdate(@Body note: Note): String

The key is then passed back as a @Tag parameter on the @Update method:

@Update
@POST("add_note")
suspend fun updateNote(@Tag updateKey: String, @Body note: Note): Note

The README's usage example shows the two calls in sequence, adding a note to obtain the key and then updating that note later with the key. This is a reasonable design for a spreadsheet backend, since a sheet has no primary key of its own and the form's response identifier is the closest thing to one.

Where retrosheet is the wrong tool

The first limit is the one the library's own description advertises. No login, no authentication, means the data has to live in a sheet that is reachable the way the library expects, and that has consequences for anything private. The README's first listed benefit is free analytics via Google forms, which is a fine use for survey responses and a bad fit for a spreadsheet of customer records. If the data must not be world-readable, this design does not work, and no amount of configuration changes it.

The second limit is that a spreadsheet is not a database. There are no transactions, so a write that races another write is resolved by whatever order the form submissions arrive in. There is no locking, no constraint checking and no rollback. The update mechanism depends on a key that the form returns, which is a reasonable approximation of a row identifier but is not a durable primary key, and the key is only produced when response editing is enabled, so the update feature is a setting-dependent feature rather than a guaranteed one.

The third limit is the query dialect. Reads go through the Google Visualization query language, which covers selection, filtering and ordering well, but it is not SQL, and the mapping from a row to your object depends on the column order you declared in the config. Change a column in the sheet without changing the config, and you get wrong data rather than an error.

The fourth limit is the operational picture. A sheet is edited by people, and the library's assumptions about column order, sheet names and form configuration are exactly the assumptions a human collaborator breaks by inserting a column. That makes this a good fit for a prototype and a poor fit for anything with a second person touching the data.

The alternative is a real backend, and the difference in approach is ownership. A hosted API means your own data model, your own validation, your own auth and your own query language, and a migration path that the README's second listed benefit anticipates, since the interface you wrote here maps one to one onto a Retrofit service. Until then, the spreadsheet is the cheapest server that exists, and for a proof of concept it is hard to argue with the arithmetic.

Maintenance, releases and the licence

The repository is not archived, and the last push was on 2026-09-05. The published releases are 3.0.1 on 2025-07-14, 3.0.2 on 2025-07-15 and 3.0.3 on 2025-07-28, which is a burst of patch releases in the space of a fortnight and then a gap of more than a year before the last push. That pattern says the work happens in short campaigns rather than on a schedule, and it is worth remembering when you plan a dependency upgrade.

The version in the README's dependency block is 3.0.3, with a comment that it is the latest version, and it is the same version that appears in the full build example with the Ktor 3.1.3 and Ktorfit 2.5.1 dependencies and the Kotlin 2.1.10 plugin versions. Those numbers are a useful reference point, because the library sits on top of Ktorfit and Ktor and inherits their breaking changes, and the README's example pins the trio together for that reason.

Two migration documents sit at the root of the repository, MIGRATION.md and MAVEN_CENTRAL_MIGRATION.md, which is the sort of thing that tells you the artifact's coordinates have changed at some point and that people upgrading need to read something. The repository also holds a dist/ directory, a wizard-api/ directory that presumably backs the web wizard, a kotlin-js-store/ directory for the JavaScript target, ktlint.gradle for formatting, and a sample/ directory with composeApp and iosApp subdirectories for the two mobile targets.

The licence is Apache-2.0, with a LICENSE file in the repository root. The README also carries a badge declaring that no AI was used, which is unusual enough to be worth mentioning as a project statement rather than a technical fact, and it sits alongside the ordinary badges for the supported platforms.

Editorial conclusion

retrosheet fits a developer building a prototype, a hackathon entry or an internal tool where the data model is a table, the user base is small, and standing up a server would cost more than the spreadsheet. It does not fit a product with private user data, concurrent writers, transactional requirements or a need for arbitrary queries. Verify first that the sheet is published to the web in the way the base URL assumes, that the columns you declared in RetrosheetConfig match the sheet header order, and that the form has Allow response editing enabled if you intend to use the @Update path, since the update key is only produced when that setting is on.

Frequently asked questions

Official sources

1. License: Apache-2.0
2. Project website
3. README
4. Releases
5. theapache64/retrosheet on GitHub