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 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.
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
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 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
That's it! A pretty simple script to ensure we get unique invoice numbers before creating and sending out the documents.
