Quantified Self – Google And Android – Reporting

In the previous series I showed you how to

• capture your data automatically,
• collate and summarise your data.

Now it is time to show you how you can extract this information, with the final goal being the ability to get your (android in this case)phone polling for the data, and using it to make decisions.

In order to do this we will be using:

Google Apps Script (GAS) javascript functions to obtain values from the Google Spreadsheet where your data lives.

Google Web Services to provide a web interface to call the function and return the data over the internet.

Tasker to provide a programmatic interface on your phone to call the web service, handle the resulting data and do “something” with it.

Minimalistic Text to display the output on the home screen of your phone.

As previously stated – I’m not a developer to trade so my code is in no way elegant or best practice – it is simply to fulfill a need that I have. This was my first foray into writing web services and this drove a lot of the decisions that I made. I could have embedded all of the logic into the web service it’s self. I preferred to keep it separate so that I could test my application code separately from the web service (which was initially problematic for me).

I also decided to create a single parameterized we service that I could make do many things depending on the parameters that I pass to it. With hindsight – I’d probably create a single web service for each function I need.

Firstly – we need to create a javascript function that we can use to read the data from our google spreadsheet. In my example I only want a single value to be returned so my function will accept four parameters: the spreadsheet ID, the sheet name, the row and the column.

NOTE: for simplicity sake both the row and column are numeric values, cell B5 would be row 5 column 2.

The function to read the data is as follows:

function readSheet(sheetID, sheetName, sheetRow, sheetColumn) {
 Logger.log(sheetID);
 Logger.log(sheetName);
 Logger.log(sheetRow);
 Logger.log(sheetColumn);
 var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(sheetName);
 var value = sheet.getRange(sheetRow, sheetColumn).getValue()
 return value
}


NOTE: You can remove the logging columns if you wish – I added them in to help troubleshoot the web service.

Before going any further – we have to test the function, I used:

function testSheet(){
 Logger.log(readSheet('xxxYourSheetID12345xxxInHere', 'Sheet1', 2, 3))
}

ran the above function and read the log to confirm that it did return the value referred to in the parameters.

This is great for getting data from one sheet to another of from sheets to scripts but is of no direct use by my phone as android phones can’t call GAS scripts.

In order to call this function “externally” we need a web service.

This was by far the most tricky part of the process – hopefully the following steps will help you circumvent most of the problems that I had.

As I said, my code is probably overly complex but I’ll talk you through it step by step (unlike the code above which I think is self explanatory).

function doGet(request) {
 var serviceName = request.parameters.serviceName;
 var myResult;
 switch("" + serviceName){
   case "readSheet":
     myResult = 'Read Sheet';
     var sheetID = request.parameters.sheetID;
     var sheetName = request.parameters.sheetName;
     var sheetRow = request.parameters.sheetRow;
     var sheetColumn = request.parameters.sheetColumn;
     return ContentService.createTextOutput(readSheet(sheetID, sheetName, sheetRow, sheetColumn));
     break;
   case "readSheetName":
     myResult = 'Read Sheet Name';
     var sheetID = request.parameters.sheetID;
     var sheetName = request.parameters.sheetName;
     var sheetRow = request.parameters.sheetRow;
     var sheetColumn = request.parameters.sheetColumn;
     return ContentService.createTextOutput(readSheet(getSheetID(sheetID), sheetName, sheetRow, sheetColumn));
     break;
   case "writeSheet":
     myResult = 'Write Sheet';
     return ContentService.createTextOutput(myResult);
     break;
   default : myResult = 'Unknown Service : ' + serviceName;
     return ContentService.createTextOutput(myResult);
 }
}

Taking this step by step:

