Skip to content

Instantly share code, notes, and snippets.

@hinklefoxmail hinklefoxmail/invoice.gs Secret
Created Sep 3, 2017

Embed
What would you like to do?
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
You can’t perform that action at this time.