Google Sheets to Dictionary

MIT App Inventor Help
, ,
1

I am working on an app that I use a dictionary to create a "database" of information on tanks. In this example, I have 14 tanks with all of the info about the tanks (height, gallons, etc.)

If this was my only location, I would be done. Trouble is, I have 20 locations with different number of tanks and tank information. Location 2 has only 3 tanks, while location 4 has 22 tanks. Google Sheets lets me enter in the data for the tanks easily in a table format. But right now in the app, if I want to select the location I am at and have the app update, I have to create 20 different dictionaries in the app and then populate from there. Or worst case, create 20 different versions of the app based on location.

Below is my blocks to create the dictionary in the app. I have tried searching multiple places and either I am just not finding a tutorial to help me understand, or it is going over my head.

End game: I would like to have a field where I enter in location (number 1 through 20 manually), and the app goes to my Google Sheets, access the sheet attached to that location (in the workbook, the sheets will be named 1,2,3, etc.) and then populate the dictionary based on the data from the Google Sheets.

I have provided a view link to the google sheet I will be using. Right now, sheet 1 and 2 are the same data, just transposed. I was working to see if I could populate the data either via "read row" or "read column" and was not having any luck with that either.

UPDATED INFORMATION - I used the dictionary because I can then populate individual global variables within the app that I manipulate later on. I also post the information in different sections that are not side by side or in a table format. I need to take the data from the google sheets and populate specific variables. Example. If I select Site 1, Tank 1, I need a variable named "tnkHeight" to be filled with 288, the variable "xmHeight" to be filled with 12, the variable "specGrav" to be filled with 1.0, and so on. These separate variables are used to compute data on the tank later on in the app. The dictionary is just the holding box to select what data I populate the separate variables with.

Google Sheet

Dictioanry
Dictioanry1656×6000 779 KB

2

If it were me, I would just use one big flat workbook in the spreadsheet, with a column to indicate the location of each tank. You can then use gviz to query the data directly from the spreadsheet. (and just forget about using dictionaries...)

1 Like
3

Thank you for that. I was not aware. I will dig into the tutorial you shared deeper.

4

I've read the tutorial more now, and it still does not quite solve all of my issues. In the app, I do some math with the data stored in the table/dictionary. I also post the data on different sections that won't always be side by side. I used the dictionary because I could use the dictionary to populate variables that I could manipulate later on in the app.

That was my fault for not specifying that information in my original question.

5

You can use list blocks to populate variables and do maths

You can actually use gviz queries to do some maths on the data....

6

Look like this?

Screenshot_2022-10-21-20-39-37-60
Screenshot_2022-10-21-20-39-37-60720×1520 99.3 KB

Using gsheet???

On selecting the tank it's respective details to print on labels?

if so you can use script code as given in the site (Credit to @TIMAI2 )

and call the range of cells (fn=readRange) based on selction..

Sample URL

7

I am not sure if I fully understand your question "On selecting the tank it's respective details to print on labels?"

The dictionary acts as a repository of data on the tanks. So all of the tank data is set and loaded into the dictionary. From the dictionary, once I select a tank (see blocks below "Load_Tank_Data"), the information for that one tank is loaded into the needed variables (e.g. from height list, the specific height is loaded into "Tank_Height", and so on).

After that, the next immediate step is to update labels in the app for information to the user (see block below "Load_Display_Data").

To help understand and see where I am going, I have also uploaded all of the steps surrounding this.

Once a user has entered in a tank number and clicked the "Check Level" button, that triggers the block "Chk_Lvl_Btn". Input check is just to make sure the number they put in was valid. In "Chk_Lvl" you see where it calls "Load_Tank_Data". That is included as well. Load_Tank_Data pulls from the dictionary created at the apps initialization. "Act_Calc" was a test portion for proof of concept. That sub routine will be removed.

I cannot say where I learned/figured this method (dictionary) out of doing this. I started working on this app about a year ago and had to step away for a while. At the time, this was just a proof of concept, and now I am coming back to expand the usability.

I am not against scrapping the dictionary method at all and starting over. I think I am able to do what I need using yours and TIMAI2's suggestions and only pull the data for the one tank in question from the Google Docs as needed, instead of trying to populate an entire site at the beginning. I also think I can take the data and bring it into a list and treat the list like an array and populate the variables elsewhere off the list(array).

Tank Selection
Tank Selection1552×1422 312 KB

Load Data
Load Data1596×1742 481 KB

Update Display
Update Display1800×1324 387 KB

8

If possible share a demo aia

9

Sure. Here you go. As a side note, I am always open to suggestions and guidance on how to make these better, but for now, if I can get this one task solved, that will help me a great deal.

GSWP_Calibration_App.aia (721.3 KB)

10

I have not understand fully.. But i feel your app is working correctly//

11

The isolated app is working.

However, when you look at the dictionary created, there are 8 "titles" or "headers" created (e.g. height, xm_height, gallons, cal_span, etc.). Under each header is 14 entries of information. That collection of information is what identifies a tank. So Tank 1 has information of Height = 288, xm_height of 12, gallons of 26,659, and so on.

This dictionary is for one site or location. This one site has 14 tanks. I want to make this app dynamic for my 20 sites. Some sites may have as few as 3 tanks and some sites may have as many as 25 tanks. Additionally, Tank 1 at site 1 may be 288 inches tall, and Tank 1 at site 2 may be 198 inches tall.

The reason for using Google Sheets is so I can easily enter in the data for each tank in an easy to use spreadsheet and then have the app access the data. This will also allow me to make 1 app to work at all 20 sites across the country, instead of having to make an app specific to each site and then maintain 20 different apps across the company.

The app I shared was proof of concept, now I am expanding it to corporate wide.

12

If you really want to hard code the dictionary, there is other way to make it easier with less blocks than you drag so many.

just write your data in a json string and decode it with Web.decodeJsonWithDictionary.

13

So far, in my latest round of testing, this method seems to be working the best. I have used the examples from METRIC RAT using gviz to read the data from my spreadsheet. I am also testing only bringing in the data for the specific tank needed instead of bringing in all of the tanks for that site. I will be doing more testing over the week. Thank you for this link.

1 Like