HOW TO: Use a Google Spreadsheet hosted on your Google Drive to update a Map

[SteveJG]

Use a Google Spreadsheet hosted on your Google Drive to 
update a Map  by SteveJG


A developer can provide updated map information onto an OpenStreetMap map tile using an App Inventor Map component 
and a shareable Google Sheet on Google Drive.
              


Skills

App Inventor intermediate to advanced skills
	
Know how to create a shareable Google Spreadsheet and host the spreadsheet in your Google Drive using your computer
What
Use the MIT Map component to create and display run time map markers and simple information about the Marker 
locations.
Use a shareable Google Spreadsheet to store map information you update.  Edit the spreadsheet as necessary 
using your computer to provide current information to the app.
Use the Web component to download the spreadsheet from Google Drive to your app as a csv file.  Convert the csv to a List. 
Use the List to populate coordinate and other information for use on the map.
The example app called “Algerian Pharmacies” displays all the pharmacies listed on a spreadsheet formatted as shown 
below.  The shareable spreadsheet is hosted in Google Drive 



The csv  that is downloaded from Google Drive looks like:

1,date,latitude,longitude,num tel,adresse,name\n
2,03-09-2019,36.379877,6.469655,213555712784,Ibn ziad,ouafek youcef\n
3,08-09-2019,36.392043,6.480409,,Ibn ziad,khaznada leila\n
4,02-09-2019,36.448638,6.640592,,didouche mourad,guerguit\n
5,30-09-2019,36.447781,6.64539,21331906336,didouche mourad,merouche said

Notice, the pharmacy name is in the 7th element. The second element contains the dates the named pharmacies are open late.

Pharmacies open late on specific dates display on the Map in green.  All the other pharmacies listed on the spreadsheet display in red. 
Pressing a pharmacy icon can be used to phone the pharmacy represented by the icon.  There are two ways this can be done 
using the Phone component.  Allow the user to phone the late night pharmacy (or any pharmacy) depending whether the app is 
compiled using the GooglePlay downloaded Companion or the u Companion downloaded from the Designer. The Block image uses 
disabled blocks to show where you can enable the Phone code, depending on what you want to happen.



Custom Map Markers are displayed for the pharmacy locations instead of the colored map pins. The app has the option to change 
the color of custom markers that are svg images. A svg image is provided. Ordinary pharmacies display as red icons, pharmacies 
open late display as green backed icons (provided that there is a pharmacy in the list that is open on today's date and indicated 
in the AlgerianPharmacyLate Google Spreadsheet.)


A bug  in App Inventor nb180 and Companion 2.55 or 2.55u causes an issue when creating an apk.  MIT is working on fixing the 
bug.  Until MIT fixes this, the developer must provide a Design Time Map Marker on the Designer and set the 
design time Marker's Visible property to false.  The invisible marker is needed to allow the Design Time Markers to function 
when the app is compiled. 


A Companion live development app will work fine without placing the invisible Design Time marker.  The compiled app will 
display an error if the 'invisible' design time marker is not present.

What you see is what you get
The app example you create is an advanced mini-tutorial.  The 'tutorial' assumes the developer has at least intermediate App 
Inventor skills and can follow the logic in the Blocks provided.

