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

[jemimacoulter]

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.

[Julia]

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.

[Julia]

@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.

[jemimacoulter]

Thanks Julia!

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

Jemima

[Julia]

Glad to hear that I was helpful

[jemimacoulter]

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

[Julia]

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 support@zeroqode.com 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.

[jemimacoulter]

Hi!

I’ve added support@zeroqode.com to the collaborators list in settings. The bubble application is called Koretest2 and the page name is ingredients_search.

Thanks so much,

Jemima

[Julia]

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.

[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

2. 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.

[jemimacoulter]

Hi @Julia,

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

[Julia]

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.

[jemimacoulter]

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

[Julia]

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.

[jemimacoulter]

Hi @Julia!

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

[Julia]

Hi, there!
Apologies for the slow reply due to workload

@jemimacoulter

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

I don’t think so, because in this case, I’m afraid is not quite related to the plugin itself. We have tested your page and our Demo page as well and it works fine on Live Demo, meaning the plugin writes data into the indicated cell in the Google Sheets and retrieves data back. However, for some reason, the data comes from one cell, meaning that A13 kicks off the calculation and gives back the result while the result of cell A14 is not. The crux of the problem lies in bubble logic behind. I may suggest creating a new page and simplify the task and try to figure out where something is missing. For instance, create a new Sheet and make the calculation as simple as possible just to test it out and understand what is going wrong. Verify the formulas in each cell and try them out separately. With a strong desire, unfortunately, I will not be able to give step-by-step instructions as it is related to the way and logic of using the plugin rather than its workability.

Hope this helps!
Best, Julia.

[jemimacoulter]

Hi Julia!

No worries at all! I’ve created a really simple page where you type in an input and the program writes it to a cell and shows it in a text box. I have the same issue! I can write an input to a cell and it shows, but when I write a second input to a cell it still shows the first input. Without the google sheets performing any formulas at all!

It really seems like the API call ‘Get Row values’ can only happen once per page load.

The workflows for each of the inputs are exactly the same as are the dynamic data calls to show what value is in C1.

This is on the page ‘gs_trouble_shoot’ in our program if you wanted to look at the workflows.

I think this is the basic heart of the problem I’m having and I’ve really unpacked and repacked all the workflows and reordered everything and haven’t found any solution for this little example so I’m really at a loss.

What do you think?

Jemima

[Julia]

Hi, @jemimacoulter!

Let me check this out, please. I’ll get back to you asap. Thanks.

Best, Julia.

[Julia]

Hi @jemimacoulter!

Sorry for the slow reply. We have checked your app and the plugin on our side and it has quite weird behaviour in your particular app: for some unknown reasons the API call happen once, indeed. We found a solution, that I hope will resolve your issue. We have updated plugin and fixed problem with getting data from spreadsheet. Please upgrade the plugin to the latest version, refresh your app and give it a try again.

Best, Julia

[jemimacoulter]

Hi @Julia

Fantastic -thanks so much! I will give it a try and let you know,

Jemima