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.

Enable GZIP compression for Amazon S3 hosted website in CloudFront

Enable GZIP compression for Amazon S3 hosted website in CloudFront

By default compression doesn't work in CloudFront for a website backed by an Amaxon S3 bucket.

The first step is pretty obvious - switch on compression in CloudFront:

Compress Objects Automatically option in Amazon CloudFront

To get to this setting open you distribution, go to the Behaviors tab and edit your behavior(s). Scroll down to the bottom and toggle Compress Objects Automatically to On. Save and drum your fingers while the distribution updates.

The less obvious piece is that CloudFront will only compress files between 1,000 and 10,000,000 bytes (as of writing this post) and it detects the filesize from the Content-Length header. What the documentation doesn't mention is that S3 does not send the Content-Length header by default and so no compression is applied.

Go to S3 and open the properties for your bucket (not for individual files). Expand Permissions and then click Edit CORS Configuration. You need to add Content-Length as an allowed header like this:

Amazon S3 CORS Configuration

Catfood Software Support

Catfood Software Support

Need help with a Catfood Software product? Please leave a comment below.

Catfood Earth 3.41

Catfood Earth 3.41

Catfood Earth 3.41 fixes a problem that was preventing the weather radar layer from loading.

I've also updated to the latest (2015g) time zone database and the latest time zone map from Eric Muller.

Download the latest Catfood Earth.

(Previously)

Catfood Earth 3.40

Catfood Earth 3.40

I've just released Catfood Earth 3.40 for Windows and 1.50 for Android.

Both updates fix a problem with the clouds layer not updating. The Android update also adds compatibility for Android 5 / Lollipop.

Also, Catfood Earth for Android is now free. I had been charging $0.99 for the Android version but I've reached the conclusion that I'm never going to retire based on this (or even buy more than a couple of beers) so it's not worth the hassle. Catfood Earth for Windows has been free since 3.20.

Enjoy!

Fortune Cookies for Android

Fortune Cookies for Android

Fortune is now available on Google Play. It's an Android version of the UNIX fortune program and will send a random fortune cookie to your notification area at 8ish every morning.

ZoneInfo Update (tzdata for .NET)

ZoneInfo Update (tzdata for .NET)

I've used the ZoneInfo (PublicDomain.ZoneInfo) project from CodePlex for quite a few years, especially in Catfood Earth. The project had rusted a little so I emailed the author (Mark Rodrigues) and he was kind enough to add me as a developer. I've just updated ZoneInfo with some of the local changes I'd made and a variety of patches from the CodePlex community. It now works with the latest IANA tzdata file, at least for the test cases I can run. Let me know if I missed something (and thanks Mark for letting me contribute back to this very helpful project).

Thank you Feedly

Thank you Feedly

It has been brought to my attention that I've been whinging too much recently

So I'd like to take a break from that and say how much I'm enjoying feedly. It's a wonderfully well designed RSS reader. I use the Chrome Extension version and the Android app. It preserves the Google Reader keyboard shortcuts so I can sail through my subscriptions and it brings back social sharing. 

I looked at feedly once before and didn't really get it. I thought it was just one of those algorithmic recommendation news manglers that tries to guess what you want to read. It might do that on the home page but the 'All' view is a perfect replacement for Google Reader. 

I love it. I want to pay for it to make sure it stays around. Thank you feedly. 

Reviews and Links for August 2012

The Last Policeman by Ben H. Winters

5/5

Stonking police procedural set in the months leading up to a global catastrophe.

 

Kill Decision by Daniel Suarez

5/5

Excellent techno-thriller. A little more serious and focused than Daemon and Freedom (TM). It's about a worst case drone scenario, ants, extra-special forces and some smart birds. Very good.

 

Links

Windows 8, Users 0? http://t.co/966Cuwjz

Bill Nye declares Todd Akin "fucking idiot"; issues debate challenge http://t.co/AZ3k55Y4 #fb

ITHCWY: Fight Facebook with Email: I was a little saddened to read today that Diaspora is transitioning over to… http://t.co/2G0pDdu0

Diaspora Founders To Move On, Handing Over Decentralized Social Network ‘To The Community’ http://t.co/KQGb2kpv -- sad, but not the future

RT @MargaretAtwood: Just used http://t.co/Nhna2CGO for gruesome printer problem: excellent, done in 10 mins! Tks to S H E F I N. Website ...

Check out Catfood Earth Live Wallpaper on Google Play! https://t.co/NTJQ1sYL

ITHCWY: Twenty-Four Hours with Twilio: I've wanted to play with Twilio's voice and SMS service for a while and… http://t.co/KOK0PG2M

Tuesdays http://t.co/BLNiCP3H

Twitter Cuts Off Tumblr's Ability to Find Friends http://t.co/1g3ZcClf

5 of 5 stars to The Last Policeman by Ben H. Winters http://t.co/9NF2nviH

XML: http://t.co/VadVt321 #rofl

Windows 8 Is Now Available For Developers (And For Everybody Else, There’s A 90-Day Free Trial, Too) http://t.co/iu1li6BV

Gotye's YouTube orchestra remix of "Somebody That I Used to Know" http://t.co/OUEXXltQ

ITHCWY: City by the Bay: View from Bernal Hill this afternoon. http://t.co/11cI3ctk

Nice panorama! Curiosity rover: Martian solar day 2 #360pano http://t.co/w1H2ocUm via @360cities

ITHCWY: Share a picture in MonoDroid: Here’s how to share a picture to Facebook, Twitter and so forth from… http://t.co/pByzvjmx

5 of 5 stars to Kill Decision by Daniel Suarez http://t.co/TKgUMNW0

BBC News - Mars rover makes first colour panorama http://t.co/fZ7u8smZ

How Apple and Amazon Security Flaws Led to My Epic Hacking http://t.co/BDmRAbm3

Via KQED Guides: Guide to Bay Area Tidepools: Where to Explore Amazing Marine Life | http://t.co/TDBRDnTD #todo @myEN

ITHCWY: Catfood: WebCams for Android: I’ve just released a WebCam app for Android. It’s based on WebCamSaver but… http://t.co/azZUwkkz

Pay for a new social network with no ads? https://t.co/2tox3c2y Anyone I know going to be on there? #fb

Curiosity http://t.co/D2cyWE66

BBC News - Photo shows Mars rover descent http://t.co/KfOv1qOF

Catfood WebCams for Android - Catfood Software http://t.co/VzeySq90 via @CatfoodSoftware

Check out Catfood WebCams on Google Play! https://t.co/VTU8YiBd

ITHCWY: Not a Private Key: When jarsigner says "Key Associated with [alias] not a private key" it almost certainly… http://t.co/3sk89ENV

ITHCWY: Sending email via GMail in C#/.NET using SmtpClient: I’ve stubbed my toe on this a couple of times, so here… http://t.co/QJ7YjcjI

Help end patent litigation insanity and tell your congress person to back SHIELD. http://t.co/27anadBt

ITHCWY: Support SHIELD–a small measure of patent sanity: A friend pointed me at the SHIELD (PDF) act today. This… http://t.co/ArXHgZ0e

ITHCWY: Thank you for choosing HSA Bank!: No, thank you HSA Bank for not giving me a choice and then cheekily… http://t.co/9torSXCq

Bill would force patent trolls to pay defendants’ legal bills | Ars Technica http://t.co/poB3zzlX +1, via @sr00t

What a happy coincidence. As well as #IPAday it's also goof off at work day: http://t.co/7AItItWq

Apparently it's #IPADay - luckily there's some @21stAmendment in the fridge. http://t.co/jyYPSePC

Reviews and Links for May 2012

No book reviews this month.

Links

