Google Script - Automatic Invoice Numbering

Another foray into creating a script to help make a process more efficient.

We had a program that spent a LOT of time with the invoicing process.  They were hoping that they could use a Google form to gather information for invoices, then use the Add-on Autocrat to create the invoice which could then be shared with appropriate parties.

The problem was that each invoice needed to be assigned a unique number.  Enter a script to add to the spreadsheet.  After creating it, I realized that this simple script could be used in a variety of ways so thought I'd share (I've already used it for two other applications!).

1. Create your form to collect the data (not covering this here - I am assuming you know how to create a Google Form)

2. In the result page, select to create spreadsheet so you can view the data in a spreadsheet

3. Go to the Tools Menu and open the Script editor
4. Delete out the default text
Replace with this script (all in highlighted gray including the final curly bracket):

function assignInvoiceNumber() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 
 var lastRow = sheet.getLastRow();
 var lastColumn = sheet.getLastColumn();
 
//create an array of the invoice numbers already created
 var colArray = sheet.getRange(2, lastColumn-5, lastRow-1, 1).getValues();
 
//sort the array values to find max
 var maxInColumn = colArray.sort(function(a,b){return b-a})[0][0];

 var nextCell = sheet.getRange(lastRow, lastColumn-5);

//assign the max+1 to the next entry
  nextCell.setValue(maxInColumn+1);

}


I've tried to note in the script what it does.  The script mainly has establishing a lot variables to grab information -
ss - finds the active spreadsheet (since this script is tied to the sheet, this is easier than if it is a published script)
sheet - finds the sheet - the [0] is telling it to grab the first sheet; if you want to run this on a different sheet, number according to location starting with zero
lastRow - finds the last row on the sheet and returns (or "remembers") the number
lastColumn - finds the last column on the sheet and returns the number
nextCell - establishes the cell needing the new invoice number
colArray - is getting the values in our Invoice# column; the numbers in the parenthesis are dictating which column to get those values from:
  • the 2 says start with the second row (to eliminate the header row)
  • the lastColumn-5 says to start with the column 5 from the end (or last) column
  • the lastRow-1 says to end with the next to last row of data (since the last row won't have an invoice number yet)
  • the 1 says to only get the one column of data 
The next line - var maxInColumn = colArray.sort(function(a,b){return b-a})[0][0]; - is sorting the values we got from that column - this way if they are out of order because we have to hand-assign one, it will make sure not to duplicate an entry.

The last line -   nextCell.setValue(maxInColumn+1);  - is assigning a new invoice number to the last row of data - one that is 1 greater than the previous max.

Once done, we set a trigger so the script would run whenever a new entry was made
One note - you do NOT want to use a time trigger on this.  If you do it will just keep changing the last entry every time it runs.

That's it!  A pretty simple script to ensure we get unique invoice numbers before creating and sending out the documents.

Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

Google Sign-in - New & Updated

2017 MACUL Conference - Adding Pre-Conference Sessions to your Registration

Google Classroom - Add "Class Materials"