var yesArr = ["A155","AC10","AC25","NGCE","H2","H3","H6","H8","H12","H44","H56","_","DD","1P","2AC","2L","2P","31P","3AC","3L","4AC","4L","4P","5AC","5L","6L","7L","8L","8P","BGP","CDD","CPP","DHCP","ESXTP","IAP","IP","IPL","MCELL","MOIP","MT","NDD","PDD","PP","SIP","TP","VDD","VOIP","WLL","_DD","PAA","V5C","V5H","VAA","VAD","VDA","DDD","10E","10GE","15E","1HM","1ME","20E","25E","2HM","2HX","2ME","30E","35E","3HM","3HX","3ME","40E","45E","4HM","4HX","4ME","50E","5HM","5HX","5ME","6HX","75E","7HX","80E","8HX","HM","1IM","2IM","2MX","3IM","4IM","5MX","7IM","CETH","CETH2","ETH2","IM","IM1","IM2","IML","IMS","RDD","STM","XMX","NGCETH","DAA","HAA","GWM"];
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({ name: "Check Backup", functionName: "checkBackup" });
  menuEntries.push(null);
  menuEntries.push({ name: "Get Topological Links", functionName: "getLink" });
  ss.addMenu("Commands", menuEntries);
}

function checkBackup() {
  var backupCol = 9
  var dataStartRow = 2
  var row;
  var searchString;
  var textfinder;
  var searchResults;
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("input");
  var dataLastRow = dataSheet.getLastRow();
  dataSheet.getRange(1,backupCol).setValue("Backup");
  dataSheet.getRange(dataStartRow, backupCol, dataLastRow).setValue("NO");
  for (var i = 0; i < yesArr.length; i++) {
    searchString = yesArr[i];
    textfinder = dataSheet.createTextFinder(searchString);
    searchResults = textfinder.findAll();
    for (var x = 0; x < searchResults.length-1; x++) {
      row = searchResults[x].getRow();
      dataSheet.getRange(row, backupCol).setValue("YES");
    }
  }
}

function getLink() {
  var dependCol = 12;
  var dataStartRow = 2;
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("input");
  var data2Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("input2");
  var dataLastRow = dataSheet.getLastRow();
  data2Sheet.deleteColumn(3);
  var searchString;
  var textfinder;
  var row
  var searchResults;
  var dependTop;
  var dupeflag;
  var x;
  var doneArr = [];
  dataSheet.getRange(1,10).setValue("Topological Link");
  for (var i = dataStartRow; i < dataLastRow+1; i++) {
    searchString = dataSheet.getRange(i,4).getValue();
    for (x = 0; x < doneArr.length; x++) {
      if (doneArr[x][0]==searchString) {
        dupeflag = true;
        break;
      }
    }
    if (dupeflag) {
      dataSheet.getRange(i,10).setValue(doneArr[x][1])
    }
    else {
      textfinder = data2Sheet.createTextFinder(searchString);
      searchResults = textfinder.findNext();
      row = searchResults.getRow();
      dependTop = data2Sheet.getRange(row,dependCol).getValue();
      dataSheet.getRange(i,10).setValue(dependTop);
      doneArr.push([searchString,dependTop]);
    }
    dupeflag = false;
  }
}