A rather neat piece of plumbing, Chrome extension pushes byte array of jobs data to Excel via Python

Transcript

The United States is suffering from extremely high unemployment and in this post I give an application that harvests job leads from a leading jobs website. The application has numerous technical components, (i) a Chrome extension, (ii) a Python webserver housed as a COM component and (iii) a VBA deserialization component. Taken together they demonstrate transmitting binary data from the browser through to the Excel worksheet.

In the US, initial jobless claims are running at a 4-week average of 3 million and the non-farm payrolls are currently at 20 million. These figures are both depressing and staggering. Europe can expect suffering on similar terms. Hopefully the code in this post can assist some to find work.

Co-browsing vs Web-scraping

Websites depend upon ad revenue to survive and so they need humans to see the adverts placed. Every time a human sees an advert it is known as an impression. Web-scraping is the process of running code to fetch a web page and to scrape data from the HTML; this typically involves the automation of a hidden web browser and as such any adverts on a hidden web page are no longer viewable but rendering ad impression statistics false. Eventually, this means that ad revenue is debased and devalued. As such, I disapprove of web scraping.

Instead, I give a ‘co-browsing’ application where code captures job leads from a web page that a human user is browsing. So this application is only active when a human browses a web page. This means any advert impressions are genuine and website’s revenue is not threatened.

The code

There are three separate parts to this application, (i) the chrome extension, (ii) the Python web server (housed as a COM component) and (iii) the VBA deserialization component. They are all in Github, https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/

The Chrome Extension

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/Chrome%20Extension/

The chrome extension will wait for a jobs page to load and then read the jobs data, it builds a JavaScript array of jobs and when complete it will convert the single dimensioned array of jobs into a two-dimensional grid array where each row is one job and the attributes are spread across the columns.

I convert to a grid because ultimately it will be sent to an Excel session where it is to be pasted onto a worksheet. The grid is then persisted to a byte array instead of JSON to take advantage of a data interchange format native to VB6, VBA that I have re-discovered and that allows a byte array to be deserialized to a VBA (OLE Automation) Variant (two dimensional).

Once converted to a byte array we make an XMLHttpRequest() to the Python web server (see next component). If you are experimenting then you might need to change port number in the code here.

There are two main JavaScript files, content.js and JavaScriptToVBAVariantArray.js. The former houses logic specific to this application whilst the latter is the array conversion code library file which I intend to use across a number of projects.

Python Web Server housed as a COM component

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/PythonWebSeverCallsBackToExcel

I have previously written about and given code as to how to write a Python web server housed as a COM component and instantiable from VBA. I have also previously written about and given code as to how to call back into Excel VBA from a Python class.

But there is something new in this Python web server which needs detailing, in short one cannot simply call back into Excel with an interface pointer passed in a different threading apartment; instead the interface pointer has first to be ‘marshalled’. I have encapsulated the plain vanilla callback code in the Python class CallbackInfo and the special marshalling case in its derived class MarshalledCallbackInfo.

In the context of the application, the Python web server is part of the pipeline that passes the byte array from the Chrome extension into Excel VBA. It calls into Excel VBA by calling Application.Run on a (marshalled) Excel.Application pointer. The name of the procedure called by Application.Run is configurable, and passed in. Time to look into the VBA code.

Excel VBA

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/ExcelVBA

I do not check into whole workbooks, I check in the individual code modules instead. Thus to build the Excel VBA workbook code base one needs to import the modules. Luckily, I wrote one module called devBuild to import the rest of them. I intend to follow this pattern when placing code in GitHub. Look at the README.md file for more detail. From here, I’ll assume you’ve built a workbook codebase.

I have written about the serialization and deserialization of Variants to byte arrays and back again so I’ll refer you to that post for the details. In short we take the byte array passed from the Chrome extension via the Python web server and deserialize this to a two dimensional variant array which can then be pasted onto the worksheet.

I guess I could write some more code to build a cumulative list but the point of this project was to show binary data being passed from browser to Excel, to demonstrate (a) the plumbing and (b) the binary data interface format (i.e. no JSON).

Chrome Extension - Stop Stray CORS requests

If I type in a web address such as the British Newspaper theguardian.com then I might naively expect all resources to be delivered from that domain name. But the modern web page has all sorts of cross network calls to web analytics and constant delivery networks (CDNs). Web analytics are a fact of modern life; they help firms reach their customers with better targeted adverts. No more scatter-gun adverts, we can now have pertinent products pitched to us individually. This helps allocative efficiency which is a good thing.

