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

Animation of a year of Global Cloud Cover

Animation of a year of Global Cloud Cover

Here's an animation showing a year of global cloud cover (from July 2017 to July 2018) :

The clouds are sourced from the free daily download at xplanet. I run a Google apps script that saves a copy of the image to Google Drive every day (basically the same as this script to save Nest cam images). The hard part was waiting a year to get enough frames. Xplanet combines GEOS, METEOSAT and GMS satellite imagery with some reflection near the poles. Although I didn't need to for this project note that you can subscribe to higher quality / more frequent downloads.

As well as the clouds you can also see the terminator between day and night change shape over the course of the year. This video starts and ends with the Summer equinox when days are longest in the Northern hemisphere.

Where it's nighttime the image is based on NASA's Black Marble. The daytime is based on Blue Marble, but blended with a different older image which has better ocean colors and interpolated daily between twelve monthly Blue Marble satellite images. The result of this is that you can see snow and ice coverage changing over the course of the year. If you look closely you'll also notice vegetation growing and dying back with the seasons.

Rendered in a slightly modified build of Catfood Earth (the main release doesn't know how to access my private cache of xplanet cloud images). As well as combining day, night and cloud images Catfood Earth can also show you earthquakes, volcanoes, US weather radar, political borders, places and time zones. It has been enlivening Windows desktop wallpaper for fifteen years now (as shareware back when that was a thing, these days it's a free download for Windows and Android).

Export Google Fit Daily Steps to a Google Sheet

