ITHCWY: Robert Ellison's Blog

San Francisco Street Tree Datalapse

San Francisco Street Tree Datalapse

A datalapse video of San Francisco street trees:

City elevation contours and street tree database  from DataSF. I included all trees with a latitude, longitude and known planting date. On the visualization trees grow over 25 years to a generally exaggerated 25 meters radius. Each species of tree is assigned a random color.

Book reviews for August 2017

Empire Games (Empire Games #1) by Charles Stross

Empire Games (Empire Games #1) by Charles Stross

4/5

 

The Delirium Brief (Laundry Files, #8) by Charles Stross

The Delirium Brief (Laundry Files, #8) by Charles Stross

4/5

 

The Thirst (Harry Hole #11) by Jo Nesbø

The Thirst (Harry Hole #11) by Jo Nesbø

4/5

 

The Revolution Trade (The Merchant Princes, #5-6) by Charles Stross

The Revolution Trade (The Merchant Princes, #5-6) by Charles Stross

4/5

 

The Traders' War (The Merchant Princes, #3-4) by Charles Stross

The Traders' War (The Merchant Princes, #3-4) by Charles Stross

4/5

 

Influx by Daniel Suarez

Influx by Daniel Suarez

4/5

 

The Bloodline Feud (The Merchant Princes, #1-2) by Charles Stross

The Bloodline Feud (The Merchant Princes, #1-2) by Charles Stross

4/5

 

Recent Wildlife

Recent Wildlife

Recent Wildlife

Recent Wildlife

Recent Wildlife

Recent Wildlife

Recent Wildlife

Recent Wildlife

Black bear cub, red legged frog, deer, lizard #10, young salmon, elk and a random crab.

2017 Total Solar Eclipse from Madras, OR

Total solar eclipse composite shot from Madras, Oregon for the 2017 eclipse.

Edited highlights from the 2017 total solar eclipse shot from Madras, Oregon.

360 spherical video of totality. This is best in a virtual reality headset.



The three timelapse videos edited into the version at the top of the page. The first one was shot on a GoPro. The second is from a Sony RX100V with an ND5 filter and the third is the Sony timelapse processed to keep the sun at the center of the frame.

(Previously)

Stars from Pine Mountain Lake

Stars from Pine Mountain Lake

Timelapse of sunset and then stars shot over several nights at Pine Mountain Lake in Groveland, CA.

Why Microsoft is Likely Doomed Based on one Email Folder

Close up of the useless Junk folder in Microsoft Outlook

When you get a piece of spam in Outlook you move it to Junk or block the sender. And then, even if that junk mail is marked as read, the Junk folder has a BOLD MESSAGE COUNT. It's the only folder that does this. I cannot do any other work while I have a bold message count and so I have to switch to the Junk folder and delete the message to get rid of it.

Regular email: read, file, done.

Junk email: recognize as spam, click block sender, confirm that I really want to block the sender, switch to Junk folder, mark as read, delete.

Something is really wrong with this workflow. It's a lens through which you can view the ultimate demise of the company. Sure, Office isn't going away soon and Azure is growing like crazy and SQL Server runs on Linux. But somewhere in Redmond 5,000 people designed a Junk email folder that is the MOST IMPORTANT folder in Outlook. The rest were presumably too busy making Windows Update worse to stop this.

My Google experience is that I really don't get much spam. The spam that I do get is hidden from me unless I actually need to rifle through it for some reason. On the occasion I actually get legitimate junk I just flag it as such and never have to touch it or it's ilk again.

Bay Snaps

Golden Gate Bridge

Alcatraz through Spray

Stars over Lake Tahoe

Timelapse of stars over Lake Tahoe, California

4K timelapse of stars and the Milky Way over Lake Tahoe, California.

Email Alerts for new Referers in Google Analytics using Apps Script

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.

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

I needed a console app that reads some inputs from an online Excel workbook, does some processing and then writes back the results to a different worksheet. Because I enjoy pain I decided to use the thinly documented new Microsoft.Graph client library. The sample code below assumes that you have a work or education Office 365 subscription.

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Http.Headers;
using System.Threading.Tasks;
using Microsoft.Graph;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Newtonsoft.Json.Linq;

namespace Excel365Test
{
    /// <summary>
    /// 1) Install Microsoft.Graph NuGet Package
    /// 2) Install Microsoft.IdentityModel.Clients.ActiveDirectory NuGet Package
    /// 3) Register app at https://portal.azure.com/ - need app ID and redirct URL below
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            TokenCache tokenCache = new TokenCache();

            // load tokens from file 
            string tokenPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Excel365Test");
            if (!Directory.Exists(tokenPath)) { Directory.CreateDirectory(tokenPath); }
            tokenPath = Path.Combine(tokenPath, "tokens.dat");
            if (System.IO.File.Exists(tokenPath))
            {
                tokenCache.Deserialize(System.IO.File.ReadAllBytes(tokenPath));
            }

            // this is the OAUTH 2.0 TOKEN ENDPOINT from https://portal.azure.com/ -> Azure Active Directory -> App Registratuons -> End Points
            var authenticationContext = new AuthenticationContext("https://login.windows.net/your-url-here/", tokenCache);

            // only prompt when needed, you'll get a UI the first time you run
            var platformParametes = new PlatformParameters(PromptBehavior.Auto);

            var authenticationResult = authenticationContext.AcquireTokenAsync("https://graph.microsoft.com/",
                "your-app-id",     // Application ID from https://portal.azure.com/
                new Uri("http://some.redirect.thing/"),         // Made up redirect URL, also from https://portal.azure.com/
                platformParametes).Result;
            string token = authenticationResult.AccessToken;

            // save token so we don't need to re-authorize
            System.IO.File.WriteAllBytes(tokenPath, tokenCache.Serialize());
            
            // use the token with Microsoft.Graph calls
            GraphServiceClient client = new GraphServiceClient(new DelegateAuthenticationProvider(
            (requestMessage) =>
            {
                requestMessage.Headers.Authorization = new AuthenticationHeaderValue("bearer", token);

                return Task.FromResult(0);
            }));

            // test reading from a sheet - in this case I have a test worksheet with a two column table for name/value pairs
            var readSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["test"];
            var readTables = readSheet.Tables.Request().GetAsync().Result;
            string readTableId = readTables[0].Name;
            var table = readSheet.Tables[readTableId].Rows.Request().GetAsync().Result;
            
            // convert page to a dictionary... this doesn't handle pagination
            Dictionary<stringdecimal> tableValues = table.CurrentPage.ToDictionary(r => r.Values.First.First.ToString(), 
                r => Convert.ToDecimal(r.Values.First.Last, CultureInfo.InvariantCulture));

            // test adding a row to a table with four columns
            // sadly it seems you need this exact format, a regular JArray or JObject fails

            WorkbookTableRow newRow = new WorkbookTableRow
            {
                Values = JArray.Parse("[[\"1\",\"2\",\"3\",\"4\"]]")
            };
            
            var outputSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["data"];
            var outputTables = outputSheet.Tables.Request().GetAsync().Result;
            string outputTableId = outputTables[0].Name;
            var outputResult = outputSheet.Tables[outputTableId].Rows.Request().AddAsync(newRow).Result;

            // the excel unit tests seem to be the most useful documentation right now:
            // https://github.com/microsoftgraph/msgraph-sdk-dotnet/blob/dev/tests/Microsoft.Graph.Test/Requests/Functional/ExcelTests.cs
        }
    }
}

Paste the code into a new console project and then follow the instructions at the top to add the necessary NuGet packages. You'll also need to register an application at https://portal.azure.com/. You want a Native application and you'll need the Application ID and the redirect URL (just make up some non-routable URL for this). Under Required Permissions for the app you should add read and write files delegated permissions for the Microsoft Graph API.

Hope this saves you a few hours. Comment below if you need a more detailed explanation for any of the above.