I Thought He Came With You is Robert Ellison’s blog about software, marketing, politics, photography and time lapse.

Export Google Fit Daily Steps to a Google Sheet

Google Fit Daily Step Export

Google Fit is a great way to keep track of your daily step count without needing to carry a Fitbit or other dedicated tracker. It's not easy to get that data out though, as far as I can tell the only way is Google Takeout which is not made for automation. Luckily there is an API and you can do almost anything with Google Sheets.

If you're looking to export your step count this post has everything you need, just follow the instructions below to get your spreadsheet up and running. This is also a good primer on using OAuth2 with Google Apps Script and should be a decent starting point for a more complex Google Fit integration. If you have any questions or feedback please leave a comment below.

To get started you need a Google Sheet, an apps script project attached to the sheet and a Google API Project that will provide access to the Fitness API. That might sound intimidating but it should only take a few minutes to get everything up and running.

In Google Drive create a new spreadsheet and call it whatever you like. Rename the first tab to 'Steps'. Enter 'Date' in cell A1 and 'Steps' in cell B1. Next select 'Script editor...' from the Tools menu which will open a new apps script project.

Give the apps script project a name and then select 'Libraries...' from the Resources menu. Next to 'Add a library' enter 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF ad click Add. This will find the Google OAuth2 library. Choose the most recent version (24 at the time of writing) and click Save. Then select 'Project properties' from the File menu and make a note of the Script ID (a long series of letters and numbers).

Open the Google API Console. Create a new project and name it something like 'Google Fit Sheet'. From the Dashboard click Enable APIs and Services and find and select the Fitness API. Then go to Keys and create an OAuth Client ID. You'll be asked to create a consent screen, the only field you need to enter is the product name (i.e. 'My Fit App'). Then choose Web Application as the application type. You need to set the name and the authorized redirect URL. The redirect URL is https://script.google.com/macros/d/{SCRIPTID}/usercallback replacing {SCRIPTID} with the actual Script ID you made a note of above. After adding this make a note of the Client ID and Client Secret.

Go back to the apps script project and paste the code below into the Code.gs window:

// add your Google API Project OAuth client ID and client secret here
var ClientID = '';
var ClientSecret = '';

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Google Fit')
      .addItem('Authorize if needed (does nothing if already authorized)', 'showSidebar')
      .addItem('Get Steps for Yesterday', 'getSteps')
      .addToUi();
}

// see step count example at https://developers.google.com/fit/scenarios/read-daily-step-total
function getSteps() {
  var start = new Date();
  start.setHours(0,0,0,0);
  start.setDate(start.getDate()-1);

  var end = new Date();
  end.setHours(23,59,59,999);
  end.setDate(end.getDate()-1);
  
  var fitService = getFitService();
  
  var request = {
    "aggregateBy": [{
      "dataTypeName""com.google.step_count.delta",
      "dataSourceId""derived:com.google.step_count.delta:com.google.android.gms:estimated_steps"
    }],
    "bucketByTime": { "durationMillis": 86400000 },
    "startTimeMillis": start.getTime(),
    "endTimeMillis": end.getTime()
  };
  
  var response = UrlFetchApp.fetch('https://www.googleapis.com/fitness/v1/users/me/dataset:aggregate', {
    headers: {
      Authorization: 'Bearer ' + fitService.getAccessToken()
    },
    'method' : 'post',
    'contentType' : 'application/json',
    'payload' : JSON.stringify(request, null, 2)
  });
  
  var json = JSON.parse(response.getContentText());
  var steps = json.bucket[0].dataset[0].point[0].value[0].intVal;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Steps');
  sheet.appendRow([start, steps]);
}

// functions below adapted from Google OAuth example at https://github.com/googlesamples/apps-script-oauth2

function getFitService() {
  // Create a new service with the given name. The name will be used when
  // persisting the authorized token, so ensure it is unique within the
  // scope of the property store.
  return OAuth2.createService('fit')

      // Set the endpoint URLs, which are the same for all Google services.
      .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      // Set the client ID and secret, from the Google Developers Console.
      .setClientId(ClientID)
      .setClientSecret(ClientSecret)

      // Set the name of the callback function in the script referenced
      // above that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

      // Set the scopes to request (space-separated for Google services).
      // see https://developers.google.com/fit/rest/v1/authorization for a list of Google Fit scopes
      .setScope('https://www.googleapis.com/auth/fitness.activity.read')

      // Below are Google-specific OAuth2 parameters.

      // Sets the login hint, which will prevent the account chooser screen
      // from being shown to users logged in with multiple accounts.
      .setParam('login_hint', Session.getActiveUser().getEmail())

      // Requests offline access.
      .setParam('access_type', 'offline')

      // Forces the approval prompt every time. This is useful for testing,
      // but not desirable in a production application.
      //.setParam('approval_prompt', 'force');
}

function showSidebar() {
  var fitService = getFitService();
  if (!fitService.hasAccess()) {
    var authorizationUrl = fitService.getAuthorizationUrl();
    var template = HtmlService.createTemplate(
        '<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
        'Close this after you have finished.');
    template.authorizationUrl = authorizationUrl;
    var page = template.evaluate();
    SpreadsheetApp.getUi().showSidebar(page);
  } else {
  // ...
  }
}

function authCallback(request) {
  var fitService = getFitService();
  var isAuthorized = fitService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

Right at the top of the code there are spaces to enter the Client ID and Client Secret from the API Console. Enter these and save the project.

Switch back to your Google Sheet and reload. After reloading there will be a Google Fit menu item. First select Authorize... You'll get a screen to authorize the script and then a sidebar with a link. Click the link to authorize the script to access your Google Fit data. You can then close the sidebar and select Get Steps for Yesterday from the Google Fit menu. You should see a new row added to the spreadsheet with yesterday's date and step count.

The final step is to automate pulling in the data. Go back to the apps script project and select Current project's triggers from the Edit menu. Add a trigger to run getSteps() as a time driven day timer - I recommend between 5 and 6am. You can also click notifications to add an email alert if anything goes wrong, like your Google Fit authorization expiring (in which case you just need to come back and authorize from the Google Fit menu again.

At this point you're all set. Every day the spreadsheet will automatically update with your step count from the day before. You can add charts, moving averages, export to other systems, pull in your weight or BMI, etc. I want to add a seven day moving average step count to this blog somewhere as a semi-public motivational tool... watch this space.

Get an email when your security camera sees something new (Apps Script + Cloud Vision)

Get an email when your security camera sees something new (Apps Script + Cloud Vision)

Nest (previously DropCam) can email you when it detects activity but that gets boring quickly. How about an email only when it sees something totally new?

The script below downloads a frame from a web cam and then calls the Google Cloud Vision API to label features. It keeps a record of everything that has previously been seen and only sends an email when a new feature is detected. You could easily tweak this to email on a specific feature (i.e. every time your dog is spotted), or to count the number of times a feature appears. I'm using a Nest cam but any security camera that has a publicly visible image download URL will work.

var OAuthCreds = {
  "type""service_account",
  //...
};

var SendEmailTo = '';
var MonitorImageUrl = '';



function main() {
  var timestamp = Date.now().toString();
  var scriptProperties = PropertiesService.getScriptProperties();
  var currentProps = scriptProperties.getProperties();
  
  Logger.log('Grabbing a frame');
  var url = MonitorImageUrl + '&cb=' + timestamp;
  var response = UrlFetchApp.fetch(url);
  var image = response.getBlob();
  image.setName('image.jpg');
  var bytes = image.getBytes();
  var encodedImage = Utilities.base64EncodeWebSafe(bytes);
  
  Logger.log('Calling cloud vision');
  var service = getService();
  if (service.hasAccess()) {
    
    var request = {
      "requests":[
        {
          "image":{
            "content": encodedImage
          },
          "features":[
            {
              "type""LABEL_DETECTION",
              "maxResults":50
            }
          ]
        }
      ]
    }
    
    var annotateUrl = 'https://vision.googleapis.com/v1/images:annotate';
    var annotateResponse = UrlFetchApp.fetch(annotateUrl, {
      "headers": {
        Authorization: 'Bearer ' + service.getAccessToken()
      },
      "method" : "post",
      "contentType" : "application/json",
      "payload" : JSON.stringify(request, null, 2)
    });
    var json = JSON.parse(annotateResponse.getContentText());
    
    var anythingNew = false;
    var newText = '';
    
    for (var l = 0; l < json.responses[0].labelAnnotations.length; l++) {
      var description = json.responses[0].labelAnnotations[l].description;
      var score = json.responses[0].labelAnnotations[l].score;
      
      if (!(description in currentProps)) {
        Logger.log('Found new feature: ' + description);
        scriptProperties.setProperty(description, score);
        anythingNew = true;
        newText += 'Found: ' + description + ' (score: ' + score + ')\r\n';
      }
    }
    
    if (anythingNew) {
      MailApp.sendEmail(SendEmailTo, 'Found something new on the webcam ' + new Date(), newText, { 
        attachments: [image] 
      });
    }
    
  } else {
    Logger.log(service.getLastError());
  }
}

// modified from https://github.com/googlesamples/apps-script-oauth2/blob/master/samples/GoogleServiceAccount.gs#L50 below...
function getService() {
  return OAuth2.createService('CloudVision')
      // Set the endpoint URL.
      .setTokenUrl(OAuthCreds.token_uri)

      // Set the private key and issuer.
      .setPrivateKey(OAuthCreds.private_key)
      .setIssuer(OAuthCreds.client_email)

      // Set the name of the user to impersonate. This will only work for
      // Google Apps for Work/EDU accounts whose admin has setup domain-wide
      // delegation:
      // https://developers.google.com/identity/protocols/OAuth2ServiceAccount#delegatingauthority
     // .setSubject(USER_EMAIL)

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getScriptProperties())

      // Set the scope. This must match one of the scopes configured during the
      // setup of domain-wide delegation.
      .setScope('https://www.googleapis.com/auth/cloud-platform');
}

