Templates Plugins Courses

Google Sheet & Charts - Program not retrieving latest data from the spreadsheet

Hi!

I’ve been working on a program that requires the Google Sheets & Charts plug-in to go in and edit cell values on a spreadsheet and then take the outcomes of those cell changes (ie the spreadsheet is doing some calculations that would be too complicated for Bubble to do) and shows this data in a repeating group.

Specifically, this is about recipe searching, so you type in a list of ingredients into the bubble program, the Google Sheets API then inputs these ingredients into the google sheet, then the google sheets works out which recipes contain these ingredients and those recipes appear in a cell, and (hopefully) this information can be accessed from the bubble program.

I’m doing this by states: setting a ‘Get Files’ page state to ‘Google Sheets- Get Files (Drive)’ with path/hash current date/time UNIX and a ‘Search Row’ page state to 'Google Sheets- Get Sheet Values (Row)‘s values’.

This all works fine the first time upon loading the program. I can search for an ingredient and it comes up with the repeating group showing all the recipes that contain that ingredient. However, when I clear my original search (and I’m not refreshing the page here) and search again for a different ingredient, the recipes don’t update.

The weird thing is that I can look at the Google Sheet that my program is supposed to have inputted this new ingredient into and can see that the Google Sheet has updated to include this change- so that part of the program has worked.
It seems then that the bubble program is not retrieving data from the updated google sheet, but the one for the previous search. It seems to be able to update the sheet once, but not twice?

My workflow upon pushing the ‘search’ button looks something like…

Set a ‘Get Files’ page state to ‘Google Sheets - Get Files (Drive)’ with path/hash being current date/time UNIX

->

Google Sheets - Write to Cell (writes the ingredient you’re searching for into the appropriate cell in the google sheet)

->

Set the ‘Get Files’ page state to ‘Google Sheets - Get Files (Drive)’ with path/hash being new current date/time UNIX

->

Set a ‘Search Row’ page state to 'Google Sheets- Get Sheet Values (Row)‘s values’ with the same spreadsheet id.

My repeating group that shows the recipe then retrieves data via Type of Content: Get Sheet Values (Row) and Data Source: page’s ‘Search Row’ state - see workflow above. So that should update with the Search Row state.

I can see in the step-by-step debugger that the 'Google Sheets- Get Sheet Values (Row)‘s values’ hasn’t updated to the new ingredient. Even though I can see that the cell in the google sheet has changed upon loading the file in a separate page of the program.

My thinking is that it’s something to do with the ‘Get Files’ page state needing to somehow refresh or reset.

Any input would be welcome!

Also if anyone can recommend any learning materials for this particular Plug-In that would also be really helpful!

Thanks.

Hi, @jemimacoulter!

Thanks for reaching out!

There is a bubble particularity that impacts the API calls. Whenever you frequently make an API call and there is no hash indicated, the bubble doesn’t seem to recognize the call. Let me check this out with the dev team and I’ll get back to you asap.
Best, Julia.

1 Like

@jemimacoulter, we have investigated your case and it turns out that the issue is not related to the “hash”. On our side, the plugin works correctly even after the frequent times of API calls. Anyway, please follow the step that might help:

  1. Try to delete the plugin actions in the workflow and set them up again (sometimes it can help)

  2. If point 1 doesn’t work, try to uninstall the plugin and give it another try

  3. Please check if you use the latest plugin version 1.10

  4. Have a look at our Live Demo in order to verify the settings of used actions: https://zeroqode-demo-16.bubbleapps.io/google_sheets_charts_service

  5. You don’t need this step. You have to use it once, when you need to get the Spreadsheet ID.

  6. Make sure if do really use the same spreadsheet id.

Hope this helps!
Best, Julia.

Thanks Julia!

This is really helpful- I’ll give these steps a go:)

Jemima

2 Likes

Glad to hear that I was helpful :slightly_smiling_face:

Hi Julia!

