Quizz Refresh

Philippe_Krait · 01-13-2021 08:05 AM

To try out AppSheet, I have created a simple quiz for my partner, based on a table in Google Sheets, it creates a list of questions in another table, and the App should just load the new questions when refreshed.

To allow the questions to refresh, they are based on random formulas in the Google Sheets, and I have turned on automatic refresh. Looking at the sheet, it happens as scheduled.

But the problem is that, apparently, it does not refresh the saved version of the sheet, which mean that when I try and sync the app, it always loads the same series of question.

I have looked a bit around to find either a proper solution or a workaround, but I must admit I’m stumped.

Can anyone offer any suggestion ? Thanks in advance…

LeventK

I’m afraid we couldn’t be able to follow what you’re asking really. Might be beneficial starting from here:

Philippe_Krait

I have a Google Sheet file with two sheets.

The first sheet has a table that gives me a list of countries and their characteristics.

The second sheet has a table that randomly points to the first sheet’s table, so that whenever the second sheet is re-computed, a different list of countries appear in it.

So that this second sheet gets recomputed at frequent intervals, I have turned the recalculation option on at every change and every minute. And it works, every minute, the table on the second sheet contains a different list of countries.

Now, I have created an App under AppSheet, which looks at the second sheet above to create a quiz.

The problem is that, when I refresh my App, I would expect it to go and have a look at the current state of the second sheet, and also therefore refresh the list of the countries there.

But it does not happen. Even though I see the table refresh every minute on the Google Sheet, whenever I refresh my app, I get the same list of countries, not the one displayed on the sheet.

How do I solve this ? I must point out that I have read everything in the guide, and done quite a bit of searching, but I have not found out anything that refers to that specific problem.

LeventK

How data is gethered to this 2nd sheet from the 1st table? QUERY? IMPORTSHEET? IMPORTXML? How?

What’s this re-computation? Because of any sheet formula?

When you have imported this 2nd sheet as a table to your app, have you explicitly checked each column in the table provided does it contain a sheetformula or not in the column structure? Secondly have you imported this table as a read-only table?

That’s somehow related with how AppSheet reads the base gSheet. I believe there’s no live connection between the gSheet and AppSheet and if I’m not wrong the back-end sync occurs every 30 mins automatically and it cannot be changed. However though, if your data is changed in the sheet and you sync the app, it should update app data in the front-end.

Philippe_Krait

Thanks for the answers, see below for further details.

Just simple references to the first sheet, with an Index() Function
The recomputation is because the Index() above uses a random offset. When the sheet is recalculated, the offset changes and therefore the Index() returns a different line from the first sheet.
I explicitly use the columns from the sheet in the App, there are no formulas. And yes, it’s read only.
I think I understand what you are saying, but isn’t it bizarre not to have a live connection with the gSheet ? I mean, I’m not updating it through the app, but I could do that, right ?

I think the problem is that although the data changes on the sheet through the recalculation, it’s not actually saved. When I reload the sheet, though, it says “saving” briefly, and then when I reload the app, the data has refreshed there correctly.

The problem is that although I can do automatic recalculation, I have not found in gSheet any mean to automatically reload/save.

So, in the end, it might simply be that I’m not using the right mechanism there, but I’m not sure what other mechanism I can use.

I’m trying to read all the references that you sent, and thanks for these, but I think I’m a bit in over my head…

Philippe_Krait

Thinking about it, my app is really not that secret, is it done around here to share the link so you can see the intent ?

LeventK

@Philippe_Krait
You can share the gSheet to levent@able3ventures.com with edit access so that I can take a look. You can also add the same email to your app as a co-author (can edit definition) so that I can also look at your app setup as well.