#boarding SFO http://t.co/YLDFpmwF

Penn Jillette's rant against Obama's drug policy http://t.co/Ri5HAqxH

Congratulations @SpaceX -- Dragon arrives at space station in historic 1st http://t.co/91suk4ZV via @sfgate

Why your camera's GPS won't work in China (maybe) http://t.co/FQIFN8wI

Sigh, obvious, invalid, bullshit -- BBC News - Microsoft wins patent fight with Google's Motorola unit http://t.co/0PENWTCV

BBC News the secret links between Star Wars and Wales http://t.co/T8yEulCu (is there any tenuous link with Wales you won't publish?)

:) Hot weather to continue next week http://t.co/izAc2yA1

Not Skip's Tavern any more... http://t.co/dPx1NIj8

Reality rocks in San Francisco earthquake exhibit http://t.co/yo82B38b (Looking forward to this!)

BBC News - In pictures: Annular eclipse http://t.co/YA5F6or2 (Check out the Lemurs checking out the eclipse)

ITHCWY: Annular Eclipse at SFO: The only solar observatory outside the international terminal at SFO (some… http://t.co/ZqMXm8Ec

Beer was near, sadly earlier. http://t.co/2BeMAJZj

America's great divergence - American History - http://t.co/zQcVJIcQ http://t.co/fSdtXSvl

"Why won't you answer me?" - Parenting - http://t.co/zQcVJIcQ http://t.co/Ljzg6vpG (I should stop telling Kate about her 'milk head')

1906 earthquake refugee cottage at The Presidio. http://t.co/pof5LotA

+1 Judge suspends US law that provided for indefinite detention without trial - Boing Boing http://t.co/xsBuLyYb via @BoingBoing

Daniel Raven-Ellison, Guerrilla Geographer Information, Facts, News, Photos -- National Geographic http://t.co/DjgJdvMJ via @NatGeo

ITHCWY: Gopher Snake: Bernal Heights Park http://t.co/OoHYDU0y

Turned out nice... http://t.co/G2pHtgbd

RT @CatfoodSoftware: Blog: Catfood Software on Google+ and a Hangout Pledge: Catfood is now on Google+. Once 50… http://t.co/ZC84h8AN

ITHCWY: Open Immigration: I'm increasingly in favor of opening up immigration. Partly it's a general sense that a… http://t.co/cBLQT2rI

ITHCWY: Snake rests on Toad: At the California Academy of Sciences. http://t.co/YTQh682A

New Golden Gate Visitor center - lots of tat, no food :( http://t.co/vXmZn099

Obama sighting on morning dog walk. #fb http://t.co/wZdwN2Mj

President Obama: 'I Think Same-Sex Couples Should Be Able to Get Married'; http://t.co/CtC6k2A8 (shameful that it has taken this long)

Gaia revisited: http://t.co/sHSeFph7 - I'm still in the extreme camp: http://t.co/6dzIjBy7

Post Doyle Drive detour quite pleasant on the way home tonight. http://t.co/fSK8e4v9

THINKWALKS: http://t.co/ToOZo3KQ #todo in San Francisco @myEN

ITHCWY: Bottled Water: A company called Evive launched this week to battle the evil of bottled water with reusable… http://t.co/5X9e3emO

ITHCWY: Pelicans http://t.co/0xoup7z5

It's @KQED pledge yet again. Throw them a bone public radio freeloaders: http://t.co/00UvTamT

Illegal dumping can now be voted to fix at http://t.co/SsliF12n #bernal-heights!

Yet Another Awful Dumping Incident on Bernal Hill http://t.co/35kLDRdn via @bernalwood

+1 AllClear ID Rolls Out First-Ever Social Security Number Blocking Service For Children's IDs http://t.co/wnRILNzQ via @techcrunch

Rejected and controversial New Yorker cover art (the mentos one is very good) http://t.co/afo9AxZd via @BoingBoing #fb