A Google Sheet working as a REST API: good or bad idea? 💣

[gvalero]

Hola!

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.

API para Repuestos

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 think @Jacopo_Chiapello and @Amal can use this option if your APPs are needing something trustworthy to keep growing with no traumas

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.

The friend @Robert_Petitto weeks ago wrote this useful thread which can help to understand this idea as well: NoCodeAPI + Fetch ❤️

I hope this helps someone someday.

Feliz día!

[Uzo]

nice, but you will get the same result if you just use the filter formula in GS to bring searching data to glide… much easier, also you can make a waiting image while waiting for results…
i based my search on the filter formula, and it takes from 5-10 sec to run 163,819 rows with two searches for starting with keyword and including a keyword

English Dictionary

[gvalero]

@Uzo

nice, but you will get the same result if you just use the filter formula in GS to bring searching data to glide… much easier

Hola,

I understand the case but the goal is quite different.
I guess you are using a combination of Filter((Importrange()) functions to get the result from an external GS and show it on APP later, it’s fine but the problem with it is that all users using your APP (at the same time) will see the same info/result.

If I’m looking for the meaning of “Monday” on your APP and another user is typing “Blue” at the same time, I will see the result based on “Blue” suddenly causing a confusion because of the last searching overwrites any previous result always. Your trick doesn’t allow multi-user operations unfortunately (any user must have and see his own result) although this can be fixed if you implement the old method we used to use when the USC didn’t exist (2019) and needed to create a basic calculator (https://multicalc.glideapp.io).

Instead, my GS (working as API) replies calls and its results are saved in computed columns (unique values for each user) so, each user or APP’s instance has its own values, these are not public.

You can see this openning the APP on several web browser’s tabs, each instance has its own values and run independently of one another:

Thanks for feedback, saludos!!

[Amal]

This great. However Can you please make a tutorial on how to achieve this. A lot of us (non-coders) have no idea about how to get API and then query it down and show it in a formatted manner. I was able to pull my data from GS using some appscript i wrote (followed a turotail), but it takes me 20 sec to bring it into glide, and even after it comes in i have no idea how to format the json .

[Eric_Penn]

[Yasin_Hassanien]

Great work, can we copy the app so we can check the backend ?

[gvalero]

Hola de nuevo,

Sorry for not replying to you sooner, I’ve been busy these days

@Amal, I am not very good making videos or a tutorial Also, in my point of view, develop an API using scripts is beyond of No-Code movement (no offense), the programming has its time, logic, tricks and sometimes even, Low-Code tools can’t have all solutions natively (thus, the plugins are created).

But its doesn’t mean people can learn step by step and work with specialized topics beyond of current capacities. In this community there are many cases like it and sometimes I have been surprised to see what they have done, Glide also helps a lot to achieve this, no doubt.

This is best explanation I have found to create a REST API using a GS, it’s also a tutorial and this is how a part of my script looks like:

@Yasin_Hassanien my APP is just a demo, I used some tricks, shortcuts and dummy columns that probably I will discard in my next versions so, I wouldn’t like to confuse you with it.

But if you tell me specifically what configuration you want to know/create, I can give you some tips or PICs, no problem.

Saludos, feliz día!

Gavp

[gvalero]

@miroslav_madaric

[ThinhDinh]

Do you still maintain this project? Would it be easier to do now with the Call API column?

[gvalero]

Please forget my last reply @ThinhDinh, I mixed up the thread you were referencing.

As soon as I have some “free time” I’ll improve this demo/option, although I’ve been working with Google Sheets API v4 since months ago and I think I can play with this API as well, saving me a lot of development time.

Saludos!