[SPIKE] Spreadsheet based service as our Data Store

[pandevim]

Team: @esu2020 @pandevim @justonsky

Following up from #21. After exploring through different solutions:

• Giant database like MongoDB mentioned in #24
• NoSQL Database as a Service solutions like Airtable mentioned in #23
• A plug and play databases services like EasyDB and RestDB
• Serverless solutions like Firebase, AWS Lambda and Google App Engine

I came to see trade-off between complexity and free tier API limitations. My investigation led me to these solutions:

1. https://steinhq.com/ (5,000 requests/month)
2. https://sheet.best/ (2,000 requests/month)
3. https://sheety.co/ (500 requests/month)
4. https://sheetbase.dev/ (unknown)

The above mentioned Spreadsheet/Google Sheet based solution are free, easy to setup and maintain, support Restful JSON API, real time read/write, etc.

Some noticeable drawbacks are still present like less request limit and if the service went down but it seems sufficient and we always have data in google sheet where we can change the service and the website will keep running. Whereas API limits for Google Sheet API is 500 requests/100 seconds/project, and 100 requests/100 seconds/user.

So I spike Stein to be used along Google Sheets for our data store.

[justonsky]

Google Sheets is a great service but a one problem I'm thinking about is the lack of support for multiple selection / array values in the same cell (for the categories and techUsed properties of our project). Additionally, how would we also deal with Person objects in our spreadsheet, and when we try to retrieve data from the API?

[pandevim]

Thanks for addressing this. I totally forgot about our schema design. I will consider these as drawbacks because according to my knowledge there is no straight forward way to implement Array and Object in Google sheets. Maybe we can cleverly store our data in multiple Google sheets which can look like nested data from a high level or maybe there is some way in the 3rd party spreadsheet service. Till my next update I can try to build a prototype with some mock data and figure way out of these drawbacks.

[pandevim]

It turned out the js library of Stein is not providing any abstractions to structure JSON properly. And the closest thing can be done is to create different sheets for each object and array which create lots of problem in maintaining and entry of data.

Also while creating the prototype I ran into some problems like:

• These solutions don't have a very good API design
• Do not guarantee privacy in their T&C
• Do not grantee adequate up time for their service

Finally tried another approach in #31

[phil-ociraptor]

Some considerations here:

Pros:

• Can query Google's API directly: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get?apix_params=%7B%22spreadsheetId%22%3A%221MdlCGpAAMbPb4VsLJUJLk8zlZ1dM9O_euiw0jFP6AdU%22%2C%22range%22%3A%22A%3AM%22%7D
• We already use Google Sheets, so this would cause minimal amount of data duplication

Cons:

• Data returned is not rich and we would need to do a lot of data manipulation to massage it into the format that we've promised.