All the Blocks used are shown above. The createAndPopulateMapMarkers Procedure and the when any Marker.Click event handler contain the critical code Blocks.  
The when any Marker.Click block is found in the Any component drawer. The Procedure and the event handler contain the code 
that identifies which pharmacies are open (on today's date) if one of the dates stored in column B (see below) corresponds 
to today's date.

How to build this app

Create a shareable Google Spreadsheet. Here is an image of the spreadsheet I created (AlgerianPharmacyLate) based on input from a developer.




The columns MUST contain the data as shown in the example spreadsheet in the order of the fields shown.  The spreadsheet must be compatible 
with the List indexes used to filter the data within the app.  For example, column B (also labeled 2 in red where the red value 
is the List index for the dates a pharmacy is open late at night) is the date.  I do not provide a link to my Google Drive.  
You MUST provide a link to a spreadsheet you create. Hard code your spreadsheet Id into the  initialize global SpreadsheetID 
to block. (also see the Edit below) Insert the ID part of the link as shown in the example here....

If you do not provide a spreadsheet ID, the example app defaults to a hard coded csv (assigned to the dummyCSV variable).  Code
is provided so you can test this and if you do not provide a link to a shareable spreadsheet, the app will load the dummyCSV 
in place of spreadsheet data.  The full contents of the Text Block is:

1,date,latitude,longitude,num tel,adresse,name\n
2,03-09-2019,36.379877,6.469655,213555712784,Ibn ziad,ouafek youcef\n
3,08-09-2019,36.392043,6.480409,,Ibn ziad,khaznada leila\n
4,02-09-2019,36.448638,6.640592,,didouche mourad,guerguit\n
5,30-09-2019,36.447781,6.64539,21331906336,didouche mourad,merouche said contained in  



The dummyCSV data will load in the 'else' statement automatically if the spreadsheet ID is not set (Text box left empty). To use the latest spreadsheet data, 
you must create and share a spreadsheet with the updated information.

Edit 3 Nov. 2019:================================
Some developers may also need to change the SpreadsheetSheetGID block (which is now 0) to the GID of their spreadsheet.  The GID on the spreadsheet below is gid=519553060    
shown in the example. The GID of my spreadsheet example is  0  . That information must also be included in addition to the link identification.  If the gid is not 519553060 
for your link,  use what ever value is shown for gid=   on your spreadsheet.



======================================================

A Web component loads the spreadsheet data as a csv file when the app is run. The code that loads the spreadsheet data is in the 
Screen1.Initialize event  handler. You as a developer must provide the spreadsheet on your Google Drive.  You must enter the shareable link in the 
code. The csv file is converted into a List so the data can be manipulated by the app.

Update the spreadsheet (update it using your computer) to change open dates or add additional locations.  
The users of your app will have access to updated information.  

A shareable Google Spreadsheet provides the csv used to create the List used to post information on the map.  You do 
not have to use a Google Sheet; the required csv file can be imported from a CloudDB or FirebaseDB instead.  In those 
cases, update the csv on those databases using an App Inventor app to modify the stored csv.  How to use a database other 
than a Google spreadsheet is not explained.  


...but I don't want to update pharmacies

Adapt the code to do what you want it to do. You do not have to update late night pharmacies.  You are the developer. Use the 
spreadsheet to update locations, add locations etc.

Issues
Some Buttons coded are disabled/not visible in the 'finished' app.  These Buttons were part of the process I used to test  
the app to see what works. Button3 is enabled; all other buttons are enabled but set to Visible = false.  I left the code in
 the invisible Buttons  intact.  The code might be useful for developers to help understand some of the processes involved in developing
 a complex app such as this one.

The example app has some error control in the code Blocks.  You may need to adapt the app to your environment and add additional error
trapping routines. 

Before compiling the apk, add a Design Time Marker and set it's Visible property to false.


“None of the pharmacy icons appear green.”  That happens when today's date does not correspond to one of the 
date entries on the spreadsheet.  To get a different behavior or want to provide a list of pharmacies and the dates 
they are open, you can do that.  Code a Label to 'print' that information if you need that feature.  What you build 
using this tutorial will be your app.

If you compile your version of the app using a Companion with a 'u' suffix, you will not be able to distribute the
app using Google Play; use a Companion without a suffix. The Companion 'u' is needed to make 'automatic' phone calls.

You must provide a shareable link to a spreadsheet.  Make sure your spreadsheet columns mimic the spreadsheet I used to
create the app.



Aia File

Use the AlgerianPharmacies.aia to capture the required Blocks.  Load the example Project.  You, however, must provide the 
spreadsheet and link and enter the link into the existing code as indicated above.  If you do not provide a link the aia 
will load a 'dummy' csv.  It will not load the required csv using the Web component.

Finally
Please do not take this app aia and publish it as your own.  Use the techniques demonstrated in the example code to develop 
your own map app.

Thank you.


Regards,
Steve

[TimAI2]

:) 

[Samira kh]

Bonjour mr steeve

lorsque je mets le lien partageable

lorsque je clique sur bouton1 il m'affiche  pas de connexion de données

[Samira kh]

mr steve regarde j'ai essayé votre application

[SteveJG]

This change fixes the problem Samira with your spreadsheet link. 

