Question about "CRUD with Google Sheets, Web App and AI2"

Hi Everybody,

Especially TIMAI2 for your response.
I am a newbie, I have some difficulties to understand but I have to finish an app with AppInventor.
The goal of this project is to provide to an athlete, a session of sport thanks to some information about him.
The model and calculation are even done but on Excel. So, for more impact we need an application.
My predecessors have been started this project and I have to finish it.
Here is their work:

As you can see there is a calculation part but it’s not as accurate as the excel file.

So, I’d like to change it and based my work on those in the link:
Thank to this process I can send information on a google sheet (which correspond to the excel file) get in back the sport session. I can also get more than one athlete on the app, which can be useful for a coach.

My problem is that I don’t where I can add column?
I’d like to see something like this on the Google sheet:

And then these numbers which correspond to time record, will be used in another sheet for the calculation.

I hope I was clear and forgive me for my English.
Hope to see you soon for your help.


1 Like

Your problem looks interesting.
Unfortunately, a screenshot of your Excel file does not reveal the formulas behind the cells.
Could you explain what that formula for epiResult means, and where it comes from?
I apologize for my lack of French.

1 Like


I would first pursue improving your app calculations (not sure why they are not as accurate, or not accurate enough as excel/google sheets?). @ABG is the man for that job !

My “CRUD” example is dynamic, so it will work with any number of rows and columns. I am guessing you want to upload the base data to the spreadsheet, then perform a calculation using that data, then add the result to another column, before calling back the results to the app.

You will probably want to run another script to do this (the calculation), tacked on to the end of the create record section. You may also need a blank field…

But first, explore what you can do with just the app…

1 Like

The screenshot of my Googlesheet file is an example of what i’d like which is based on the link of Metricrat’s blog.
Well this formula is an adaptation of a study which can make a matabolism profil of athlete thanks to time record. So, thanks to many physiological factors we’d like predict the capacity of power of metabolism.
The calculation will provide a set of race where it will show the speed at a certain time.

Here the formula say : if the time of course doesn’t excess 4xconstant_time so you run at this constant speed. But when it excess this time, until you reach the final time, you decrease you and this decrease will be exponential.

1 Like

ok so think I should provide the excel file
With your CRUD I wanted creat, upload the base data on the spreadsheet. Then these data will be used on another sheet of the same spreadsheet for calculation. Then take the result thanks to a Ai2 block (I saw something like this).

1 Like

Here some screenshot of the app when it runs :

It shows the time and the power output (in my case it will be the speed).

1 Like

yes, please provide the excel file.

1 Like

Here is the link to my Spreadsheet

Tell me if something wrong

1 Like

Nothing makes my afternoon more interesting than an 8 sheet
spreadsheet doing integral calculus in French, with almost 40 named ranges,
with numbers in the French convention (commas instead of decimal points),
annotated in French.

Let me start by summarizing what I see here.

You have a data entry sheet, where you enter elapsed time in seconds for passage of irregularly spaced marks in a race, one race per row, one time reading per cell, ascending as the racer progresses along the track. The marks are columns in your data entry sheet.

You calculate and graph various physiological reserves by time, per race.

Looking at your blocks, I see some things missing …

  • You do not use internal storage like TinyDB to hold multiple races and racers.
  • You do not use the new dictionary data type to store complex information about racers and their possible multiple races, like racer name and race date/time, and maybe ambient temperature(?).
  • You do not use procedure parameters and value procedures to allow reuse of your computations against different races and to simplify calculation steps by name.
  • You rely too much on global variables where value procedures could be used. This kills reuse.

It’s possible to do this all in blocks, without Internet access to Google Sheets, if you want. Considering races are run outside away from WiFi, this can be helpful.

Be aware that AI2 needs decimal points instead of commas for its math blocks.

See this thread for how another app was converted from Excel to blocks, albeit too slow for robot arm control but probably okay for race time analysis … How to make a link between an excel file and AppInventor

You named ranges would be good candidates for conversion to value procedures.

Study materials …

Now I must take a puissance break.

1 Like

Thank you very much for your time !
I’m sorry for all this stuff and I understand that it’s not easy to understand especially in french

I’m happy to hear that is possible to make all this stuff in one app.
I’ve even worked to use the tinyDB to the storage of race.
Now I will consider your advice ans try to make it possible.

If you’re curious about these calculations, they are based on this article :

I wish you a good rest and thank you again.

1 Like

2 posts were merged into an existing topic: Spread Sheet - error csv