If web analytics were restricted to economic transactions then I'm confident there would be no problem. Sadly, some web analytics have been put to political use which is naughty. How does one opt out of a naughty analytics provider?

Use Hosts file to Block WebAnalytics

In the past I have altered my computer's hosts file at C:\Windows\System32\drivers\etc\hosts so a name resolves to loopback interface 127.0.0.1 meaning data destined for an address never leaves your computer. But this is like a sledgehammer to crack a nut.

Chrome Extension CORS filter

The precise technical term for cross network calls is Cross-origin resource sharing (CORS). CORS requires an exchange and interaction between (browser) client and (web) server.

On the server side, by default web servers disallow CORS and programmers have to actively change their code to permit CORS requests and actually even on this blog you'll find an example enabling CORS.

But the loopholes opened on the server side can be closed on the client side.

On the client side, we can write a Chrome Extension to disable CORS requests. I have given Chrome Extension examples before on this blog. This post's extension is slightly different in that it runs as a background script instead of a context script.

So in our example we are going block requests to Facebook domains because at the time of writing they are 'on the naughty step', being criticized by a British Parliamentary Oversight committee. U.S. Congressional oversight committees' reports are currently pending. But the code could be tweaked to apply to all manner of naughtiness.

manifest.json

Here is the manifest.json file. Create a directory, I called mine N:\CORS Chrome Extension\ and copy this there. This is a standard manifest file, it asks for permissions to block web requests.

  {
    "name": "Cross Origin Filter",
    "version": "0.0.6",
    "description": "Helps you stop stray CORS requests.",
    "permissions": [
      "webRequest",
      "webRequestBlocking",
      "*://*/*"
    ],
    "background": {
      "scripts": [
        "bgp.js"
      ],
      "persistent": true
    },
    "manifest_version": 2
  }

bgp.js

Below is the background page script, I called mine bgp.js (it must match entry in manifest.json) and saved this again in folder N:\CORS Chrome Extension\

The code adds a listener to the event onBeforeSendHeaders but whilst other events are available we need to scan through the request headers looking for the Referer so we can establish if the request is cross domain.

The code parses URLs using the URL object ;we only need the hostnames e.g. www.theguardian.com, www.facebook.com so we throw away the parameter string. Once we have the hostnames we can compare them against a list of domain names to block. There are two list matching sections, one compares exactly and the other compares the tail of the domain name.

If a domain matches one we want to block then we create a blockingResponse object and set its cancel property to true. This cancels the webrequest. We print to the console when we've blocked a domain.

chrome.webRequest.onBeforeSendHeaders.addListener(function (details) {
  
  var myVars = {};
  myVars.urlsPresent = false;

  try {
    myVars.requestURL = (new URL(details.url)).hostname;
    for (var i = 0, l = details.requestHeaders.length; i < l; ++i) {
      if (details.requestHeaders[i].name == 'Referer') {
        referer = details.requestHeaders[i].value;
        myVars.refererURL = (new URL(referer)).hostname;
        myVars.crossOrigin = (myVars.refererURL !== myVars.requestURL);
        myVars.urlsPresent = true;
        break;
      }
    }
  }
  catch (err) {
    console.log("Error whilst determining URLs, err.message: " + err.message);
  }

  if (myVars.urlsPresent === true) {
    try {
      myVars.block = false;

      if (myVars.crossOrigin === true) {

        {
          var aBlockCrossOriginEndsWithList = [".fbcdn.net"];
          for (var i = 0, l = aBlockCrossOriginEndsWithList.length; i < l; ++i) {
            if (myVars.requestURL.endsWith( aBlockCrossOriginEndsWithList[i])) {
              //debugger;
              myVars.block = true;
              break;
            }
          }
        }

        {
          var aBlockCrossOriginList = ["connect.facebook.net", "www.facebook.com"];
          for (var i = 0, l = aBlockCrossOriginList.length; i < l; ++i) {
            if (aBlockCrossOriginList[i] == myVars.requestURL) {
              myVars.block = true;
            }
          }
        }


      }
    }
    catch (err) {
      console.log("Error whilst determining blocking, err.message: " + err.message);
    }
  }

  if (myVars.block === true) {
    try {
      console.log("CORS Filter v.0.0.6, blocking " + myVars.requestURL + " from " + myVars.refererURL);
      //debugger;
      blockingResponse = {};
      blockingResponse.cancel = true
      return blockingResponse;
    }
    catch (err) {
      console.log("Error whilst returning blocking response, err.message: " + err.message);
    }
  }

}, { urls: ["*://*/*"] }, ['requestHeaders', 'blocking']);