Change the SpreadsheetSheetGID (which is now 0) to possibly 519553060 .  Why?  Your full shareable link looks like this     ....... 5w/edit#gid=519553060     and my example ends in 0  .  For some reason, that information must also be included in addition to the link identification.  If gid is not 519553060 for your link,  use what ever value is shown for gid=   .

Does your spreadsheet work properly now after editing the Block?  Is the app working now?

-- Steve

[ABG]

(added to Maps section of FAQ)

https://sites.google.com/view/ai2-faq-view/home

ABG

[Samira kh]

Bonjour Mr Steve

merci ça marche

cette technique je peux l'utiliser sur l'application l’hôpital le plus proche travailler directement avec Google Sheet

[Samira kh]

MERCI Mr ABG

[Samira kh]

Mr Steve

le park international

ce bloc il faut le changer

pour travailler directement avec Google Sheet

j'ai mis ça

mais il me donne des résultats faux

help me

[SteveJG]

I am glad you got the Algerian Pharmacy tutorial working Samira.

résultats faux????   What does this mean?  You did not explain what happens.    I imagine you get bad results because:

1)You try to use the  csv created by the code for Algerian Pharmacies with the National Parks code without make adjustments for the order the csv is created and the number of elements in each. See #2.

2) You  use the Algerian Pharmacies spreadsheet to create the csv file for the National Parks tutorial.  The Algerian Pharmacies creates a csv  with this format:     1, date,longitude,longitude,num tel,adresse,name  

The Spreadsheet uses  seven (7) columns and creates a csv with seven elements.  The National Parks  csv  is hard code in the form   Shenandoah N.P.,1111111111,35.7647,-82.2653\nYosemite N.P.,1111111111,37.8651,-119.5383 etc. The format is  Name,phone,latitude,longitude. There are only four (4) elements.  The National Parks does not have an ID or a date or an adresse.

3)  The National Parks tutorial stores its Map pin data (csv) internally. There is no spreadsheet.  The Algerian Pharmacies uses a Google Sheet to create a csv.  You want to use your Algerian Pharmacies spreadsheet with National Parks.  That can be done but it means you have to modify the code a lot to create a csv in a form the app can use.

The index for latitude is 3; the index for longitude is 4 and name is 1 in National Parks.

The index for latitude is still 3 and longitude is still 4 in Algerian Pharmacies but name is 7.    

 A partial fix might be to change 1 to 7 as shown below:

I don't know how many other issues need to be adapted. You have to debug your code.

In Algerian Pharmacies the for each number from blocks starts at 2, not 1 (because I do not want the sorting  to use the ID value (the Block does this by starting at 2, not at 1). I guess this will have to start at 2 in your new code.

4) When you adapt the code, make sure you use the appropriate value in the SpreadsheetSheetGID block  if you use that Block.  It might be 0 as your image shows, it might be a different value depending on your spreadsheet.

I hope the information above observations will help you adapt either Algerian Pharmacies or National Parks to work with the identical data set.   Sorry, I cannot do that for you.

--Steve

[Samira kh]

Bonjour mr Steve

je parle pas de pharmacie

je parle de votre park international vous avez travailler par scv dans la base

mais je veux comment faire directement avec Google Sheet si j'ajoute des informations  sur Google Sheet il fait la mise à jour directement sur l'application sans refaire le fichier csv

[SteveJG]

From the discussion of National Parks in this Forum https://groups.google.com/forum/#!msg/mitappinventortest/hOJQJuOF-cs/gRuECYvcFQAJ I understood you previously converted the example to use a spreadsheet.  Is that not true?  Why don't you use what you already coded?

You cannot use the Spreadsheet you created for Pharmacies directly.  The Pharmacies spreadsheet has seven columns.  The spreadsheet needed for National Parks should have four columns.  You could write code in the National Parks tutorial to use the Pharmacies spreadsheet but that requires extensive manipulation of either the existing tutorial Blocks or how you import the Pharmacies spreadsheet to use only four of the seven spreadsheet columns with the existing code.  Alternatively, you could extensively modify the National Parks Blocks.  Either path is a big headache.

Modifying code  to accommodate your Pharmacy spreadsheet is difficult.  Easier is to make small modifications to the original National Parks tutorial and create a GoogleSpreadsheet from the Destinations.csv,  compatible with the National Parks format. I recommend you make a copy of the tutorial aia when you make the modifications. The Destinations.csv is included in Media in the National Parks aia file (NationalParks_2.aia). 

