Location Tracker with App Inventor 2 using phone sensor to Google Sheets and then link to GlideApps

I lke GlideApps, they are cool. You can make some great apps with them. But you cannot access youtr phone’s sensors. So I thought, use App inventor to grab data from phone sensors, send it to google Sheets and then use GlideAppps to access the same google Sheets with that data to make a richer App. This only works for Android, and this is a proof of concept process.

App Inventor GPS lat/long capture from phone

There didn’t seem to be a video on App Inventor (AI) to Google Sheets. I also haven’t used AI for a while now (2017 last used) so I thought I’d do a couple of basic tutorials on using it. The first was to use this app to get my location and show it on a map- App Inventor, Make An Android App Where My Location In 14 Minutes:

I got the Lat/Long but it would not open a map in a browser.

So another tutorial, this one inmcorporated the map into app and zoomed in on basic location, but, it didn’t make a marker.

So I used this tutorial to add the marker to the map in the app, it required you to set initial variable values then call them later, lat/long & marker values (actually, I only think I needed the variable marker with a null initialisation) :

So this app worked, finding my current location and putting a marker on the map and zooming into that location (set at 18 (zooms totally to 20) may need to be reduced to 16, but can use fingers to zoom out.) I also added accuracy as it sometimes stuck marker a little away from my location. It sometimes puts 2 pins in and at start I need to press button 3 times as it starts in Cambridge in England, then zooms to sea somewhere then finally gives my current location. So it would need something to clear the initial marker and refresh. All in all it works.

A god article here about GPS from App Inventor Exploring with the Location Sensor.

Data to Google Sheets

The next step is to push the data to Google Sheets. I followed the video below but had a few issues:

I tried to cut/paste from the tutorial code into my own, but that didn’t work. I needed to change the last function variables to suit my spreadsheet.


var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1TWsWFZqVLuR5touj0mNQOFmg8793kr0oMOFYagTL_gE/edit#gid=0");
var sheet = ss.getSheetByName("DataIn");


  addUser(e,sheet);
}

function doPost(e) { 
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1TWsWFZqVLuR5touj0mNQOFmg8793kr0oMOFYagTL_gE/edit#gid=0");
  var sheet = ss.getSheetByName("DataIn"); 
  
  addUser(e,sheet);
}


function addUser(e,sheet) {
  var num = e.parameter.num ; 
  var latx = e.parameter.latx ; 
 var longx = e.parameter.longx ;

  sheet.appendRow([num,latx,longx]);    // sheet.appendRow([num,lat,long]);
}

Also the major issue that I cam across was the updating of the code. If you publish to web , when you update you must ensure that you change the revision, otherwise, even though you have a renewed URL you still have the original code. This flumoxed me to such a degree that I had to build the tutorial app from the viodeo to see the spreadsheet being populated, so hours wasted on a fundamental error, one of my classic mistakes. So although I was de-bugging by putting in simple data (rather than Lat/long coordinates) it still did not populate the spreadsheet. I finally got there.

So now I can push data into the spreadsheet.

Next I made a simple GlideApp that plotted the locations on a map.

Time Issues

This is a bit of a twisty one. If you use Google Forms to put data into Google Sheets then you automatically get a timestamp. BUT, if you fire from App Inventor to Google Sheets that is not the case.

Now , there may be a time trigger you could use if a new row is added into Google Sheets, most probaby with a GAS script, but I was trying to do it in App Inventor.

I added the Clock to the App and then Added another variable to my output for time. I had several goes at this and the app got naffy about it and wouldn’t wortk. In the end the below logic works BUT it sends a number to the google sheet:

So we have to change that number to a date/time format. I found an article in StackOverflow : AppInventor: how to insert DateTime into Google Spreadsheet and show only recent DateTime

Basically you have to use miliseconds and then convert the number (lets call it X for the time being by:

(X/ 86400000) + 25569

To generate this number, start with AI’s Millisecond function. NOTE: Both GS and AI use milliseconds, but they have different 0 points, so you have to manipulate the result a bit. The formula I’ve used in AI in the past is this:

GS Date/Time = (Clock1.GetMillis(Clock1.Now) / 86400000) + 25569

https://stackoverflow.com/questions/22941399/appinventor-how-to-insert-datetime-into-google-spreadsheet-and-show-only-recent

Also, in Google Sheets you have to adjust the millisecond number into a Date/Time format.

So that was a long winded process , but it works. Once you have the data you can calculate duration between points to get overall time. No using forms but string manipulation.

Another method to get a timestamp at each marker is to use Google forms to push your data from App Inventor to Google sheets, as per the video below:

End comment

The process went pretty well. Accuracy of data wasn’t that great, but a good starting point for testing.

I’ve been thinking of the interface between the Apps for a while so nice to see some successful information transfer.

Thinking about the compiling of the app, there is an issue of hardcoded link to web app URL. LocTracker2.aia file for people to compile it themselves is HERE. Click on download button.

I made two videos about this process, the first a more general overview:

The second on how I built the 2 apps:

The 3rd video on time/date issues between AI & Google Sheets.