Thunkable doesn't function well with the google spreadsheet data source

Dear Thunkers
I am currently working on an app that communicates with my google spreadsheet but it doesn’t work properly, is this a known bug?
Everytime I save something to the data source my screen goes blank and the data has been saved successfully… I don’t quite get that :smile:. Also there is another bug, when I saved something to the data source and wish to edit it, the new data doesn’t update so it remains the same data before the edit.
I hope someone has come across these issues too and found a way to go around them.
Thanks in advance for all your help.
Jens

Stop calling things bugs when it’s probably your own incorrect coding that’s responsible for the problem.

Possibly, @Dean_Artis, but the Google Sheets integration is actually very buggy. Here’s what I’ve noticed so far:

  • The data sources sometimes don’t work even when the blocks that get data are unchanged. The only fix I’ve found is to add a new source for the same sheet.
  • When you add a new data source and then select it from the update spreadsheet block, the fields all get set back to some default so you have to go through your code again and restore the fields to whatever they should have been (if you remember).
  • If you update a cell in a sheet, it breaks all of the formulas in the row containing that cell. It removes the formulas and changes the cell to plain text.
  • Thunkable Live does not use the newest Google Sheet data unless you force-quit the app and re-open it.

I have about 40% faith that Google Sheets integration will work when I use it and I’ve spent dozens of hours working with it. Yes, some things can be due to user error but… when something works and then you don’t change any blocks and suddenly it doesn’t work, well, that’s called a bug.

I think a custom MySQL database is the best option for most users tbh. I’d love to do a tutorial video for how to set this up for free with thunkable. That’s what I did for Aba-glossary.bubbleapps.io

All the terms are in a MongoDB using an auto generated api on a heroku free server.

All free. Holds all my stuff. Fast. Easy to query.

I’ll be making a video soon!

2 Likes

@jens12341234 There is a bug right now that can cause crashes in some scenarios. If you have a data source linked to a data viewer on a screen and you make changes to the data in that data source (update values, create new row, delete a row) while the screen with the data viewer is not visible there might be issues. We are working on a fix and hope to have it released very soon. For your second point, can you clarify what you mean? If you attempt to update the data via your app you don’t see the changes reflected on your sheet? If so, can you share you blocks so I can take a look and see what might be going on. It is possible that we have an issue on our end but I haven’t heard of this specifically before.

@tatiang Thanks for describing your issues. I will be happy to take a look and see if we can get these problems fixed. I’ll try to mimic your formatting so it is obvious which issue I’m referring to with my questions:

  • Is there any consistency around how the blocks stop working? It seems very odd to me that unchanged blocks would stop working but would work if you add a new data source. Almost like our authorization token for your Google Sheets data is expired but gets refreshed when you add a new sheet. After you add a new one, does the previous source start working again?
  • Can you provide a little more context here? I assume you mean the block to update a value? What fields are you referring to?
  • Are you saying that updating a value in a single cell will cause cells in the same row to convert their formulas to text? If so, that is a huge unintended issue that I was not yet aware of. I can look into fixing that as formulas are one of the main things that enable more advanced usage so we obviously don’t want to be overwriting them.
  • We do cache the data to prevent unnecessary network usage but you should be able to get the fresh data by either using the refresh block on the data viewer or pulling to refresh. If that doesn’t work for you please let me know.

Thanks for voicing the issues you’ve seen. We can’t fix things that we aren’t aware of so it is always helpful to hear what problems our users are encountering. Sorry that you’ve had a bad experience; we’ll be working to make improvements as quickly as we can.

@Dean_Artis While this may be true sometimes, it isn’t the most productive comment. If you suspect that someone’s blocks might be incorrect it usually is most helpful to ask to see them and determine the response based on what you find.

@jared That is awesome and would be a great tutorial to have!

2 Likes

@Steven, I appreciate your response. And I totally understand that you need to hear about these things and have specific details/examples in order to be able to fix them.

For this one…

…if I have a Google Sheet called “Lat Long Pairs” and I add it as a data source and then create an update value block like this:

…but the data source fails and I have to re-add it – which means giving it a unique name such as “Lat Long Pairs 2”, then when I look at the update value block, the “in” (column) value is set to some default… maybe the first column? It doesn’t remember and try to re-attach to the column I had selected in that block. I can imagine from a programming stand point that’s not a simple fix but from my perspective, if I have four or five update value blocks in my project, they all get broken in this way when I have to essentially refresh the data source. It seems like there should be a way to replace a data source with the same sheet rather than adding the exact same sheet but having to give it a unique name.

Yes, that’s exactly what I’m experiencing. I’ll try to make a sample project that shows this. The project I’m working on right now has 2,000+ blocks and it would take me longer to explain everything in that one than it would to make a sample project for you.

I’m curious if it’s restricted to the particular Google Sheet I’m using so it’ll be a good test to set it up with a new Google Sheet and see if I can re-produce it. I may have also been mistakenly trying to create a row… I wasn’t so sure how the update value block worked when I started out: Sample Google Sheet project?.

I see. What you’re asking about in the update block is not technically impossible though, as you mention it would be difficult, and it doesn’t really address the underlying problem. Unless I’m misunderstanding, the real issue here is that you need to re-add a data source that contains the same data. So just to confirm, are you experiencing instances where you have a data source in a project and it is working as expected but then, through no changes that you’re aware of, it stops working entirely and you need to replace the data source with a newly created one from the same actual source (whether that is a Google Sheet or Airtable base) in order to get things working again? If so, if you get into this state again would you please let me know so I can investigate before you add the new data source? Can you also attempt to view the data in the designer to see if there is some issue on our end with fetching the data entirely (you can do so either by clicking on the Data Source in the Data Sources section of the project tree or if you’re using a Data Viewer by clicking on the click here to view data button beneath the Selected Table dropdown)? This would give me a better idea as to whether it is related to your auth token. When the Data Source is replaced does it work without any further changes to the blocks (apart from selecting the new source/table/field as necessary)? Any additional information would be very helpful in diagnosing the problem.

Thanks for confirming and providing an example. I believe we have identified the issue and will have a fix out shortly.

I appreciate you following up. This was a pretty big undertaking on our end and there are tons of potential issues with Google Sheets because of how open ended it is. I’m sure there are some additional issues that are not yet discovered but we will do our best to address them as soon as we discover them. Hopefully we can bump up your confidence level and the consistency of the integration. If you have any other issues please let us know!

@Steven

Yes, I’d be happy to do that.

I’m not using Data Viewers at all. When I look at the sheets from the Data Source area, the sheets look correct as far as column names and basic row data. I can only see 5 rows even though some of those sheets actually have hundreds or thousands of rows of data.

Yes, the replacement data source (sheet) works fine after I reset the field names in the update value block.

And hey, I feel a little more confident now just seeing your response. :slight_smile:

3 Likes

I believe your third issue where updating a value in a cell causes formulas to stop working should be fixed now. It should not require any changes to your projects. Take a look and let me know, please.

@jens12341234 Your original issue should also be fixed now. Please update your Thunkable Live app (should be version 222) and let me know if you’re still seeing any crashes.

1 Like