Skip to content
  • There are no suggestions because the search field is empty.

Mastering Google Apps Script: Create Unique IDs for Your Projects!

Picture of Michael Foutz

Creating unique IDs in Google Sheets using Google Apps Script is an invaluable skill for automating and organizing data efficiently. This blog post delves into a script that does just that, broken down into four key sections for better understanding.

 

 

Introduction to Unique ID Generation

We explore a Google Apps Script designed to generate unique IDs for list items in Google Sheets. This functionality proves essential for various applications, from event planning in educational settings to managing inventories or databases.

 Get Code from Github Repo

1. Generating a Unique ID

The foundation of the script lies in the generateUniqueID function, which creates a unique identifier by assembling a random sequence of alphanumeric characters.

 
code
function generateUniqueID() {
const idLength = 10; // Desired ID length
const characters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ123456789";
let uniqueID = "";

for (let i = 0; i < idLength; i++) {
uniqueID += characters.charAt(Math.floor(Math.random() * characters.length));
}


return uniqueID;
}

2. Updating the Sheet with IDs

The updateSheetWithIDs function seamlessly integrates the unique ID generator with Google Sheets, ensuring each required row is assigned a new, unique identifier.

 
code
function updateSheetWithIDs() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const sheetData = sheet.getRange(1, 1, sheet.getLastRow(), 2).getValues();

sheetData.forEach((row, index) => {
if (!row[0]) {
const newID = generateUniqueID();
sheet.getRange(index + 1, 1).setValue(newID);
}
});
}
 

3. Custom Menu Integration

To enhance user experience, the onOpen function introduces a custom menu in Google Sheets, enabling script execution directly from the spreadsheet's interface.

 
code
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('ID Operations')
.addItem('Generate IDs', 'updateSheetWithIDs')
.addToUi();
}
 

Conclusion and Customization

This script serves as a versatile tool that can be tailored to meet various needs. Adjustments can be made to the ID length, character set, or targeted sheet to accommodate different projects. The script's adaptability makes it a powerful addition to your Google Apps Script repertoire.

For those interested in the complete code and potential updates, visit the GitHub repository at Mastering Google Apps Script: Create Unique IDs for Your Projects.

This script not only simplifies the task of generating unique IDs in Google Sheets but also opens the door to a wide range of data management possibilities. Whether for educational purposes, business management, or personal projects, mastering this script can significantly enhance your efficiency and organization.