[Solved] How can I use Google Sheets data in a ForEach loop?

Hi Community,
I am trying to load data from Google Sheet to Firebase , i have more than 250 rows but after loading 20 rows it completed. What could be possible cause.

Does cloud variable has limit of storing data ?? or there could be issue in the table ( I tried removing last lines but it could load only specific amount of data)

1 Like

You are doing it in a very complicated an slow method.

All what I can see from this code is that you want 3 columns to be written to Firebase so first get each column in a separate list variable using the list of values block but once for food then for calories and last for serve.

Now that you have the 3 lists start with a count loop from 1 to length of list.
image
read the lists with the count variable i and save the values to cloud variable as you are doing it now.

1 Like

I’d love to learn more about the move from Sheets to Firebase? What prompted this move?

For a best experience with getting your data into the cloud variable, you should load your data first and then try to push it all via looping.

Thanks Muneer, I wanted Food should be created as nested Object.
For faster retrieval from Food ITem ( Search)
FoodCalorieMeter
|
FoodITem
|_Calories
|_Serving

1 Like

This is exactly what I proposed.

Hi Jared, i was not able to load data directly to firebase , it has 250 rows.
It was easy to load in google sheet and then to firebase, hence tried this method.

also, i found sheets quickly loads to the screen, without much retrieval efforts.

My current objective is to load data into firebase, once user selects item from list viewer, it fetches data from firebase, because sheets is slow.

I may be doing it wrong… trying all ways for faster retrieval.

1 Like

Check this

This is based on the Assumption that the Food column in your table is unique

Thanks for this @muneer , Much Appreciated… i will try this as well.

However, from below, Why does it stops after loading 20 rows, is my concern.

image

1 Like

The get value blocks are asynchronous and would cause a timing mismatch for the loop. The cloud variables in the other hand are synchronous and will force the loop to wait until the operation is completed.

In my code, I’m getting all the values from the Google sheet first then start the loop without having to worry about the timing inside the loop.

1 Like

Probably because of the google sheets imposed api rate limit…

I’m encouraging all to check out xano for these types of uses! Is super fast and easy to get started with!!

Each loop you call the same sheet three times. 3 per loop * 20 loops equals 60 calls within a minute and then your blocked until the next minute.

One more solid example of why google sheets is not the best choice for a backend Database. :slight_smile:

2 Likes

If this is the case then my locks will surely work because it takes only 4 API calls.

However, I understand that Thunkable uses GraphQL so the project limit should apply not the user limit, isn’t it?

Is there a Google project being created at our end when we connect to a Google sheet from Thunkable? That will be a big security hole!!!

Your code is working… Thank You very much …

I’d have to check with our engineers, but from the look of it, it seems to be following the user limit. It’s getting 60 pings from the same ip address at least before quitting. That’s my guess for now.

not sure what you’re asking here. as far as I can tell, we aren’t creating anything in google when just connecting to a sheet.

1 Like