How I track internship applications

Fri Oct 9 2020

Since internships and post-grad job applications are opening up and interviews are starting soon I wanted a way to track the status of everywhere I applied. I had a saved template I found on r/cscareerquestions a few months ago but it was pretty bare-bones for what I wanted.

So, I added to the original template a custom function that counts highlighted cells (or rows) so that the status table would update automatically. I also added a few columns in order to add details such as a contact at the company and function to count the total number of applications. So far, this has been a huge help in staying organized and knowing what companies I'm still waiting to hear back from.

If you want to use this template just press Make a Copy under File.

The status table does need to be refreshed, in order to update, as you highlight rows (since it is a custom function). This can be done by simply unchecking and rechecking the refresh button.

For those interested: the function is just a script that counts the number of rows that are highlighted in certain colors.

function countColoredCells(countRange,colorRef,rangeSum) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var range = activeSheet.getRange(countRange);
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();

  var bg = range.getBackgrounds();  
  var colorCell = activeSheet.getRange(colorRef);
  var color = colorCell.getBackground();

  var count = 0;

  for(var i = 0; i < rangeHeight; i++)
    for(var j = 0; j < rangeWidth; j++)
      if( bg[i][j] == color )
        count = count+1;

  return count;
};

If you just want the script to make your own template or for other use cases you can find it in the script editor under Tools. I got this script from Prolific Oaktree's Video