Category Archives: thirdspace

Outsmarting Google: Generating Download Links with Google App Script

Outsmarting Google: Generating Download Links with Google App Script

For the most part, I love working with Google App Script. The APIs are what you expect them to be. Most of the features are well-documented. Heck, I’ve even tried to build Google Sheets into a small relational database.

But after you’ve been around the block for awhile, you realize there is this odd black market of sorts built into Google App Script and the associated Drive services, things you can do that Google never really meant for you to do, or built in as a feature at some point but forgot about.

This post exposes one of those dirty back alleys you need to generate a download link for Google Documents.

The Scenario

In reality, this should have been a straightforward process. We were trying to loop through a directory structure and print out some data about each file into a Google Sheet. The Google Sheet would then just serve some JSON that a little front end app could consume to allow people to download, copy, or view Google Drive files in a custom way.

All of the looping part worked as expected, but for some reason a previous version of the download link was no longer working.

function execute(){
  //This is the top level folder
  var folderId = "FOLDER_ID_HERE"; 
  var folder = DriveApp.getFolderById(folderId);
  var sheet = SpreadsheetApp.getActiveSheet();
  //Append Headers to Sheet
  sheet.appendRow(["File Name", "Parent Folder Name", "URL", "Download Link", "Copy Link"]);
  //Call function to recurse through subfolders
  loopSubFolders(folder, sheet); 

function loopSubFolders(parentFolder, sheet){
  var subFolders = parentFolder.getFolders(); 
  listFilesInFolder(, sheet); 
    listFilesInFolder(, sheet); 

function listFilesInFolder(folder, sheet) {
//writes the headers for the spreadsheet
    var contents = folder.getFiles();  
    var cnt = 0;
    var file;

    while (contents.hasNext()) {
        var file =;
// writes the various chunks to the spreadsheet- just delete anything you don't want
            data = [
                file.getUrl().split('/edit')[0] + '/export?format=docx', 
                file.getUrl().split('/edit')[0] + '/copy'




Things started to get tricky when logging out the download URL. Google Apps Script makes it easy get the file URL using getUrl, but that is just a link to view the document. After some research, it seemed like getDownloadUrl might do the trick, but alas that didn’t work for any of the Google docs or the random files in the folder.

At some point in this process, I also decided it was a good idea to print out the files as binary blobs in the spreadsheet, which summarily crashed the sheet.

We were able to find some older tutorials that broke down some various link structures, but none of those seemed to work for the current Google Drive setup. At the end of the day, we kind of just started trying things based off the structure of the /copy link until we found something that stuck.

However, this only seems to work for native Google Drive content types, and it needs an export format or otherwise the documents were downloading as HTML. Either way, bound to be useful to someone.

The post Outsmarting Google: Generating Download Links with Google App Script appeared first on Jeff Everhart.

Weekly Web Harvest for 2017-10-08

  • youtube-dl/ at master · rg3/youtube-dl

    download youtube videos with many options from terminal

    h/t Wes Bos

  • Typography in ten minutes | Butterick’s Practical Typography

    : if you learn and fol­low these five ty­pog­ra­phy rules, you will be a bet­ter ty­pog­ra­pher than 95% of pro­fes­sional writ­ers and 70% of pro­fes­sional de­sign­ers. (The rest of this book will raise you to the 99th per­centile in both categories.)

  • Pollen: the book is a program

    Pollen is a publishing system that helps authors make functional and beautiful digital books.

  • The Bet I Made with Teachers All Around the United States Last Year – dy/dan

    My point in all of this is that math teachers have names for their numbers, much in the same way that ornithologists have names for their birds. And much in the same way that ornithologists haven’t given me a reason to care about the difference between a Woodlark and a Skylark, math teachers often fail to motivate the difference between rational numbers and integers and whole numbers and imaginary numbers and supernatural numbers.

    The difference is that ornithology isn’t a course that’s required for high school graduation and university enrollment and labor market participation. Kids aren’t forced to study ornithology for twelve years of their childhood.

    So I’m inviting us to ask ourselves: “Why did we invent these categories of numbers?” And if we agree that it was to more effectively communicate about numbers, we need to put students in a place where their communication suffers without those categories. If we can’t, then we should confess those categories are vanity.

  • Nolitory
  • Carol Black on Twitter: “THREAD How kids learn without your “feedback:””

Remove H1 Option from WordPress TinyMCE Editor

 *  Remove the h1 tag from the WordPress editor.
 *  @param   array  $settings  The array of editor settings
 *  @return  array             The modified edit settings

function my_format_TinyMCE( $in ) {
        $in['block_formats'] = "Paragraph=p; Heading 2=h2; Heading 3=h3; Heading 4=h4; Heading 5=h5; Heading 6=h6;Preformatted=pre";
    return $in;
add_filter( 'tiny_mce_before_init', 'my_format_TinyMCE' );

In terms of basic accessibility guidelines each page should have only one H1 element. In WordPress that’s usually built into the template and is usually the title of the post or page.

I was thinking about that last night and wondered if we should just remove it as an option from the TinyMCE editor in WordPress. Removing options goes against my typical stance but I couldn’t think of any scenarios that demanded an H1 element but plenty of scenarios where offering it seemed like it’d create confusion.

A brief perusal of the WordPress Codex’s TinyMCE section led to the code above. I put it in our generic network-activated plugin for Rampages. Next steps could include integrating a filter in kses to remove/replace H1 elements with H2 elements but that’ll wait for now as it seems a chunk more aggressive.

Conditional Content Display in Gravity Forms Templates

I don’t know when the conditional shortcode was added or if it has always existed and I was just unaware . . . but the ability to use a shortcode wrapper to show/not show portions of content in blog posts created via Gravity Forms is all kinds of handy.

As a quick example, the following content would not show if the entry to the form field {What’s up?:1} was empty.

[gravityforms action="conditional" merge_tag="{What's up?:1}" condition="isnot" value=""]
<h2>{What's up?:1} - more words</h2>[/gravityforms]

In the past, if we didn’t have the wrapper and we had no response on the ‘What’s up?’ field we’d be left with the awkward ‘ – more words’ being there.

This is so very nice for optional content where you might want to add additional html structure — like a link to an attachment but *only* if the attachment exists. It solves so very many problems so easily.

You could also display different additional HTML elements based on the response to specific field entries. Like if a ranking on a scale of 1-5 is a 5 show a particular overly happy gif.

[gravityforms action="conditional" merge_tag="{How happy?:1}" condition="is" value="5"]
<img src="" alt="a very happy turtle">

Weekly Web Harvest for 2017-10-01

  • This band used Facebook Live’s lag to loop their song / Boing Boing

    We rearranged each instrument on “Bear Claws” to fit Facebook Live’s delay, with each loop getting more complex, adding instruments, rhythms, and melodies. Additionally, by projecting the video live from a soundstage we created an infinite tunnel consisting of all the previously recorded loops.

  • Is the Wolf a Real American Hero? – The New York Times

    This story — that wolves fixed a broken Yellowstone by killing and frightening elk — is one of ecology’s most famous. It’s the classic example of what’s called a “trophic cascade,” and has appeared in textbooks, on National Geographic centerfolds and in this newspaper. Americans may know this story better than any other from ecology, and its grip on our imagination is one of the field’s proudest contributions to wildlife conservation. But there is a problem with the story: It’s not true.

  • Hundreds of White House emails sent to third Kushner family account – POLITICO

    some security measures were taken when it was installed

  • BBC – Future – The deadly germ warfare island abandoned by the Soviets

    A year later, the corpses of two missing fishermen were found nearby, drifting in their boat. It’s thought that they had caught the plague. Not long afterwards, locals started landing whole nets of dead fish. No one knows why. Then in May 1988, 50,000 saiga antelope which had been grazing on a nearby steppe dropped dead – in the space of an hour.



In this post, I will be lazily drawing a connection from the Past to the Present using the discovery of Ancient Browser Bookmarks.

I don’t know how many of you follow the habit of always importing your old web bookmarks/favorites from browser to browser, year to year, for seemingly forever. I predict that many of you have since it’s the default behavior of most browsers. You just go with the flow, like me.

If you do, you’ll be glad you did! (another Past/Present wordplay there..)

(The ancient Netscape, FTW)

I found many such gems, but one in particular I will discuss here, because it’s oddly amusing.

Inside one of my old pixel-dusted bookmark folders labeled innocently enough ‘Personal Interest‘, there was a folder named ‘Architecture‘– a long lost interest of mine.

Inside that, there was only one bookmark – so it must have been a really good one! A real treasure, perhaps. I was especially excited after I noticed I had previously re-named the bookmark to read STEVE HOLT! ARCHITECTURE

Bookmarking the Future INSTITUTE FOR CONTEMPORARY ART, VCU I laughed at my cheeky former self.

The website was actually for Steven HOLL Architecture. Seeing that, I somewhat disappointedly clicked around trying to figure out why I had saved this many years ago – probably around the earliy 2000s based on the AD reference. So what was it that made me bookmark this? I have no idea. It was a nicely designed site now. Maybe it was then, too. Maybe that was all it was.

As I clicked around the site some more, I discovered a World Map section. I clicked it. Seeing the world map with circles around the world, I noticed some over North America. I began to zoom in. And zoom in some more.


It seemed RVA was squarely in one of the circles. Indeed it was!

So I clicked the info circle. Behold! It was for the new ICA of VCU!

Eureka! My past self knew I was going to work at VCU one day, exactly in the same year that HOLL architecture would be building their beautiful piece of architecture! Fascinating!! How did my clever past self know?!

OK. That’s some lazy connecting of the dots with 20/20 hindsight, but it’s still fun.

Now you try it.

Look if you have a collection of old, forgotten bookmarks Bookmarking the Future INSTITUTE FOR CONTEMPORARY ART, VCU that your past self once deemed absolutely worthy of saving, but your present self could seemingly care less about (considering you never visit them anymore.)

I’m sure you can find something. Aren’t you curious to see why your former self once cherished it so? Maybe, just maybe,  you’ll find a hidden message from your former past self, too…

WordPress Plugin Health Dashboard (Early POC)

In trying to get a better handle on how we’re going to manage our plugins in the future, I found the API for the plugins data. Not like they were hiding it but I’d not seen it before.Consider me Columbus
absent the genocide, slavery, and other terrible things.
Replacing *slug* in the following URL gives you a pretty robust javascript

With that option, I could build a little Google Sheet/Scripts viewer that would look up information based on a list of plugin slugs. I could set conditional formatting to do various things for visual cues . . . I could even build a little mini-algorithm to evaluate different aspects and weight them towards a total plugin score.

I’m debating whether it’s worth working into a more sophisticated plugin that will tell me how many sites the plugin is installed on, display that data etc. There are plugins like that out there but they die on large multisite installations.1 I’d also like some way to tie into the vulnerable plugin announcements.

Here are the two little functions that look up and write the plugin’s health based on the slugs being written in column A and a header in row 1.

function writeData(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = spreadsheet.getActiveSheet();
  var lastRow = ss.getLastRow();
  var range = ss.getRange('A2:A' + lastRow);
  var slugs = range.getValues();
  for (i in slugs){
   var len = 2+Number(i);
   getData(slugs[i],ss, len); 

function getData(slug,ss,len) {
 var url  = '' + slug + '.json';
 var  result = UrlFetchApp.fetch(url);
    if (result.getResponseCode() == 200) {
        var json = JSON.parse(result.getContentText());
        var version = json.version;
        var updated = json.last_updated;
        var tested = json.tested;
        var rating = json.rating;
        var values = [[version,updated,tested,rating]];        
        var range = ss.getRange('B'+ len + ':E'+ len);

1 I have a functional, if crude, option that paginates through them in a way that doesn’t crash/die.

Analyzing and Visualizing Networks

One of the current projects I’m working on involves building out some analytical tools that sit on top of an application that lets students track attendance at extra curricular events for a living and learning program for the daVinci Center. For most of the visualizations, I used amCharts to build out some nice looking and functional charts, but since this data set is pretty unique, I also wanted to explore some of the unique information available that other analytical tools might ignore.

After working together a pretty gnarly SQL query to expose all of the student attendance data through the WordPress REST API, I settled on creating a network graph that shows all of the co-attendance between the students at all of the events.

See the Pen Student Network Analysis by Jeff Everhart (@JEverhart383) on CodePen.

Overall, I was really happy with how this turned out for a few reasons. One of the key tenants of the da Vinci model and its living and learning programs is that the cross-pollination of ideas across disciplines is what leads to innovation.

While the program is open to anyone, at least to my knowledge, they focus on getting students from Business, Engineering, and the Arts to work together. Thus, by encoding those groups in the network graph by using color, we should be able to literally visualize the ways in which students are interacting across those boundaries.

More importantly, it can also help very quickly identify outliers that might not immediately be apparent in other forms, which we can see by the pair of students off in the corner. In a program where collaboration is encouraged, it might be worthwhile to check in on these folks to see what’s up.

Notable Algorithms and Such

Part of the reason that I wanted to do this visualization in the first place was because I’d never written code to piece together the nodes and edges of the network before. As with most things, I decided not to consult the oracles on StackOverflow immediately, and am happy to say that I came up with a working implementation without copying anything from anyone else.

Since this was a undirected graph, meaning there is no directionality associated with the links or edges between nodes, I needed to capture each unique occurrence of a pair of students attending the same event.

Here is what each attendance record looked like in simplified form:

{eventID:511, userEmail:"", ...}

And here is what the finished data structure looked like before feeding it into D3:

let network = {
    "nodes": [{
        "id": "", 
        "group": "Humanities & Sciences"
    "links": [{

We have a network object, with properties for the nodes and links that both contain array of different objects. Each link object contains a source, target, and value. Since this is undirected, the source and target are sort of arbitrary, and the value specifies the number of times that those two people attended the same event. The value count was integral in helping to weight the links on the force directed graph so that students with more co-attendances are linked more tightly together.

At present, we have only about 50 or so records, but that number will easily quadruple by the end of the semester, so I was interested in making the code used to construct this network diagram as efficient as possible.

In the end, there is one section that amounts to O(n^2) runtime, but I was able to prevent a lot of loops within loops by making using of hash tables, or just plain old objects in JavaScript as my in between data structures.

If you’re interested in looking at that code, you can take a look at the source on GitHub.


The post Analyzing and Visualizing Networks appeared first on Jeff Everhart.

Javascript for Added Accessibility

Imagine you’ve got a legacy WordPress site built using one of those drag/drop themes. Now complicate it by having a chunk of content created by a non-standard javascript-based slider plugin.

Continue to imagine you have a very, very short window of time to bring it up to WCAG2.0 accessibility standards.1

Child themes aren’t a viable option because of the complexity and the tools that built this mess don’t even pretend to allow you to address most of the concerns.

Enter our good friend javascript.

I can now take our empty section elements and give them aria2 labels as Site Improve demands.

jQuery(document).ready(function() { 
     var sections = document.getElementsByTagName('section');//gets all the sections
    var labels = ['general-navigation','logo','navigation','row','main slides','purpose','sub navigation','online at vcu section','online at vcu ad', 'connect with vcu', 'connect with vcu header','twitter posts','blog area','blog posts','footer','footer address details'];//these label the section names in order
    for (i = 0; i < sections.length; i++){
      sections[i].setAttribute('aria-label', labels[i] );//applies the label names to each section

One of the slider plugins was also attaching the same ID in two places. The weird one was on the HTML tag at the top of the document. That threw errors and was weird but it was also applied via javascript after the page finished loading so it was harder to get at. Awkward but apparently functional.

function delayedRemoval() {
  timeoutID = window.setTimeout(removeId, 800);//delays things so the other js finishes then removes it

function removeId(){
var zapIt = document.documentElement;//how you get the html tag
var zapped = zapIt.removeAttribute("id");//remove the ID

1 As interpreted by Site Improve . . . and yes it should have been there already but interpretation of WAVE vs Site Improve has some significant differences.

2 I’m pronouncing this like the opera solos but it’s actually stands for Accessible Rich Internet Applications

Weekly Web Harvest for 2017-09-24

  • BBC – Future – The deadly germ warfare island abandoned by the Soviets

    A year later, the corpses of two missing fishermen were found nearby, drifting in their boat. It’s thought that they had caught the plague. Not long afterwards, locals started landing whole nets of dead fish. No one knows why. Then in May 1988, 50,000 saiga antelope which had been grazing on a nearby steppe dropped dead – in the space of an hour.

  • BardBots — Babble Lab

    BardBots is a project in which students are introduced to key concepts in computational thinking via an unlikely combination: Shakespeare and robots.  We believe that the distance between the humanities, the arts, and computer science is not as wide as it appears.

  • Poetic Computation: Reader

    There is something poetic about code itself, the way that syntax works, the way that repetitions work, and the way that instruction becomes execution through abstraction.

    h/t Jon Becker

  • The Schank Academy: Cyber Attack Academy

    Is the Cyber Attack Academy right for you?

  • The Self Driving Car Whiz Who Fell from Grace | WIRED

    In September 2015, the multi-millionaire engineer at the heart of the patent and trade secrets lawsuit between Uber and Waymo, Google’s self-driving car company, founded a religious organization called Way of the Future. Its purpose, according to previously unreported state filings, is nothing less than to “develop and promote the realization of a Godhead based on Artificial Intelligence.”

  • Idle Words

    The implication is clear: home cooks are being radicalized by the site’s recommendation algorithm to abandon their corned beef in favor of shrapnel-packed homemade bombs. And more ominously, enough people must be buying these bomb parts on Amazon for the algorithm to have noticed the correlations, and begin making its dark suggestions.

  • Carbon

    pretty source code tweeted

  • How I Make Explorable Explanations

    However you choose to do it, you’ve got to make your reader / viewer / player curious – you’ve got to make them love your question.

  • Holacracy – Flattening the Organization Structure and Busting Bureaucracy

Contact us

Academic Learning Transformation Lab - ALT Lab
1000 Floyd Ave, Suite 4102 | Richmond, Virginia 23284 | 804-827-5181

Last updated: September 26, 2017

Virginia Commonwealth University