retrosheet 📄

Turn Google Spreadsheet to JSON endpoint. [For Android and JVM].

Benefits 🤗

• Since you're using Google's server, you don't have to worry about server health.
• Rapid response and unlimited bandwidth
• Since we're using OkHttpInterceptor under the hood, you can easily migrate to your REST API without any significant code change.
• You don't have to create an admin panel/dashboard to control the data. You can simply use Google Spreadsheet app.
• You can use this library to create POC/MVP instantly

Install 🤝

    implementation 'com.theapache64:retrosheet:1.2.3'

Usage ⌨️

How to write data ? ✍️

Step 1 : Writing Data To Sheet

• Create a Google Form with some fields

Step 2

• Select response destination and select/create a Google sheet to store the responses.

Step 3

• Now you can open the sheet and change sheet name and column names if you want. This is just to make the Google sheet table look like a real database table (optional)

I've changed to

Step 4

• Next, Press the Send button and copy the form link

Step 5

• Now let's go to our code and create our RetrosheetInterceptor

val retrosheetInterceptor = RetrosheetInterceptor.Builder()
    .setLogging(false)
    // To Read
    .addSheet(
        "notes", // sheet name
        "created_at", "title", "description" // columns in same order
    )
    // To write
    .addForm(
        ADD_NOTE_ENDPOINT,
        "https://docs.google.com/forms/d/e/1FAIpQLSdmavg6P4eZTmIu-0M7xF_z-qDCHdpGebX8MGL43HSGAXcd3w/viewform?usp=sf_link" // form link
    )
    .build()

Step 6

• Next, let's create a normal Retrofit API interface

interface NotesApi {

    @Read("SELECT *") 
    @GET("notes") // sheet name
    suspend fun getNotes(): List<Note>

    @Write
    @POST(ADD_NOTE_ENDPOINT) // form name
    suspend fun addNote(@Body addNoteRequest: AddNoteRequest): AddNoteRequest
}

• @Write : To write data to a sheet

• @Read : To read data from a sheet.

You can lean more about query language from here : https://developers.google.com/chart/interactive/docs/querylanguage.

NOTE: You can use your column name in the query rather than using column letter such as A,B,C etc.

How to read data ? 📖

Step 7 : Reading data from Sheet

• We're done configuring the writing part. Now let's finish the reading part. Create/open a google sheet, (it can be either form connected, or a simple Google sheet).

• Press Share and copy the link

Step 8

• Remove contents after the last forward slash from the copied link.

For example, this

https://docs.google.com/spreadsheets/d/1IcZTH6-g7cZeht_xr82SHJOuJXD_p55QueMrZcnsAvQ/edit?usp=sharing

would become this

https://docs.google.com/spreadsheets/d/1IcZTH6-g7cZeht_xr82SHJOuJXD_p55QueMrZcnsAvQ/

Step 9

• Finally, Set the Retrofit or OkHttp's baseUrl with the above link.

Done 👍

Full Example 🌟

import com.squareup.moshi.Moshi
import com.theapache64.retrosheet.RetrosheetInterceptor
import kotlinx.coroutines.runBlocking
import okhttp3.OkHttpClient
import retrofit2.Retrofit
import retrofit2.converter.moshi.MoshiConverterFactory

/**
 * Created by theapache64 : Jul 21 Tue,2020 @ 02:11
 */
const val ADD_NOTE_ENDPOINT = "add_note"
fun main() = runBlocking {

    val retrosheetInterceptor = RetrosheetInterceptor.Builder()
        .setLogging(false)
        // To Read
        .addSheet(
            "notes", // sheet name
            "created_at", "title", "description" // columns in same order
        )
        // To write
        .addForm(
            ADD_NOTE_ENDPOINT,
            "https://docs.google.com/forms/d/e/1FAIpQLSdmavg6P4eZTmIu-0M7xF_z-qDCHdpGebX8MGL43HSGAXcd3w/viewform?usp=sf_link" // form link
        )
        .build()

    val okHttpClient = OkHttpClient.Builder()
        .addInterceptor(retrosheetInterceptor)
        .build()


    val moshi = Moshi.Builder().build()

    val retrofit = Retrofit.Builder()
        .baseUrl("https://docs.google.com/spreadsheets/d/1YTWKe7_mzuwl7AO1Es1aCtj5S9buh3vKauKCMjx1j_M/") // Sheet's public URL
        .client(okHttpClient)
        .addConverterFactory(MoshiConverterFactory.create(moshi))
        .build()

    val notesApi = retrofit.create(NotesApi::class.java)
    println(notesApi.getNotes())

    // Adding sample order
    val addNote = notesApi.addNote(
        AddNoteRequest("Dynamic Note 1", "Dynamic Desc 1")
    )

    println(addNote)
    Unit
}

Samples 🌠

• Notes - JVM - README Example 👆
• Notes - Android - Android App : Simple note taking app, with add and list feature
• Nemo - Android App : E-Commerce App
• More JVM Samples

TODO ☑️

• Create an online tool to generate model class from sheet link

Author ✍️

• theapache64