Auto Invoicing System
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') | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment