Folder Notification

FOLDER_ID = "0Bz-U3E0jxSujdENGdWVpeHRNMkk" //This is the folder ID
SPREADSHEET_ID = "13gES4Jgu1TCdbYZ4kR3f0rtuNZKNlGvL4TZ7ieVjJWI" //This is the spreadsheet ID

SHEET_FILES_NAME = "Files"
SHEET_EMAIL_NAME = "Emails"

EMAIL_TEXT_HTML = "<p> Hi %name%, <br><br>\
%filename% has been added to the minutes folder. It can be accessed <a href=\"%filelink%\">here</a>. <br><br>\
If previous minutes are needed, you can access them <a href=\"%folderlink%\">here</a>.</p>"

EMAIL_TEXT_RAW = "Hi %name%, \n %filename% has been added to the minutes folder. It can be accessed at %filelink% \n If previous minutes are needed, you can access them at %folderlink%"

function checkFiles() {
  fileTableReset() //Remove all of the "y"s in the "exists" folder, so they can be re-added

  var folder = DriveApp.getFolderById(FOLDER_ID) //Get the folder name and the files inside it
  var files = folder.getFiles()

  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID)
  var sheetFiles = spreadsheet.getSheetByName(SHEET_FILES_NAME) //Open the spreadsheet's sheet show

  while (files.hasNext()) {
    var file = files.next() //Go to the next file

    var fileIDRow = fileIdAlreadyExists(file.getId()) //Get the row no. where the file is held

    if (fileIDRow == -1) //If the file isn't in the spreadsheet
    {
      Logger.log("Not found in sheet: File ID "+file.getId()+", Sending email")
      sendNewFileEmail(file, folder) //Send email out
      sheetFiles.appendRow([file.getId(), "y"]) //Add it to the spreadsheet
    }
    else
    {
      Logger.log("Found: File Name "+file.getName()+", Not sending email")
      sheetFiles.getRange(fileIDRow, 2).setValue("y") //mark the ID as existing
    }
  }

  fileTableTidy() //Tidy up any deleted files (ones without "y"s next to the id.
}

function fileTableTidy() { //Tidy up any deleted files (ones without "y"s next to the id.

  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID)
  var sheetFiles = spreadsheet.getSheetByName(SHEET_FILES_NAME) //Open files sheet

  columns = sheetFiles.getRange(2,2,sheetFiles.getLastRow()-2).getValues()//Get all of the files stored

  for (var i = 0; i < columns.length; i++) {
    if (columns[i] != "y") { //If the row doesn't have a y (if it doesn't exist)
      sheetFiles.deleteRow(i+2) //Delete the row
    }
  }
}

function sendNewFileEmail(file, parentFolder) { //Send the notification email to the group

  Logger.log("Send email for file " + file.getName())

  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID)
  var sheetEmails = spreadsheet.getSheetByName(SHEET_EMAIL_NAME) //Open the emails spreadsheet

  data = sheetEmails.getRange(2,1,sheetEmails.getLastRow()-1,2).getValues() //Download a 2-dimension array of all of the emails and preferred names

  for (var i = 0; i < data.length; i++) {

    var bodyHtml = EMAIL_TEXT_HTML //Get email template

    bodyHtml = bodyHtml.replace("%name%", escapeHtml(data[i][1]))
    bodyHtml = bodyHtml.replace("%filename%", escapeHtml(file.getName()))
    bodyHtml = bodyHtml.replace("%filelink%", escapeHtml(file.getUrl()))
    bodyHtml = bodyHtml.replace("%folderlink%", escapeHtml(parentFolder.getUrl())) //Replace the placeholders in the template

    var bodyRaw = EMAIL_TEXT_RAW //Get email template

    bodyRaw = bodyRaw.replace("%name%", data[i][1])
    bodyRaw = bodyRaw.replace("%filename%", file.getName())
    bodyRaw = bodyRaw.replace("%filelink%", file.getUrl())
    bodyRaw = bodyRaw.replace("%folderlink%", parentFolder.getUrl()) //Replace the placeholders in the template

    MailApp.sendEmail(data[i][0], "New Minutes: " + file.getName() , bodyRaw, {htmlBody: bodyHtml}) //Send the email
  }
}

function fileTableReset() { //Gets rid of all of the "y"s in the "Exists?" Column.

  var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_FILES_NAME)

  sheet.getRange("B2:B").clear() //Clear all of the cells in that area (where the "y"s are.
}

function fileIdAlreadyExists(fileId) { //Check if file exists in spreadsheet and if so, where it is
  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID)
  var sheetFiles = spreadsheet.getSheetByName(SHEET_FILES_NAME) //open the spreadsheet

  columns = sheetFiles.getRange(2,1,sheetFiles.getLastRow()).getValues() //get a 1D array of the file IDs

  for (var i = 0; i < columns.length; i++) {
    if (columns[i] == fileId) {
      return i+2 //if it is there, return the row number (+1 for the top row, +1 for converting from the 0 index of array to 1 index of spreadsheet)
    }
  }
  return -1 //If not there, return -1
}

function escapeHtml(unsafe) {
    return unsafe
         .replace(/&/g, "&amp;")
         .replace(/</g, "&lt;")
         .replace(/>/g, "&gt;")
         .replace(/"/g, "&quot;")
         .replace(/'/g, "&#039;");
 }