function reset() {
  var service = getService();
  service.reset();
}

There is a bit of setup to get this working. Create a new Apps Script project in Google Drive and paste the code above in. You'll need to provide you own values for the three variables at the top.

OAuthCreds is the contents of the JSON format private key file for a Google Developer Console project. Go to the console, create a new project and enable the Cloud Vision API. You'll also need to enable billing (more on this below) - a trial account will work fine for this. Once the API is enabled create a service account under Credentials and download the JSON file. Just paste the contents of this into the script.

That's the hard part over. Now enter the URL of the image to monitor (see this post for instructions on finding this for a Nest / DropCam device) as MonitorImageUrl and your email address for SendEmailTo.

One last thing - follow the instructions here to reference the OAuth2 for Apps Script library.

Once this is all done run the script (the main() function) and authorize it. You should get an email with a picture attached and a list of the labels detected together with a confidence score from 0 to 1. If this doesn't happen check the logs (under the View menu).

You can now schedule the script to run repeatedly (Resources -> Current project's triggers). You get up to 1,000 units a month for free so once an hour should be safe. If you need more frequent updates check the Cloud Vision pricing guide for details.

After a few runs you should only get an email when something new is detected. If you're seeing too many wild guesses then add a filter on the score to exclude low confidence features.

Enjoy, and leave a comment if you have problems (or modify this in interesting ways).

(Previously)

Capture DropCam (Nest Cam) frames to Google Drive

Capture DropCam frames to Google Drive

Here's an easy way to capture frames from a DropCam to Google Drive. This only works if you have a public feed for your DropCam.

Go to the public page for your DropCam (Settings -> Public -> Short URL Link) and then view source for that page. Near the top you can find the still image URL for your DropCam:

<meta property="og:image" content="https://nexusapi.dropcam.com/get_image?uuid=12345&height=200" />

In Google Drive create a new Apps Script (If you don't already have Apps Script you can find it via Connect more apps...). Paste in the following code:

function downloadFrame() {
  var timestamp = Date.now().toString();
  
  var url = 'https://nexusapi.dropcam.com/get_image?uuid=12345&height=1280&cb=' + timestamp;
  var response = UrlFetchApp.fetch(url);
  var blob = response.getBlob();
  blob.setName(timestamp + '.jpg');
  
  var folders = DriveApp.getFoldersByName('DCFrames');
  while (folders.hasNext()) {
    var folder = folders.next();
    folder.createFile(blob);
    break;
  }
}

Replace the uuid parameter in the URL with the uuid from the still image URL for your DropCam. Note that the height parameter in the script has been changed to 1280 to get the largest possible image. A timestamp is being used to add a random cache busting parameter to the still image URL and is also used as the filename for the image.

The script will save the images to a folder called DCFrames - either create this folder in your drive or change this parameter to the desired folder.

Run the script and check that it's working. If everything looks good go to Resources -> Current project's triggers in the Apps Script editor. You can now set up a timer to save a frame as frequently as every minute (which I'm using to collect frames to make a daily time lapse movie). You can also ask Apps Script to send you an email when the script fails.

Updated 2015-07-01: DropCam is now Nest Cam - assuming that Nest keep the API going everything should keep working as above for both types of camera.