function doGet(request) {
 var serviceName = request.parameters.serviceName;
 var myResult;

My web service will do multiple “things” I will pass my parameter a variable called serviceName which will describe what I want it to do.

 switch("" + serviceName){
   case "readSheet":
...
     break;
   case "readSheetName":
...
    break;
   case "writeSheet":
...
     break;
   default :
...
 }

This is where I define the values for the serviceName and put the code for each of the options.

Looking specifically at readSheet:

   case "readSheet":
     myResult = 'Read Sheet';
     var sheetID = request.parameters.sheetID;
     var sheetName = request.parameters.sheetName;
     var sheetRow = request.parameters.sheetRow;
     var sheetColumn = request.parameters.sheetColumn;
     return ContentService.createTextOutput(readSheet(sheetID, sheetName, sheetRow, sheetColumn));
     break;

If I’m calling the readSheet web service I will also be passing in four additional parameters: sheetID, sheetName, sheetRow and sheetColumn which we read from the request object and store in local variables. I then call the Javascript readSheet function that we created above, pass in the variable values we have just obtained and will return the specified value.

This is then passed to the standard web service ContentService.creatTextOutput function to return the provided value.

Now the hard part – getting it to work. I’m also using a library to manage my code so I can’t remember which steps are due to it being a library and which are for the web service.

1. The code must be versioned.
   In the code editor select File | Manage Versions | Save New Version.
   NOTE: If you edit your code – you have to create a new version. The web service runs against a specific version of your code NOT the version that you can see on your screen (yes that WAS confusing for a long time)

2. Create the web service
   In the code editor select Publish | Deploy as Web App.
   Select the version of your code (see step 1) Select who the script runs as – I selected “me”. This means that the script can access everything that I can access.
   Select who can run your web service – I selected “public”. I had to select this option as I want to access this data anonymously (via my phone)
   IMPORTANT: Hopefully you will have deduced that the web service is accessible by everyone and the service can access all of your data ! The only protection you have is

   1. make sure no one knows the unique URL at access your web service.

   2. you do NOT create a generic web service that can access everything – you create a specific service that can only access a restricted set of data eg don’t pass the parameters into the web service, hard code them in the body of the web service or underlying functions and then create multiple web services eg getMonthlyRunMiles, getWeeklyRunMiles etc.

Now to test the web service. In the Deploy screen above you will have been given a url along the lines of: https://script.google.com/macros/s/xxxAUniqueServiceIDxxxx/exec

You then have to add to the end of this any parameters you want to pass in the format:

?parameter1=value&paramater2=vaue

for as many parameters as you have defined.

This quickly becomes tedious so I created a google spreadsheet to hold the url and parameter name / value pairs and would build the resulting URL using the formula : “=$B$1&"?"&A3&"="&B3&"&"&A4&"="&B4&"&"&A5&"="&B5&"&"&A6&"="&B6&"&"&A7&"="&B7”

B1 being the starting URL, A(3:7) holding the parameter names and B(3:7) holding the values.

This made it easy to modify the parameter values to test all the permutations that I wanted.

NOTE: I have a spreadsheet where I record the “name” of all my spreadsheets and the unique code that google assigns it, I have a function referred to above called getSheetID that I can call to look up the name and return the code for the spreadsheet rather than “remembering” and passing around the really long and ambiguous strings. This is a very slight modification to the function I have already described.

Now you have a working web service where you can, from your browser, enter a URL and get access to any data item you want. How do we now make this accessible from your Android phone?

To do this we are going to use Tasker to call the web service (it’s just a URL remember) read the returned value, store it in a Tasker variable and then do “something”.

The example I’m going to use is to read my current weight from my weight tracking spreadsheet (logged automatically via my WiFi FitBit scales and an auto API extract of course) via the web service and then display it on the screen of my phone via the Minimalistic Text widget for android.

I’m not going to go into the nitty gritty of Tasker – there are various other sites that cover than in greater depth than me.

First create a Tasker profile called GetWeight (rename as appropriate)

The trigger for this will be time based (change if you wish) – I set mine to 7:00 (after I weigh myself in the morning)

Now we need to create a three step action.

1. From the Net category select the “HTTP Get” action.
   The server:port will be the url of the web service just as you tested it above, including all the parameters and values.
   This calls the web service and gets the returned value.

2. From the Variables category select the “Variable Set” action.
   The Name will be the name of the Tasker variable you want to populate with the returned value – I chose %CURRWEIGHT
   The To will be %HTTPD
   This puts the returned value from the web service into the variable called CURRWEIGHT.

   At this point you may wish to test the action by running it and viewing the contents of the variable in the VARS pane in Tasker. depending on what you want to do you don’t need to do the next step.

3. From the Plugin category select Minimalistic Text.
   Click the pencil icon next to Configuration and Variable name will be the name of the Minimalistic Text variable that you want to populate %MYWEIGHT and Variable Content will contain the tasker variable you created above; %CURRWEIGHT

Now we jump into minimalistic text – again I’m going to assume a basic understanding of how to use this. Create a new homescreen widget (or edit an existing one). In the layout tab click “Add” and go to the “Misc” section. From here we will select a “Locale variable”. We then click on the newly added locale variable and in the “Variable name” enter the name of the variable that you referenced in step 3 above (%MYWEIGHT in my case).

Now on my home screen I have a widget that displays my current weight, and is updated automatically without me doing anything more than stepping on the scales !