Retrieve location specific Phone Numbers (or other data) from an appropriate Google Spreadsheet

The LocationSensor’s CurrentAddress can be used to control other event’s in your app. CurrentAddress
This is an ADVANCED developer tutorial. It shows a way to use the tool to decide what phone numbers can be used in an emergency phone number app based on information in various spreadsheets. The CurrentAddress is used to determine where the phone is located (for example, the city the Android thinks it is in). HOW TO: Parse a LocationSensor's Current Address by SteveJG shows what the CurrentAddress might contain. However the example contains all the required code. The tutorial assumes you know how to create a Google Spreadsheet and host it in your Google Drive.

Modify the example to display images appropriate to a location or other information in your app by modifying the code if you don’t need phone numbers. You can use the CurrentAddress to determine what your app should do or display based on where the phone is located; just adapt the provided example.

Above is an image of the example screen on the Designer. It is used to display the contents of the spreadsheet as a csv file in a Label. When you build the app, do not include this feature. The csv is displayed to test that part of your download from the spreadsheet is working.

Below is the same screen working in the app.

Select a Google Spreadsheet (from among many stored on a Google Drive) automatically. This is an example spreadsheet

Use information about where the Android is located to select a spreadsheet. The example uses the city ‘field’ in a LocationSensor.CurrentAddress block. Either a WIFI or network connection is needed to access the spreadsheet and the Google Map database. If the Android is not able to access the Web, it still could work using your GPS if you provide ‘default’ information stored in a TinyDB and code to determine if there is no connection and logic blocks to tell the app what to do in that case.

The example shows a basic approach. Code it. Provide a spreadsheet on your Google Drive and ‘share’ it. Then code the spreadsheet ID into the appropriate Blocks in the code provide in the aia. Change the name of the example city (presently Constantine) to the city you want to associate with the spreadsheet. When the app correctly identifies the appropriate spreadsheet and the app works the way you want, embellish the app with additional features.

1). Create a Googlespreadsheet on your GoogleDrive. Use your computer to create the sheet. Create a separate spreadsheet for each ‘city’.


2). Allow the sheet to be Shared with Everyone! Make note of the SpreadsheetID; you need the ID to link the spreadsheet to your app.


3). A LocationSensor and your GPS receiver provide a CurrentAddress. Determining an address requires a wait of generally about 30 seconds or more dependent on how long it takes the GPS receiver ti achieve a satellite fix. A routine prevents the main part of the app from starting until a satellite fix is achieved. As coded, the user must press a button several times until there is a response to a LocationChanged event. The manual method could be automated. Use a Clock to determine if the LocationChanged event has been triggered and when that happens; disable the Clock (Clock.Enabled to false).

4). Determine the ‘CITY’ where the Android is located. The spreadsheet in this example is selected based on the city where the Android is presently. Check the contents of CurrentAddress. When a GPS fix is achieved either the message ‘not found’ or the city is displayed and then loads the appropriate spreadsheet using a Web component.

5). The code selects one of several GoogleSheets. You want the spreadsheet associated with the city. Use If…then…else statements. If there is no appropriate sheet for the city; the app lets you know. What do you do then? It is up to you.

6). A Web component downloads the information stored in the appropriate spreadsheet to your app in the form of a csv file. You must enter your spreadsheet ID information into SpreadsheetID1 and SpreadsheetID2 variables.

7). A Web.GotText block confirms the download (response code 200). The thePhoneList is created from the csv (theCSV).

The thePhoneList List contains the information available for each city in a spreadsheet. The List has the phone numbers used in each city. The example uses a separate spreadsheet for each city.

Here is the code that shows how the app selects the appropriate places to phone and where the required numbers from the spreadsheet come from. See point #6

A single spreadsheet could be used to select the phone information instead of multiple spreadsheets. Add a third column to the spreadsheet containing the city name to the spreadsheet. Use an sql like query to filter the spreadsheet and select only the correct information for the city. This example does not show how to use a single spreadsheet.

  1. A Button event makes a phone call to each location (select appropriate information from the List (thePhoneList) ). One button is for each phone. You might use a ListPicker to initiate the phone calls instead of several Buttons. The PhoneCall1.MakePhoneCall block is disabled. You need to compile using a Companion with a ‘u’ suffix to make automatic calls. Enable the block, otherwise just display the message shown in Label5 while you are testing.

This coding example avoids advanced coding techniques (on purpose). Get the simple example working; then make it ‘pretty.’


