Over the years there has been an advent of a whole lot of new real-time databases. As a developer, we always want to utilise the perfect database for our project. The database must have high number of access functions, must have real fast access speed and most importantly have the space to store all our data. Recently Firebase Database has gained a lot of followers when it comes to providing real-time data access and storage.
But, there are certain cases where an application may not require the use of such a heavy database (like Firebase) for real-time data fetching. These may generally be small-scale projects. In this article, we will delve into how Google Sheets is used for a simple real-time database access. Let’s get on with it!
Let’s create a application which can be utilised for both admin-side and client-side.
The app will be about the availability of books of a certain book store.
So basically the the admin will update about the availability of books present in his store, which the client will get to know in the app.
Let’s get on with the flow of steps
A) The manager updates the details in the spreadsheet manually
The manager updates the details of book availability manually. In my next article, we will find out how he updates the details using the application. But for simplicity will take the former case.
B) Fetch data from the sheet using Google Sheets API
The app needs to fetch the real-time data of book availability. So, Google Sheets API will fetch results from the above sheet in the form of a JSON file format. We use Retrofit and okHttp Client for handling the network requests and making the data meaningful. Let’s make our POJO
C) Form the URL of the API
A normal URL for a GET type request goes like this
a) The Sheet ID is present in the URL of the sheet we are dealing with.
b) The Range is the fetching range of cells.
For Example → Sheet1!A2:B6
c) The API Key has to be generated using the help of Google Developer Console. A link has been shared below regarding the Key Creation.
D) Observe the JSON data
After forming the GET request link, we search the URL in the web browser for viewing the data. Using POSTMAN is also recommended for any API related queries as a web browser may only allow a GET request and not other requests such as POST or PUT.
You may stumble on an error stating that access to the sheet was denied. This might happen due to the Sheet being private and thus making it public is recommended. An upcoming article will address this problem of fetching a private Sheet using OAuth credentials!
Let’s head on to the Kotlin part of our project.
E) Import crucial dependencies in build.gradle file
dependencies implementation fileTree(dir: 'libs', include: ['*.jar'])
implementation "org.jetbrains.kotlin:kotlin-stdlib-jdk7:$kotlin_version"//Import okHttp dependenciesimplementation 'com.squareup.okhttp3:logging-interceptor:3.4.1'
implementation 'com.squareup.okhttp3:okhttp:3.4.1'
//Import Retrofit dependenciesimplementation 'com.squareup.retrofit2:converter-gson:2.4.0' implementation 'com.squareup.retrofit2:retrofit:2.1.0'
>
E) Create BookObject in Android Studio
data class BookObject(
@SerializedName("range")
var range: String,
@SerializedName("majorDimension")
var majorDimension: String,
@SerializedName("values")
var values: ArrayList
)
data class Item(
var bookName: String,
var availability: String
)
When we observe the JSON file carefully, we can make an interesting observation that the data is in a form of a list of certain items. Here, an item consists of a name (Book Name) and the availability (Sold Out Status). Thus this POJO is set out in such a way that it will take care of all necessary fields. In the app, the data will be an Object which has comprehended data from the JSON format, thus making it meaningful for the app to understand.
F) Create an API Client handling Retrofit
internal object APIClient
lateinit var retrofit: Retrofit
val client: Retrofit
get()
val interceptor = HttpLoggingInterceptor()
interceptor.level = HttpLoggingInterceptor.Level.BODY
val client = OkHttpClient.Builder()
.addInterceptor(interceptor)
.connectTimeout(2, TimeUnit.MINUTES)
.readTimeout(2, TimeUnit.MINUTES)
.build()
retrofit = Retrofit.Builder()
.baseUrl("https://www.googleapis.com/")
.addConverterFactory(GsonConverterFactory.create())
.client(client)
.build()
return retrofit
>
>
Basically Retrofit fetches the data from the given Url and uses a converter to convert the data into parseable JSOn or XML. In our case we need a GsonConverter for JSOn Files.
okHttp Client helps us in handling network request and timeout properties. This helps in handling network requests much better when there is an unreliable net connection. If we increase the connectionTimeout property, it will try to connect again and again until and unless data is fetched. After a certain time (connectionTimeout), it will disconnect.
G) Create an API Interface for Retrofit
interface ApiInterface @GET("SheetId>/values/Range>?key=API Key>")
fun getBooks() : Call
>
This is a prerequisite interface for which be built by out API Client. We mention our methods in the interface. “@GET” is an Retrofit annotation which signifies that out request is a GET Request. The function getBooks() will return data in the form of a Call
G) Fetch Data in the Main Activity using function getBooks()
The function getBooks() is present in the above interface.
apiInterface = APIClient.client.create(ApiInterface::class.java)
val call = apiInterface.getBooks()
call.enqueue(object : Callback override fun onResponse(call: Call, response: Response) Log.d("Success!", response.toString())
var text = response.body()
var bookList = text.values() >
override fun onFailure(call: Call, t: Throwable) Log.e("Failed Query :(", t.toString())
>
>)
>
We fetched the bookList containing a list of books along with their availability status. Any operation, such as featuring the list in a RecyclerView, can be performed. It’s up to the user as in how he or she deals with the data!
Thus, this is how Google Sheets API can be used a real-time database for small-scale projects. In my upcoming article, we will deal with OAuth Access and how the Book Store Manager is able to update data from the app!