Turning custom Google Sheets functions into an add-on

Question

I've written three separate functions for Google Sheets. Each takes a student's email address ([email protected]) as input, where XX represents the students graduation year, and returns one of three pieces of info. respectively.

1. First Name
2. Last Name
3. Class (Freshman, Soph. Junior, or Senior)

I'd like to create an add-on that asks the user which column contains a list of student email addresses, then have it loop through that column, running each function on each student email address, separating the data into separate columns. I feel comfortable with creating menu items and assigning functions to them. I feel like I need an awesome for loop or three. And also need to build in some logic and a way for users to select which column has email addresses. Below is the code for each of my functions. Any help would be greatly appreciated!

   //Takes a student's email address and returns their proper first name. 
function FNAME(input) {
  var length = input.length;
  var shortened = input.substring(0, length-11);
  var replaceSpace = shortened.replace('.', ' ');
  var spacePlace = replaceSpace.indexOf(" ");
  var fName = replaceSpace.charAt(0).toUpperCase() + replaceSpace.slice(1, spacePlace);
  return fName;

}

//Takes a student's email address and returns their proper last name. 
function LNAME(input) {
  var length = input.length;
  var shortened = input.substring(0, length-11);
  var replaceSpace = shortened.replace('.', ' ');
  var spacePlace = replaceSpace.indexOf(" ");
  var lName = replaceSpace.charAt(spacePlace+1).toUpperCase() + replaceSpace.slice(spacePlace+2);
  return lName;

}

//Takes a student's email address and returns their class.
function GLEVEL(input) {
  var currentTime = new Date();
  var length = input.length;
  var shortened = input.substring(0, length-9);
  var newLength = shortened.length;
  var gyear = input.substring(newLength-2, newLength);
  var year = currentTime.getYear();
  var month = currentTime.getMonth();

  if (year - gyear == 2000 && month <= 6) {
    gyear = "Senior";
  }
  else if (year - gyear == 1999 && month >= 7) {
    gyear = "Senior";
  }

  if (year - gyear == 1999 && month <= 6){
    gyear = "Junior";
  }
  else if (year - gyear == 1998 && month >= 7) {
    gyear = "Junior";
  }

  if (year - gyear == 1998 && month <= 6){
    gyear = "Sophomore"
  }
  else if (year - gyear == 1997 && month >= 7) {
    gyear = "Sophomore";
  }

  if (year - gyear == 1997 && month <= 6){
    gyear = "Freshman";
  }
  else if (year - gyear == 1996 && month >= 7) {
    gyear = "Freshman";
  }
  return gyear;  

}

Answer 1

There is the:

getColumn()

method of the Range Class.

Google Documentation - getColumn

function askUserToSelectColumn() {

  var thisSS = SpreadsheetApp.getActiveSpreadsheet();

  Browser.msgBox('Please click anywhere in the column that has the emails.  then click OK');

  var activeColumn = thisSS.getActiveCell().getColumn();

  var runCheck = Browser.msgBox('The active Column is Column x.  Run Program?', Browser.Buttons.OK_CANCEL);

  if (runCheck === 'ok') {
    loopThroughTheColumn(activeColumn); //Trigger function to run and pass the column number
  };  
};

Function to process the column:

function loopThroughTheColumn(argWhatColumn) {
  Logger.log('argWhatColumn: ' + argWhatColumn);
  var thisSS = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = thisSS.getSheetByName('theSheetNameToGet');

  //getRange(row, column, numRows, numColumns)
  var columnData = theSheet.getRange(2, argWhatColumn, 10, 1);
  Logger.log('columnData: ' + columnData);

  for (i=0;i<columnData.length;i++) {
    //Code for each row in the column
  };
};

Note the use of the Logger.log() statements. The Logger.log() statement prints information to the LOGS, which you can then see from the VIEW, LOGS menu.

Also, learn how to use the debugger.

Debugger and Breakpoints