Updated on Wednesday, July 11, 2018

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')

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

  var end = new Date();
  var fitService = getFitService();
  var request = {
    "aggregateBy": [{
    "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.

      // Set the client ID and secret, from the Google Developers Console.

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

      // Set the property store where authorized tokens should be persisted.

      // 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

      // 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();
  } 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.

Email Alerts for new Referers in Google Analytics using Apps Script

Updated on Sunday, July 2, 2017

Referral Traffic in Google Analytics

It's useful to know when you have a new website referrer. Google Analytics is plagued with spam referral and you want to filter this out of reporting as quickly as possible to stop it from skewing your data. It's also helpful to be able to respond quickly to new referral traffic - maybe leave a comment or promote the new link on social media.

The script below will send you a daily email with links to any new referrers.

var TableId = 'ga:your-view-id';
var SendEmailTo = 'your-email-address';

function main() {
  var scriptProperties = PropertiesService.getScriptProperties();
  var currentProps = scriptProperties.getProperties();
  var anythingNew = false;
  var newText = '';
  var yesterday = Utilities.formatDate(new Date(new Date().getTime() - 24 * 60 * 60 * 1000), Session.getTimeZone(), 'yyyy-MM-dd');
  var options = {
    'dimensions': 'ga:fullReferrer',
    'filters': 'ga:medium==referral',
    'max-results': 20000
  var report = Analytics.Data.Ga.get(TableId, yesterday, yesterday, 'ga:sessions', options);
  if (report.rows) {
    for (var i = 0; i < report.totalResults; i++) {
      if (!(report.rows[i][0] in currentProps)) {
        Logger.log('Found new referrer: ' + report.rows[i][0]);
        scriptProperties.setProperty(report.rows[i][0], report.rows[i][1]);
        anythingNew = true;
        newText += 'New referrer: ' + report.rows[i][0] + '\r\n';
  } else {
    Logger.log('GA report is empty');
  if (anythingNew) {
    MailApp.sendEmail(SendEmailTo, 'Found new referrers for ' + TableId + ' on ' + new Date(), newText);

Start a new apps script project in Google Drive and paste in the code. At the top enter the view ID that you want to monitor and the email address that should receive reports.

Choose Advanced Google Services from the Resources menu and switch on the Google Analytics API. Then click the Google API Console link and enable the Google Analytics API there as well.

Finally pick Current project's triggers from the Edit menu and trigger the main function daily at a convenient time.

This script saves known referrers in script properties. For a site with lots of traffic this may run out of space in which case you might need to switch this out and write known referrers to a sheet instead.

Get an email if your site stops being mobile friendly

Updated on Friday, February 3, 2017

Get an email if your site stops being mobile friendly

Google just released an API for the mobile friendly test and so I've whipped up a script to send an alert if a web page violates their guidelines. This will run the test as often as you like and send you an email if it detects a problem. Alternatively if you're not mobile friendly it will keep emailing you until you fix any problems which might be a good motivational tool.

First start a new apps script project in drive and paste in the code below:

var urlToMonitor = '';
var alertEmail = '';
var runTestKey = '';

var runTestUrl = 'https://searchconsole.googleapis.com/v1/urlTestingTools/mobileFriendlyTest:run?key=';

function mobileFriendlyMonitor() {
  try {
    var postBody = {
      'url' : urlToMonitor
    var options = {
      'method' : 'post',
      'contentType': 'application/json',
      'payload' : JSON.stringify(postBody)
    var response = UrlFetchApp.fetch(runTestUrl + runTestKey, options);
    var json = response.getContentText();
    var mobileFriendlyResult = JSON.parse(json);
    if (mobileFriendlyResult.mobileFriendliness != 'MOBILE_FRIENDLY') {
      sendEmail('Mobile friendly test failed for ' + urlToMonitor + ', check https://search.google.com/search-console/mobile-friendly for details');
  } catch (e) {
    sendEmail('mobileFriendlyMonitor failed for: ' + urlToMonitor + ' with error: ' + e.message);

function sendEmail(msg) {
  MailApp.sendEmail(alertEmail, 'Mobile Friendly Monitor Alert on ' + Utilities.formatDate(new Date(), "GMT""yyyy-MM-dd'T'HH:mm:ss'Z'"), msg);

There are three variables you need to set, urlToMonitor is the full URL of the page to test, alertEmail is your email address (or whoever needs to be pestered) and runTestKey is the API key for the service. To get this go to the Google API Console, click Enable API, search for 'Google Search Console URL Testing Tools API' and click enable. Then click the Credentials option and generate a browser key.

Once you've configured the script choose 'Current project's triggers' from the Resources menu in apps script and set up a schedule for the mobileFriendlyMonitor() function.

Automate Google PageSpeed Insights with Apps Script

Updated on Monday, April 11, 2016


Here's a quick script to automatically monitor your Google PageSpeed Insights desktop and mobile scores for a web page:

var pageSpeedApiKey = '...';
var pageSpeedMonitorUrl = '...';

function monitor() {
  var desktop = callPageSpeed('desktop');
  var mobile = callPageSpeed('mobile');
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('results');
                   Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd'),
    // more available, i.e. desktop.pageStats.numberResources

function callPageSpeed(strategy) {
  var pageSpeedUrl = 'https://www.googleapis.com/pagespeedonline/v1/runPagespeed?url=' + pageSpeedMonitorUrl + '&key=' + pageSpeedApiKey + '&strategy=' + strategy;
  var response = UrlFetchApp.fetch(pageSpeedUrl);
  var json = response.getContentText();
  return JSON.parse(json);

You need a spreadsheet with a tab called results and an API key for PageSpeed Insights (activate the API in the console and create an API key for it, the browser based / JavaScript option). Paste the code above into the script editor for the spreadsheet and add your API key and URL to monitor. Then just choose triggers from the Resources menu and schedule the monitor function to run once per day.

Note that this currently just logs the overall score. There are a bunch of other values returned (like number and types of resources on the page) that you could choose to monitor as well. It would also be easy to extend this to monitor more URLs, or to send you an email if the score drops below a threshold.

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

Updated on Friday, July 7, 2017

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 = {

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();
  var bytes = image.getBytes();
  var encodedImage = Utilities.base64EncodeWebSafe(bytes);
  Logger.log('Calling cloud vision');
  var service = getService();
  if (service.hasAccess()) {
    var request = {
            "content": encodedImage
    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 {

// 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.

      // Set the private key and issuer.

      // 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.

      // Set the scope. This must match one of the scopes configured during the
      // setup of domain-wide delegation.

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

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).