Auto Invoicing System

invoice.gs

function edited(e) {
  var user = Session.getActiveUser();
  Logger.log(e);
  var range = e.range;
  var source = e.source
  var range = SpreadsheetApp.getActiveRange();
  var sheet = range.getSheet();

  //end if edited info isn't what we want
  if (sheet.getName() !== 'Invoices'){
    return;
  }
  var col = range.getColumn();
  var row = range.getRow();
  if (col == 1){
    sheet.getRange(row, 1, 1, 26).setBackgroundRGB(255, 255, 255);
    return;
    
  } else if (col !== 2){
    return;
  } else {
    Logger.log('Col is 2');
  }
  
  var value = range.getValue();
  if (!value){
    return;
  }
  
  
  //reset background
  sheet.getRange(row, 1, 1, 26).setBackgroundRGB(255, 255, 255);
  var myDate = getDate();
  var firstRow = sheet.getRange(1, 1, 1, 26).getValues();
  
  //make column object for easier programming
  var columns = getColumns(firstRow);
  sheet.getRange(row, columns.mostRecent).setValue(myDate.dateAndTime).setNote(user);
  sheet.getRange(row, columns.unix).setValue(myDate.now);
  sheet.getRange(row, columns.next).setValue(myDate.now+518400000);
  var thisRow = sheet.getRange(row, 1, 1, 26).getValues()[0];
  
    var numberSent = sheet.getRange(row, columns.noticesSent).getValue();
    if (!numberSent){
      numberSent = 0;
    }
    numberSent++;
    sheet.getRange(row, columns.noticesSent).setValue(numberSent).setNote(value+' sent by '+user);
  
  
  //update sheet with sending records
  var updateCol = 26;
  for (var i = columns.notice1-1; i < thisRow.length; i++){
    if (!thisRow[i]){
      updateCol = i+1;
      break;
    }
  }
  sheet.getRange(row, updateCol).setValue(myDate.dateAndTime).setNote(value+' sent by '+user);
}

function testSubmit(){
  var e = {range: SpreadsheetApp.getActiveRange(), source: SpreadsheetApp.getActive()}
  edited(e);
}

function getDate(){  
  //get date objects and variables
  var myDate = {};
  var date = new Date();
  myDate.year = date.getYear();
  myDate.monthNumber = date.getMonth();
  var monthArray = new Array();
    monthArray[0] = "Jan";
    monthArray[1] = "Feb";
    monthArray[2] = "Mar";
    monthArray[3] = "Apr";
    monthArray[4] = "May";
    monthArray[5] = "June";
    monthArray[6] = "July";
    monthArray[7] = "Aug";
    monthArray[8] = "Sep";
    monthArray[9] = "Oct";
    monthArray[10] = "Nov";
    monthArray[11] = "Dec";
  
  var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat']
  myDate.weekday = dayArray[date.getDay()];
  myDate.month = monthArray[myDate.monthNumber];
  myDate.day = dayArray[date.getDay()]+' '+date.getDate()
  myDate.time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss");
  myDate.now = Date.now();
  myDate.dateAndTime = Utilities.formatDate(date, 'GMT+1:00', "MM/dd/yyyy HH:mm");
  myDate.origin = date;
  return myDate;
}
 
function getColumns(data){
  var columns = {};
  for (var i = 0; i < data[0].length; i++){
    if (data[0][i]){
      var name = camelize(data[0][i]);
      columns[name]=i+1;
    }
  }
  Logger.log(columns);
  return columns;
}

function camelize(str) {
  return str.replace(/(?:^\w|[A-Z]|\b\w|\s+)/g, function(match, index) {
    if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces
    return index == 0 ? match.toLowerCase() : match.toUpperCase();
  });
}

