It’s just a Proof of Concept which I wanted to do since a long time ago to know how reliable and fast a Google Sheet can handle a large amount of data and be used as an API to receive/send data frequently.
As you should know, GS is not a native database therefore, some key functionalities (like an indexed file) don’t exist and can’t be used to improve the lookup speed. GS has the Filter() and Query() as tools to search data but it isn’t enough to make a lookup fast or optimal, both of them must to read all rows/registers from top to bottom to find all coincidences due to a missing indexed file which internally has ordered all data (rows) depending on a key field/column.
But it’s what we have and is what I wanted to test with this demo APP, nothing awesome you will see on it, you just have to select the Product Code from a list (there are 17 codes to test) and later, select which Vendor has this code available (product). Also, you can search the product code among all vendors ( TODOS / ALL option) and the APP will make a call to GS to find out all vendors with that product available along with their prices.
My GS working as REST API has 45.000 rows with 8 columns and it should reply about 4-6 secs to any API call if everything is fine. Of course, this process is not asynchronous (answer calls queue), my script in GS was deployed as a web app using my Gmail account so, multiple sessions or instances of my script are not running in parallel so far. Again, its just a PoC to test!
In the end, the goal of this demo is to check and know how fast a GS turned into Rest API can be if this is used for:
- Save old data which is read/checked occasionally
- Have a very important and large amount of data for our APP (payments, sales data, events, inventory) but it isn’t loaded directly on it in order to save space/rows and improve its performance.
- Have a plan B when a GS has grown too much and its manipulation is a headache.
I don’t know how long a GS with 100k rows can answer an API call but with 45k rows lasting 4-6 sec, this option doesn’t seem bad so far.
I hope this helps someone someday.