I have a database on google sheets of around 100,000 record lines. Is there a way for me to do a search on them without brining them into glide? note: my app is completely based on glide sheets.
So i need to do some sort of api call
You can use a lookup formula if you don’t want to pay. The workbook that has the data you need you just have it equal the data you need to lookup and run the lookup on that workbook and the sheet you need the data returned to equals the cells with the results. Quite simple actually.
hi thanks for your reply. my glide app has no connection with googlesheets, it operated directly on glide tables. I need a use a API to lookup googlesheet database and pull the results into glide. I have no idea how to use sheets API inside glide
1 Like
Hola!
How long would you like to wait for an API answer using your GS with 100k rows: 2, 5, 8 or 10 sec?
GS is not a real database and has its limitation to find a value (row) quickly no matter your data has a kind of order or not. There are some tricks to improve the API answer but all depends on your needs and happiness: if you are happy with 10-12 sec, no problem… you can take this way/option 
.
Saludos @Amal
Thanks for replying back. I dont mind waiting 10 sec for the data to come back. Can you please let me know how i can do this? im new to api with glide
check this one out:
So there’s a work around I built quite some time back.
- Create a table.
- Add 10 blank rows in it.
- Add a single value column that brings in your searched value here.
- Add a coloumn for rowNo.
- Do your api call and limit the results to 10 rows.
- For each row get a json depending on rowNo.
- In another coloumn parse the json.
This sounds good. I just need to know how to use API for GSheets. any steps i can follow along with?
Hey @Amal
So a short process on how to use API calls.
In your google sheet, go to tools → App Script or Extension → Appscript.
So the page that opens up is the a code block where you can write typescript/javascript code.
The following script is already linked to your google sheet.
So you have to create a function as myFunction to fetch details from an API.
A basic code would be:
var url = “”;
var options = {
“method”: “post”,// method of api call (get or post)
“headers”: {// headers to be added (majorly used is Authorization)
“Authorization” : “”
},
“payload”: “”,// any type of data you want to send to the api-call can be sent using payload
“muteHttpExceptions”:true// this is used so you get the entire success/error response when you make the call
};
var response = UrlFetchApp.fetch(url,options);
You response is now available in the response variable which you can use.
To parse the response in JSON, use the code below.
var json_response = JSON.parse(response.getContentText());
If you need any specific help regarding your api call,
you can contact me at shantanu@modernizing-spaces.com
2 Likes
Hola de nuevo @Amal
I have a demo APP for what you want. I turned a GS into a REST API and works fine so far, with 45k rows and 8 columns the API calls reply around 4-6 secs.
Let me find some free time to make some modifications to my APP (e.g. translate it to english) to help you when you test it.
There are many web sites where you can see how to develop this kind of things, one better than other (or worse), take this as example:
I will be back soon!
Saludos
3 Likes
Interested in learning as well.
+1 for English translation 
Thanks in advance.
1 Like
Thank you so much, looking forward to a English tutorial !
ok so i managed to use follow a tutorial and write my own appscript, and i was able to pull the data into glide column, but it takes like 20 secs 
. . I also need to format it, but have no idea how to, below is the result it returns on searching with a Avatar number : 29796
{“data”:[[29796,104.338397839434,8,“Olive Oxford with Pocket”,“Buzz Cut”,“Stud Earring”,“Lunchbox”,“Dark Brown”,"",“White”,"",“Tan Workers Cap”,"",“White Leather Sneaker with Grey Sole”,“Charcoal Slacks”]],“error”:false}
As you see the headers are missing for some reason :\
What headers! Its a JSON response! What are you looking for?
how do i format it so its presentable
I may not be the right guy!
But the process i can think of is this:
I added the your JSON data in the first column
const data = String(p1);
const text = data.substring(10);
return text;
Used the code above to remove the first part of the string.
As the data can be of dynamic length, used this to eliminate the last part of the string
and finally did a split text to get all values in individual fields
So now if i want the first parameter
All parameters in array start at 0!
This is on assumption that your api always sends the same number of data , seprated.