This app requires your provided shareable link spreadsheet ID for your spreadsheet to work. Place your spreadsheet ID information into the variable SpreadsheetID1 AND into SpreadsheetID2 (you can use the same spreadsheet in both variables for initial testing.

When a single spreadsheet works, add additional spreadsheet ID variables and links). The app selects the spreadsheet based on your city and sets it equal to the SpreadsheetID variable to query the appropriate spreadsheet using the Web component.

CAUTION: Using the city information from CurrentAddress based on where a device is located to select which spreadsheet is only as reliable as Google’s Map database. If No address available is what is returned by CurrentAddress, handle that issue, perhaps with ‘default’ information values stored in a variable or in a TinyDB. You have to provide the logic.

When you code the ‘city’ information your city name must be in the same form as what is reported in the CurrentAddress. It is especially important when using a language that uses diacritical marks ( Sidi Bel Abbès might have to be entered without the diacritical marks … Sidi Bel Abbes).

Is the example working with your spreadsheet (remember, the example does not link to a Google Spreadsheet unless you provide it and the information to link to)?

The example aia: csvGoogleDriveExample.aia (7.1 KB)



Bonjour Mr Steve
c’est quoi l’erreur j’ai pas compris même j’ai utilisé DO IT
il me donne le même

message d’erreur


I discussed this here A propos google sheet

I believe the error is you set up your spreadsheet link inappropriately as I show in the above link. The theCSV is probably being set to an empty value. Why is an empty theCSV causing a problem? It is causing a problem because thePhoneList is created from the theCSV. Since theCSV is empty, you see the error Attempt to get item number 2 of a list of length 0. If you do a DoIt of theCSV, you should find that variable is empty.

Sorry, one of the PU’s made this explanation difficult by splitting the topic. He didn’t need too. I think he does not understand the problem is part of your previous question and the problem is related to using a LocationSensor.CurrentAddress and a Get to provide the phone numbers from different wilayas.

Does this explanation help?

Bonjour Mr Steve
toujours le même problème
les appels téléphonique ne se fonctionnent pas

lorsque je clique sur gendarmerie pompier ou police il me donne une page HTML
et aussi la même erreur : ATTEMPT TO GET ITEM NUMBER 2 OF A LIST OF LENGTH 0

et voici le block

Did you click on the Find your City spreadsheet button before you use the phone button? It appears you did since the CurrentAddress block returned “Constantine”. That part of the app works.

That means there is an issue with your spreadsheet. Label1 should display 200 showing the connection was made correctly.

Please show an image of these two blocks after you perform a DoIt (I want to see what is being captured from the spreadsheet) and send an image of your spreadsheet.

The app should not be displaying an html file in Label2. It should be displaying what is shown in those Blocks. Label2 displays responseContent . Maybe we can figure out why this is happening if you show what the DoIt shows.

Did you set your spreadsheet to SHARE?

Congratulations; you got the spreadsheet to SHARE and the tutorial works as it is designed using your spreadsheet.

You now have a very different problem. The problem now appears to be what to do when the GetText responsecode is not 200. The discussion continues at If Web.Get is not 200

Bonjour Mr Steve
ça marche bien, j’ai fait le partage de la feuille Google Sheet en public
Mais Mr Steve je veux que le lieu s’affichera directement sans cliquer sur afficher la location
et faire l’appel même la location n’a pas encore affiché parcequ’elle prend du temps
et puisque les numéros sont les mêmes pour les 48 wilaya donc appeler sans attendre l’affichage de la wilaya Capture67

@Samira_kh This question is answered here> If Web.Get is not 200 The link describes a possible solution.

You can make the phone calls for all 48 wilaya without a Spreadsheet. Embed the phone numbers as a csv in a Text block “puisque les numéros sont les mêmes pour les 48 wilaya donc appeler sans attendre l’affichage de la” and assign the csv to thePhoneList as shown in the Blocks:


Edit: there is a mistake in the above image for the Initialize Block (and the aia)… fix the red circled items please


phoneCSV.aia (4.4 KB)

Bonjour Mr Steve
je veux combiner entre les deux d’appeler depuis Google Sheet directement et même without Google Sheet

@Samira_kh Why? There is no reason to do both. Combine the spreadsheet and the non-spreadsheet methods of phoning if it is important to you. Examples are shown for both methods. Just do what you want and need to do, Only you know exactly how you want YOUR app to work. It seems the advice at If Web.Get is not 200 does what you want; why haven’t you done that?

Is there a way to scrape the barnacles off this tutorial?

A post was split to a new topic: Multiple Line Graphs on a Single Canvas