Custom formula that generates a unique invoice number
ππ’ Generating Invoice Numbers with Google Apps Script π’π
In this video, we dive into a practical example of using Google Apps Script to automate the generation of invoice numbers. The GENERATE_INVOICE_NUMBER() function is a part of a Google Sheets project and serves a crucial role in managing invoice records. Here’s a breakdown of what this code does:
- Spreadsheet Setup: The code begins by accessing the active Google Spreadsheet and the specific sheet where invoice data is stored. Be sure to replace
"Invoices"with the actual name of your sheet. - Current Date: It captures the current date, which will be used to determine the year and month for the invoice number.
- Log Range: The script identifies a range within the log sheet where invoice numbers are stored.
- Year and Month: It extracts the current year and month, ensuring they are correctly formatted for the invoice number.
- Last Invoice Number: The code retrieves the last used invoice number for the current month from the log sheet. If there isn’t one or if the month has changed since the last invoice, it sets a new invoice number for the month (e.g., “2023-1” for January).
- Incrementing Count: If there was a previous invoice number for the month, it increments the counter and updates the invoice number accordingly (e.g., “2023-2” for a second invoice in January).
- Invoice Number Generation: Finally, it generates the invoice number in the format “YYYY-MM-counter” (e.g., “2023-1” or “2023-2”) and returns it.
This code is incredibly useful for businesses and freelancers who need an automated way to create sequential and organized invoice numbers in their Google Sheets. Watch the video to see how this script works in action and learn how to implement it in your own projects! Don’t forget to like, share, and subscribe for more useful coding tips and tutorials! ππ #GoogleAppsScript #Automation #InvoiceGeneration #CodingTutorial
In this example, we’ll create a custom formula that generates a unique invoice number based on the current date and a sequential counter.
Scenario: You want to create a custom formula to generate invoice numbers that consist of the current year, month, and a sequential number. For each month, the sequential number should reset to 1.
function GENERATE_INVOICE_NUMBER() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const logSheet = ss.getSheetByName(“Invoices”); // Replace with the actual name of your new sheet
const currentDate = new Date();
const logRange = logSheet.getRange(2, 1, 1, 2);
// Get the current year and month
const year = currentDate.getFullYear();
const month = currentDate.getMonth() + 1; // Months are 0-indexed
// Retrieve the last used sequential number for the month from the log sheet
const vals = logRange.getValues()[0];
let lastNumber= vals[1];
// Check if the month has changed since the last invoice
if (lastNumber === “” || lastNumber === null || lastNumber.split(“-“)[0] != month) {
lastNumber = month + “-1”;
} else {
let counter = parseInt(lastNumber.split(“-“)[1]);
counter++;
lastNumber = month + “-” + counter;
}
// Generate and return the invoice number
const invoiceNumber = year + “-” + lastNumber;
return invoiceNumber;
}
The provided Google Apps Script function, GENERATE_INVOICE_NUMBER(), is designed to generate an invoice number based on the current month and a sequential counter. It also logs the sequential counter in a specific sheet for record-keeping. Below is a step-by-step explanation of how the code works:
Get the Spreadsheet and Sheet:
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const logSheet = ss.getSheetByName(“Invoices”); // Replace with the actual name of your new sheet
It first gets the active spreadsheet (ss) and the currently active sheet (sheet).
It retrieves a sheet named “Invoices” (you can replace this with the actual name of your log sheet) and assigns it to the logSheet constiable. This sheet is used to store invoice number records.
Get the Current Date:
const currentDate = new Date();
It gets the current date and time and stores it in the currentDate constiable.
Define the Log Range:
const logRange = logSheet.getRange(2, 1, 1, 2);
It defines a range in the logSheet starting from row 2, column 1 (A2), with a height of 1 row and a width of 2 columns. This range is used to retrieve and update the sequential counter.
Get the Current Year and Month:
const year = currentDate.getFullYear();
const month = currentDate.getMonth() + 1; // Months are 0-indexed
It extracts the current year and month from the currentDate. Note that months are 0-indexed in JavaScript, so it adds 1 to the month to get the correct month value.
Retrieve the Last Sequential Number:
const vals = logRange.getValues()[0];
const lastNumber = vals[1];
It fetches the values within the defined logRange and assigns them to the vals array. The [0] index is used because getValues() returns a 2D array, and we’re interested in the first row.
It extracts the last sequential number from the vals array and stores it in the lastNumber constiable.
Check and Update the Sequential Number:
if (lastNumber === “” || lastNumber === null || lastNumber.split(“-“)[0] != month) {
lastNumber = month + “-1”;
} else {
const counter = parseInt(lastNumber.split(“-“)[1]);
counter++;
lastNumber = month + “-” + counter;
}
It checks if the lastNumber is empty, null, or if its month portion doesn’t match the current month. If any of these conditions are met, it resets the lastNumber to the current month followed by “-1.”
If the month matches, it extracts the counter portion of the lastNumber, increments it by 1, and updates lastNumber accordingly.
Generate and Return the Invoice Number:
const invoiceNumber = year + “-” + lastNumber;
return invoiceNumber;
Finally, it combines the current year, a hyphen, and the updated lastNumber to form the complete invoice number and returns it.
In summary, this script generates unique invoice numbers based on the current month and a sequential counter. It also keeps a record of the counter in a designated sheet for tracking purposes.