Here is the console output showing how a web page from the Guardian is having cross domain calls to Facebook blocked.

Python - Javascript - MutationObserver- Chrome Extension - ClockWatch

So in the previous post I gave code to detect changes in a web page and POST those changes to a Python based web server which in turn writes the payload to a folder, i.e. an HTTP based message queue. But that code was embedded in the same page as that which changed and was unrealistic. Much better if we can write a Chrome extension to sit in the browser and observe changes on other web pages. That is what we do here on this blog post.

Legal Note

Do please get permission before you start web scraping otherwise you fall foul of the law. There are legitimate use cases for this technology: imagine you are at a large company with plenty of IT teams, you've asked for a data feed but other team say you are not a priority but say feel free to web scrape.

Minimal Chrome Extension

The bare minimum to get a Chrome Extension working is one folder containing two files, that's all. The folder can be named anything. The two files are (i) content.js and (ii) manifest.json.

manifest.json

Here is an example manifest.json file

  {
    "name": "Clock Watch",
    "version": "0.1",
    "description": "example of a Mutation Observer",
    "permissions": [],
    "content_scripts": [ {
      "js": [ "content.js" ],
      "matches": [ "http://exceldevelopmentplatform.blogspot.com/2018/06/javascript-dynamic-blog-clock.html"   ] }
    ],
    "manifest_version": 2
  }

So much of the manifest is boilerplate but one thing to note of interest in the matches array which tells what pages to run extension over. I have published the clock code to a separate blog post, http://exceldevelopmentplatform.blogspot.com/2018/06/javascript-dynamic-blog-clock.html and we'll use that as a laboratory test page. In the matches array one can give a selection of pages, here we only have one.

content.js

This is the content.js file with the code pretty much unchanged from the previous post; all that is added is an IIFE (Immediately Invoked Function Expression) which serves as an entry point, i.e. code that runs first. Also, we have a try catch block around our MutationObserver code to help debugging.

~function () {
  'use strict';
  console.log("clock watch iife running");
  setTimeout(startObserving,1000);
  
}();

function startObserving() {
  'use strict';
  
  try {
   
        console.log("entering startObserving");
        var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver;
        if (MutationObserver == null)
            console.log("MutationObserver not available");

        // mutation observer code from https://developer.mozilla.org/en-US/docs/Web/API/MutationObserver
        var targetNode = document.getElementById('clock');

        // Options for the observer (which mutations to observe)
        var config = { attributes: true, childList: true };

        // Callback function to execute when mutations are observed
        var callback = function (mutationsList) {

            for (var mutation of mutationsList) {
                //debugger;
                //console.log(mutation);  //uncomment to see the full MutationRecord
                var shorterMutationRecord = "{ target: div#clock, newData: " + mutation.addedNodes[0].data + " }"

                console.log(shorterMutationRecord);

                var xhr = new XMLHttpRequest();
                xhr.open("POST", "http://127.0.0.1:8000");
                //xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                xhr.send(shorterMutationRecord);

            }
        };

        // Create an observer instance linked to the callback function
        var observer = new MutationObserver(callback);

        // Start observing the target node for configured mutations
        observer.observe(targetNode, config);

        // Later, you can stop observing
        //observer.disconnect();   
  }
  catch(err) {
   console.log("err.message: "+ err.message);
  }
}

Installing the Extension

In the Chrome browser navigate to chrome://extensions/. Click on "Load Unpacked" and navigate to the folder containing your two files. In my case N:\Clock Watch Chrome Extension\. Then your extension is loaded and should appear.

You can go look at the details page if you want but we are pretty much done. All you need do now is to navigate to the clock page. You'll know if you extension is loaded because a new icon appears in the top right of the Chrome browser window, on the toolbar. In the absence of a given icon, Chrome will take the first letter of your extension and use that as an icon, so below ringed in green in the "C" icon, hover over that and it will read "Clock Watch". Click on the icon and one can remove from menu if you want.

Screenshots- The Clock and the Message Queue

As highlighted in the previous post we have some code which runs a Python web server, taking HTTP POST calls and writing the payloads to a folder. Here is a screenshot to show that working

Final Thoughts

So what have we achieved here? We have a Chrome Extension which observes a page and reports the changes to a message queue by calling out with XmlHttpRequest to a Python web server. Cool but do please use responsibly.

Links

• How to install Chrome extensions manually