function getNeedsUpdate(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Invoices');
  var data = sheet.getDataRange().getValues();
  var myDate = getDate();
  var now = Date.now();
  var columns = getColumns(data);
  var financeEmail = 'finance@aisabuja.com';
  //create email message
  var string = 'A reminder needs to sent to parents about the following invoices:\n';
  var send = false;
  for (var i = 0; i < data.length; i++){
    //if bill is outstanding or late, get the data
    if (data[i][0] && (data[i][0] == 'outsanding' || data[i][0] == 'late')){
      var info = data[i];
      Logger.log(data[i]);
      
      //if there is no next reminder time or the next reminder time is past now
      if ((!info[columns.next-1])||(now > info[columns.next-1])){
        //action value
        sheet.getRange(i+1, 2).setValue('');
        //change background color to red
        sheet.getRange(i+1, 1, 1, 26).setBackgroundRGB(255, 204, 204);
        
        //create email message
        send = true;
        var date = Utilities.formatDate(info[columns.dateDue-1], 'GMT+1:00', "MM/dd/yyyy")
        string = string +'\n'+info[columns.name-1]+' invoice for '+info[columns.reason-1]+' due on '+date;
        
      }
    } 
  }
  
  string = string+ '\n\nYou can view information about the invoices here:\
     \nhttps://docs.google.com/spreadsheets/d/1iq_n6AzC-En7i3sjq1ElBz3PIdJMuNjd7u-NmVUczb0\n\nThanks,\nJohn Hinkle';
  
  Logger.log(string);
  //send email if changes
  if (send){
    var message = {
      to: financeEmail,
      subject: 'Invoice Reminders',
      bbc: 'john.hinkle@aisabuja.com',
      body: string
      }
    MailApp.sendEmail(message);
    }
  
}

function autoSend(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Invoices');
  var data = sheet.getDataRange().getValues();
  var myDate = getDate();
  var now = Date.now();
  var columns = getColumns(data);
  var financeEmail = 'finance@aisabuja.com';
  
  //create email message
  var string = 'A reminder needs to sent to parents about the following invoices:\n';
  var send = false;
  for (var i = 0; i < data.length; i++){
    //if auto, send mail
    if (data[i][0] && data[i][0] == 'auto'){
      
      //change bg to make it easier to find auto sending emails
      var row = i+1;
      Logger.log('Row '+row+' to send email');
      sheet.getRange(row, 1, 1, 26).setBackgroundRGB(232, 247, 145);
      
      var date = Utilities.formatDate(data[i][columns.dateDue-1], 'GMT+1:00', "MM/dd/yyyy")
      //create message
      var body = 'Our records indicate '+data[i][columns.name-1]+' has an outstanding invoice for '+data[i][columns.reason-1]+' due on '+date+'.<br>\
        You can view the invoice <a href="'+data[i][columns.invoiceUrl-1]+'">here</a>.<br><br>\
        If you feel you have gotten this message in error, please reply to this email so that we may update our records.';
      var message = {
        to: data[i][columns.email-1],
        bbc: financeEmail+',john.hinkle@aisabuja.com',
        replyTo: financeEmail,
        subject: 'Invoice Reminder',
        htmlBody: body
      }
      try{
        MailApp.sendEmail(message);
        var update = true
      }
      catch(e){
        MailApp.sendEmail('john.hinkle@aisabuja.com', 'Email Not Sent to '+data[i][columns.email-1], e)
        var update = false;
      }
      if (update){
      //update sheet with times sent
      sheet.getRange(row, columns.mostRecent).setValue(myDate.dateAndTime);
      var timesSentCell = sheet.getRange(row, columns.noticesSent);
      var count = timesSentCell.getValue();
      if (!count){
        count = 0
      }
      count++
      timesSentCell.setValue(count);
      
      
      //update sheet
      var thisRow = sheet.getRange(row, 1, 1, 26).getValues()[0];
      var updateCol = 26;
      for (var j = columns.notice1-1; j < thisRow.length; j++){
        if (!thisRow[i]){
          updateCol = j+1;
          break;
        }
      }
      sheet.getRange(row, updateCol).setValue(myDate.dateAndTime).setNote('email sent by auto emailer');
     }
    }
    }
     
  }

function autoCheck(){
  var finance = 'finance@aisabuja.com';
  MailApp.sendEmail(finance, 'Check Auto Send', 'Auto Invoicing will go out tomorrow. Please check this sheet to make sure no invoices have already been paid.\n\n\
    https://docs.google.com/spreadsheets/d/1iq_n6AzC-En7i3sjq1ElBz3PIdJMuNjd7u-NmVUczb0')
}