Unfortunately none of those steps have worked! I thought it might be helpful to include some pictures of the step-by-step debugger.

^ This is the first search on page load. Searching for recipes that include the ingredient Honey.

^ Can see that Honey is written to cell A13 in the google sheet with spreadsheet ID 1kU…RmQ

^And after this step the API goes into this googlesheet of the same ID and finds recipes

^Recipes are shown here and I’ve started to search for a different ingredient Kale now


^So going through the same thing as before. The API now writes Kale into the cell A13 in the same spreadsheet. And the API retrieves the row values of this same spreadsheet.

^But you can see here that on Row 13 the first item of that row is still Honey! And so the recipe results are still those for the recipes containing honey. Super weird! Especially as I can go into another page, upload the google sheet and see that in cell A13 it says Kale. So the API is changing the value of the cell, it’s just not retrieving the latest version of the spreadsheet.

I did think that it could be something to do with the repeating group that shows the recipes not updating, but the data comes from the Google Sheets Search Row Values state, so should just change when that updates.

Hopefully this makes it a little clearer and we can work out what’s going on,

Thanks!

Jemima

Hi, @jemimacoulter!
None of the proposed hints didn’t work? That’s weird indeed because we cannot replicate this on our side. Could you , please, add our [email protected] account to your collaborator’s list in Settings -> Collaboration so we could check the issue inside your application. Plus, please tell us the name of your Bubble application and page name where you develop it, it would be way easier for us to check this out.
Note: avoid the warning message regarding Professional plan, as we are an agency and you can easily add us to your collaborators.

Looking forward to hearing from you!
Best, Julia.

Hi!

I’ve added [email protected] to the collaborators list in settings. The bubble application is called Koretest2 and the page name is ingredients_search.

Thanks so much,

Jemima

Hi, @jemimacoulter!

Thanks for the provided info! We’ll check this out and get back to you asap. But please be patient, it might take time. Thanks for understanding!

Best, Julia.

Hi, @jemimacoulter!

So, after investigation what we’ve found out is that the Google Sheets always returns the same range of data from A1:A233 and the cell value successfully has been changed. Please see screenshots as a reference.

  1. Do a search for honey

  1. Do a search for Kale

In this case, please make sure that inside the Google Sheets the countings are done correctly because the plugin returns the Google Sheets value which is indicated in this range: A1:A233

Best, Julia.

Hi @julia.minacova,

I’m not sure I quite understand what you mean about the data range? I still can’t get the program to work so that it returns the right data.

The program works if you reload the page in between ingredients searches, but I want it to work multiple times in a row without having to reload the page. Hopefully that makes more sense?

Thanks for taking a look though!

Jemima

Hi, @jemimacoulter!

Yes, I do understand what is talked about and we have tested the plugin the way you’ve described it. The only difference is that our Google Sheets don’t have specific calculations. Anyway, while testing your page we have noticed that we receive the same data which is in a range of A1:A233 and which doesn’t depend on what we are looking for, meaning when we do a search for Honey we get the data for honey, and when we do a search for Kale - we get the same data. Therefore could you please check if all the calculations on the Google Sheets side are done correctly. Also, if it is possible to set a little range of the result to clearly see what we get. There are too muсh test rows or empty ones.

Best, Julia.

Hi Julia!

I’ve checked the google sheets and it all works fine as a spreadsheet. I’ve also changed the result range to just the cells we’re interested to make it a bit clearer.

On the program there’s a little table underneath the main results repeating group that shows the search row state- ie shows the google sheet section we’re interested in.

Hope that helps,

Best,

Jemima

Hi, @jemimacoulter!

Ok, thanks for the details, I’ll take a look at it and get back with an update soon. Please bear with us, while we are checking. Thanks for understanding!

Best, Julia.

Hi @julia.minacova!

Just checking in… any updates on this?

Also, do you think using the other Google Sheets Plug-in (Google Sheets + Offline) from zeroqode would make a difference?

Thanks for your help,

Jemima