fileSearchAddLink/Code.gs at master · exmelendez/fileSearchAddLink · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Test Links')
    .addItem('Create Links', 'showDialog')
    .addToUi();
}

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('index')
                        .setWidth(200)
                        .setHeight(100);

  SpreadsheetApp.getUi().showModalDialog(html, 'Insert State Test Links');
}

var gDriveEla = DriveApp.getFoldersByName("Coding")
                        .next()
                        .getFoldersByName("State Testing Links")
                        .next()
                        .getFoldersByName("ELA")
                        .next();

var gDriveMath = DriveApp.getFoldersByName("Coding")
                         .next()
                         .getFoldersByName("State Testing Links")
                         .next()
                         .getFoldersByName("MATH")
                         .next();

function createTestLink(){

  var gSheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = gSheet.getActiveSheet();
  var activeSheet = ss.getSheetName();
  var currentRow = ss.getCurrentCell().getRow();
  var lastRow = ss.getLastRow();
  var currentCol = ss.getCurrentCell().getColumn();
  var previousCol = currentCol - 1;

  for(var i = currentRow; i < lastRow+1; i++){
    var cellContent = ss.getRange(i, previousCol).getValue();
    var cell = ss.getRange(i, currentCol);

    if (cellContent  === "" || cellContent === null) {
      cell.setValue("Cell Empty");
      cell.setFontColor("red");

    } else {

      var fileExists = filePresent(cellContent, activeSheet);

      if(fileExists) {
        var fileURL = getURL(cellContent, activeSheet);
        cell.setValue(fileURL);

      } else {
        cell.setValue("File Not Present");
        cell.setFontColor("red");
      }
    }
  }
  gSheet.toast("Link Process Complete");
}

//Returns boolean if parameter passed combined with the .tif extension file type is found in the specified folder
function filePresent(cellContent, subject) {

  if(subject === "ELA"){
    return gDriveEla.getFilesByName(cellContent + ".tif")
                 .hasNext();

  } else if (subject === "MATH") {
        return gDriveMath.getFilesByName(cellContent + ".tif")
                        .hasNext();
  }
}

//Returns URL string of the parameter entered with the extension .tif
function getURL(cellContent, subject){

  if(subject === "ELA"){
    return gDriveEla.getFilesByName(cellContent + ".tif")
                 .next()
                 .getUrl();

  } else if (subject === "MATH") {
    return gDriveMath.getFilesByName(cellContent + ".tif")
                     .next()
                     .getUrl();
  }
}