Create a new GoogleSpreadsheet from the Destinations.csv

1)  Bring up a blank GoogleSpreadsheet on your Google Drive.

2)  From the spreadsheet, select File > Import.  Select Upload then drag the Destinations.csv into the pop up that appears.

3)  Create a Shareable link to your new spreadsheet.

The spreadsheet created from the Destinations.csv does not have any headers (see below if your spreadsheet uses headers). 

 

Modify the existing National Parks tutorial to use a Spreadsheet to control locations

Delete the Blocks from the National Parks tutorial showing an X in the following image.

Add a second Web component (Web2) to the National Parks Designer.

Add the following Blocks to the Project

1) Create the downloadLatestSpreadsheetLocations Procedure

2) in the existing Screen1.Initialize add   call downloadLatestSpreadsheetLocations

3) Create the Initialize global SpreadsheetID and set the Text block to your spreadsheet ID

4) Create the initialize global SpreadsheetSheetGID and set the Text block to your spreadsheet GID

Delete the Destinations.csv from Media. It is no longer needed.

Delete the File1 component from the app. It is no longer needed.

Test the new Project. Run the app using Companion and then create the apk and test it to make sure the compiled app works. Once you get the app working with this spreadsheet, substitute the Destinations spreadsheet with the locations you really want to use in the app.  Remember, the spreadsheet must be four columns without a header. If you have a header (  name,latitude,longitude,phonenumber  ) with your data you can use that too. If you have headers you must, add three Blocks to the existing  displayLocations Procedure as shown below.  

The Blocks strips the headers from your spreadsheet file.  The headers are not needed in your app. If you do not provide headers in the spreadsheet, the Block is not needed.

That is all there is to it.  It took 10 minutes to make the modifications;  it will not take you long to make the changes. 

mais je veux comment faire directement avec Google Sheet si j'ajoute des informations  sur Google Sheet il fait la mise à jour directement sur l'application sans refaire le fichier csv

The modification suggestion loads the spreadsheet when the apps starts.  If the spreadsheet is updated using your computer while the app is running, the locations will not update until the app is restarted.  If you have the necessary coding skills,  write code that continuously updates using a Clock to update the contents fullDestinationCSV variable from the Web2 (perhaps call the downloadLatestSpreadsheetLocations Procedure).  I don't understand why you want to do that. Continually updating would not update the Destinations.csv.  You removed it if you followed the instructions above and it is unnecessary.  The revised code  only uses the Spreadsheet which the app converts to a csv because the only way to use the spreadsheet data is as a csv.  If you want to update 'instantaneously', you might consider building your app to use the CloudDB that updates in real time instead of a spreadsheet.

Did this modification work for you?

[Samira kh]

Bonjour Mr Steve

fulldestinationcsv je le met en empty c ça

[SteveJG]

 I do not understand what you said.

To get help, you must explain in more detail please.

 je le met en empty c ça  --- what did you put in the empty fullDestinationCSV variable?

Désolé, je comprends très peu le français. Google Translate n'aide pas.

عذرا ، أنا أفهم الفرنسية قليلا جدا.

ترجمة جوجل لا يساعد.

[Samira kh]

Bonjour Mr Steve

le code ne fonction pas il me donne l’hôpital le plus proche incorrect

et voici le lien de Google Sheet

https://drive.google.com/open?id=1qh0UU8Ipt5-aiW2PphM1dHt_lKYlirUJ9KLvWNcggCE

[SteveJG]

This thread is intended for questions about the Pharmacy tutorial.  You are asking how to modify the National Park tutorial.

I locked this discussion to help others avoid confusion about the two Projects.   Please start a new Forum discussion Samira.  

Did you show ALL your app Blocks for this modified Project?  You need to load the spreadsheet.   The blocks you show you tried never load the spreadsheet.  

Where are your blocks that load the spreadsheet ?  There is no Web2 block in your block image required to down load the spreadsheet; there is no Screen1.Initialize block to tell the app to load the latest spreadsheet.

The additional blocks you need should look something like this:   

Your blocks will be a little different depending on modifications you made to the original aia.

There could be other issues.   Sorry, I do not have the time right now to debug your app.