Hello,
I have a huge list created in google spreadsheets, when I click a button in my application, make comparisons, calculations etc. and according to a certain condition I get a list.
The application stops a while doing all the defined processes, if i increase the list stops for a long time. I receive the results, but stays frozen for a long time. Which is not very positive.
Is there any way to overcome this situation? Or any suggestions ?
Many Thanks
Define huge... number of rows/columns
hello,
2000 rows but it will increase and 5 columns.
TY
What does your data look like?
Please describe what you app is doing with the data?
Can't see that the data from google sheets in itself is causing a problem (e.g. fetching the data / sending the data) - you don't show this part in your blocks, it must therefore be in the processes in your blocks. Perhaps we can streamline to processes if we understand what you are doing.
(a) I have a list of countries that are added in the app.
(b) and a list in google sheets of users (column A) and all the countries that are related to that user (column B), i added in column C to be easier to do the count - total of countries for user; Column D is category and E a link.
Col 1 Col 2 Col 3
Jordan Australia 6
Jordan Brasil 6
Jordan Canada 6
Jordan Spain 6
Jordan France 6
Jorgan Japan 6
Eve Canada 4
Eve Spain 4
Eve France 4
Eve Portugal 4
Taking into account all the countries added in the first list (a) i want to receive the name of the users that have less than 3 countries. For example if in list (a) i have Canada, Spain my result will be Eve taking into account list (b).
What i did:
1st list (c) I compared the texts between list (a) and column 2 of list (b) and created a list of users that have those countries.
Here the result will be Frank Canada 6 Category Link/ Frank Spain 6 Category Link/ Eve Canada 4 Category Link/ Eve Spain 4 Category Link
2nd (d) created a second list only with the name of the users that were added in list (c) (the user appears only once in this list)
Result: Frank / Eve
3rd (e) for each element of list (d) count how many times appears in list (c)
Result: Frank 2 / Eve 2
4th (f) when the user in list (e) is equal of user in list (b) i create a list with user / total countries / count / subtraction between the total and count / category
Result : Frank 6 2 4 Category / Eve 4 2 2 Category
5 th (g) created a list if the subtraction is less than 3 shows
the result in this case Eve Category (this is the only list that appears in the application)
Ty
You can have Google do the work on their server using SQL.
See the bottom of
http://puravidaapps.com/spreadsheet.php
and the syntax at
https://support.google.com/docs/answer/3093343?hl=en
Thanks @ABG
Here is a simple query:
from this data
https://developers.google.com/chart/interactive/docs/querylanguage
and another:
Not sure I completely understand the output you want, tell us from the data shown here how it should work and I can help with the query (hopefully)
Then all you have to do is download the output to your app. You should be able to feed the query with parameters is needed (you may need a web app for this)
Hello,
to do what you are suggesting i've to export the information received in the app - the countries added - to google sheets, how i do that ?
Because i read the tutorial and i think (maybe i'm wrong) what is mentioned is export the information from google sheets to the app. I want to import to google sheets the information received by the app.
Ty
Hello Tim and Ty
First what is missing, taking into account what you and ABG answer (Work the data in google sheets). Probably i need to import to google sheets the data received from the app.
Because as i write before that is the 1st step.
The user in the app add countries.
So, how can i work the calculations in google sheets if that list (of the countries added by the user in the app) are not there.
Ty
I cannot see in your blocks anywhere that you are inputting parameters to affect the output, maybe you are but this is not clear....
If this is the case, then yes, you can send the parameters to the google sheet and get the output as a return. You won't need to call down the all the data from the google sheet. See here:
The "global countries_selected_list" is the info received from the app, that i compare with the list of google sheets (user_table).
I'm going to read the link you sent, and see if i can understand how to do it.
Ty
I'm going to check. TY ABG
If you are happy for your spreadshet to be "anyone with the link" you can also use google visialization query (uses google query language). examples:
Hello Tim,
I am trying to do what suggested. But i see that most of the tutorials is to add one by one data.
It's possible to just export a list already created and stored in tiny db to google sheets? without adding and deleting and updating, just the list stored.
TY
This sounds like you are looking to do something completely different to your original question.....but yes, it is possible to upload a range of values to google sheets:
No, it's the same. But the first step, i think, before doing all the calculations is to export the list i already have in app inventor, without adding item one by one.
Or at least try this way.
(my language is not english, so sometimes probably i get lost in translation)
I will check what you sent.
Thank you so much for your help.
Hello Tim,
In the suggested tutorial to solve my doubt - http://puravidaapps.com/spreadsheet.php#source
I'm trying to change the check box to a button, instead of having the post i only have add and delete buttons.
With the add everything is working accordingly with the tutorial. The names are showing in google sheet.
But i really trying to understand in delete, how to change the blocks and script, having a button.
And i'm not getting there.
Hope you can help or i need to create a new topic on the forum ?
Ty
follow the tutorial, try something and if you got stuck, post a sceenshot of your relevant blocks
Taifun
