flickr photo shared by The National Archives UK with no copyright restriction (Flickr Commons)

Suppose you wanted to automate a chunk of your CV creation. Suppose they’d let you do it digitally via Google Docs (if not aspects of this could still work but it wouldn’t be nearly as interesting) and that you’d like to link to the “proof” files.

I am further supposing that you might be willing to think about doing this slightly differently.

Usually people build the CV/tenure document and then go back and find/link to their evidence. The path I’m suggesting would allow you to gather the evidence as you came across it and then build the index to it automatically. You’ll still want to construct the overarching narrative but this takes the grunt work of listing/linking and puts it on the computer where it belongs.1

This is the proof of concept scenario. You could make it much better depending on your needs/wants but this ought to get you started with how it could work. This script does create a spreadsheet of all your content with a variety of useful links and creates a Google document with all the files as ordered list items under their respective folder headings.2

Given one folder called CV POC . . . in that folder are your three folders of goodness . . . research, teaching, and service.

You’ve filled these folders with PDFs, Word files, images, or whatever of the proof that you are worthy of tenure/promotion/deity-status.3

Add a Google Sheet and attach the script below to it by going to Tools>Script Editor and pasting the contents in replacing everything.

You’ll save it and then hit the play button. You’ll need to approve some stuff. Once approved . . . you’ll get the output in a tidy little Google Doc with working links etc. like the one below.

Notice the script does a few things that are dependent on how the file names were written. It replaces underscores with spaces, chops off the file extension . . . stuff like that. It was more done for my own amusement and to show what might be done that for an actual logical purpose.

function listFolders() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  
  var driveFile = DriveApp.getFileById(ssId); 
 
  var parentFolder = driveFile.getParents();
  var folder = DriveApp.getFolderById(parentFolder.next().getId()); //ASSUMES one parent folder, you could always hard code it in
  
  var doc = DocumentApp.create('Tom\'s CV Demo'); //names the document
   var body = doc.getBody();    
   body.insertParagraph(0, doc.getName()).setHeading(DocumentApp.ParagraphHeading.HEADING1); //adds document headers
  
 
  ss.appendRow(["Name", "Folder", "Date", "Size", "URL", "Download", "Description", "Type"]);//write the spreadsheet headers
   
  var subfolders = folder.getFolders(); //processes through all the files
 
    while (subfolders.hasNext()) {
      var name = subfolders.next();
      var files = name.getFiles();
      
      var style = {};   //sets style and writes headers to the document    
        style[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
        style[DocumentApp.Attribute.FONT_SIZE] = 18;
        style[DocumentApp.Attribute.BOLD] = true; 
      var header1 = body.appendParagraph(name);
      header1.setAttributes(style);  //formats the document headers

      while (files.hasNext()) {
        var file = files.next();
        
        data = [
          file.getName(),
          name, //folder name
          file.getDateCreated(),
          file.getSize(),
          file.getUrl(),
          "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
          file.getDescription(),
          file.getMimeType(),
          "=sum(4+3)", //just curious if this would work and it does display 7 and put the formula in the cell
        ];
        
     ss.appendRow(data);  //write to the spreadsheet              
     
     //sets styles for file links   
     var listStyle = {}; //sets styles and writes data to the file lists 
        listStyle[DocumentApp.Attribute.FONT_SIZE] = 12;
        listStyle[DocumentApp.Attribute.BOLD] = false;               
        listStyle[DocumentApp.Attribute.UNDERLINE] = false; //undoes link underlining
        listStyle[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000'; //changes them back to black from blue       
        
        
   
     var prettyFile = file.getName().replace(/_/g, ' '); //gets rid of underscores
     var prettyLen = prettyFile.length-4;  //gets length so you can chop off the file extension 
     var item1 = body.appendListItem(capitalizeFirstLetter(prettyFile.substring(0,(prettyLen)))); //this is bad form 
        item1.setLinkUrl(file.getUrl()); //adds the link to the file name in the document
        item1.setAttributes(listStyle);  //sets the visual style created above      
      };
    
  };
 

 }   

// I was lazy and used this from http://stackoverflow.com/questions/1026069/capitalize-the-first-letter-of-string-in-javascript
function capitalizeFirstLetter(string) {
    return string.charAt(0).toUpperCase() + string.slice(1);
}

1 Thanks to David C. for helping me articulate that difference.

2 This currently gets made on the root of your document rather than in the POC folder. I could move it but that feels clumsy. I can’t quite remember how I’ve generated files within particular folders in the past.

3 In my case, I used PDFs I had handy from other projects. They are clearly not my works.

3 thoughts on “Google Script CV Workflow POC

  1. Hey there,

    this could be a great start of doing something similar to “join a huge amount of documents in a google doc folder (that are linked to each other) into a single pdf.
    Do you have this code under some version control?
    Do you want to put some license on it, so people know under which circumstances they can reuse and modify it?
    http://choosealicense.com/ could be a start if you have no idea about that topic.

    If you don’t want to think about licenses, I will just ask for the permission to reuse and modify the script for the project that i have in mind, which is the “New Future Process” of the organization called CNVC (http://www.cnvc.org/future).
    Feel free to answer via mail

    Best Karfau

Leave a Reply