openblocks-dev
Query Google Sheets
Hi!
If I see it correctly you can only Read the entire Google Sheet and not do a search query on it. Goes fine for small sheets but if you have to retrieve 100.000s of lines, that will slow down the system :)
Would like to see a way such as SQL to do searches based on column, max results etc
Hi @TychoCeltCast,
Thanks for the post, but I was wondering if Google has provided with search APIs? Please help submit it to us if you find one. And to be honest, we previously thought Google Sheets is just for storing hundred of rows of data (maybe thousands), but never to the magnitude above 100 thousands where a real database (Postgres, MongoDB etc.) should be used considering performance and stability.
Thank you @neon-balcony It does indeed seem that it does not exist in API directly. A way around it that I've thought of would be to create a new tab, use the =QUERY(A1:D234,"SELECT B, D" there, and then return the fields from that tab.
Got it, does existing API support this operation?
=QUERY(A1:D234,"SELECT B, D"
Got it, does existing API support this operation?
=QUERY(A1:D234,"SELECT B, D"
I don't think there is a API directly for it, I was thinking it could update a cell and this formula into it. Wait for the results and get them :)
It does work to change 1 row except it adds a ' to make it a string and not a formula
Hi, does @TychoWerner's post above answers your question?
I believe what you want is Google's query function, but by skimming through the sheets API docs, I don't think Google provides us with that accessibility.
Hi, does @TychoWerner's post above answers your question?
I believe what you want is Google's query function, but by skimming through the sheets API docs, I don't think Google provides us with that accessibility.
Hi, that is actually also my account, accidentally switched.
My solution could work except that it adds a ' to the formula rendering it as a string
If there is a way to set a formula from OpenBlocks to Google Sheets I could continue on a solution.
Okay, could you please record a short video to reproduce the issue about getting that quote ' for your workaround? I kind of got lost by looking at the snapshots above.
Okay, could you please record a short video to reproduce the issue about getting that quote ' for your workaround? I kind of got lost by looking at the snapshots above.
Ofcourse! https://youtu.be/g4mrGJ7l_fs Hope this helps 😃
This might be of assistance: https://developers.google.com/sheets/api/samples/data#repeating_a_formula_over_a_range
It shows how a formula can be added
After our debugging, we are sorry to find that the ' is automatically added by the google server to escape the formula as a string. And sadly we can't find a way to work around it as a formula.
Mark it as closed because current Google Sheets API cannot afford query semantics required here.