如何使用Google Apps Script（GAS）根据区域内公司出现次数生成带递增双字母前缀与数字后缀的ID

Hey everyone, I'm working on a client ID generator for Google Sheets using Apps Script and I've hit a roadblock with the dual-letter prefix increment logic. Here's what I'm trying to achieve:

• Check if a target company exists in the specified range
• If the company doesn't exist, get the latest dual-letter ID prefix (formatted like AA, AB, etc.) and generate the next sequential one (e.g., if the latest is AC, generate AD; if it's AZ, generate BA)
• If the company appears multiple times, increment the numeric suffix of the ID (formatted like XX001, XX002, etc.)

My current code: #

function generateID() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const clientSheet = ss.getSheetByName('Clients');
  const dataRng = clientSheet.getRange(8, 1, clientSheet.getLastRow(), clientSheet.getLastColumn());
  const values = dataRng.getValues();
  const companies = values.map(e => e[0]);//Gets the company for counting
  for (let a = 0; a < values.length; a++) {
    let company = values[a][0];
    //Counts the number of occurrences of that company in the range
    var companyOccurrences = companies.reduce(function (a, b) {
      return a + (b == company ? 1 : 0);
    }, 0);
    if (companyOccurrences > 1) {
      let clientIdPrefix = values[a][2].substring(0, 2);//Gets the first 2 letters of the existing company's ID
    } else {
      //Generate ID prefix, incrementing on the existing ID Prefixes ('AA', 'AB', 'AC'...);
      let clientIdPrefix = incrementChar(values[a][2].substring(0,1));
      Logger.log('Incremented Prefixes: ' + clientIdPrefix)
    }
  }
}
//Increment passed letter
var alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')
function incrementChar(c) {
  var index = alphabet.indexOf(c)
  if (index == -1) return -1 // or whatever error value you want
  return alphabet[index + 1 % alphabet.length]
}

The issue: #

I adapted this from a single-letter increment solution by tckmn, but it only handles single characters. I can't get it to work for dual-letter prefixes (like moving from AZ to BA). I need to update the logic to support the full sequential dual-letter sequence, plus correctly handle the numeric suffix for duplicate companies.

Proposed solution: #

Here's an updated version of the code that fixes the dual-letter increment and handles all your requirements:

const alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');

// Function to increment a dual-letter prefix (AA → AB, AZ → BA, etc.)
function incrementDualPrefix(prefix) {
  if (prefix.length !== 2) return null;
  
  let firstChar = prefix[0];
  let secondChar = prefix[1];
  
  let secondIndex = alphabet.indexOf(secondChar);
  // If second character is Z, increment first character and reset second to A
  if (secondIndex === alphabet.length - 1) {
    let firstIndex = alphabet.indexOf(firstChar);
    // Wrap around if first character is also Z (adjust this if you don't want wrap-around)
    if (firstIndex === alphabet.length - 1) {
      return 'AA'; 
    }
    return alphabet[firstIndex + 1] + 'A';
  } else {
    return firstChar + alphabet[secondIndex + 1];
  }
}

// Function to get the latest used dual-letter prefix from the sheet
function getLatestPrefix(values) {
  // Extract all valid existing prefixes from column 3 (index 2)
  const prefixes = values
    .filter(row => row[2] && row[2].length >= 2)
    .map(row => row[2].substring(0, 2));
  
  if (prefixes.length === 0) return 'AA'; // Start with AA if no prefixes exist
  
  // Sort prefixes alphabetically to find the latest one
  prefixes.sort();
  return prefixes[prefixes.length - 1];
}

function generateID() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const clientSheet = ss.getSheetByName('Clients');
  const lastRow = clientSheet.getLastRow();
  if (lastRow < 8) return; // Exit if no data below row 7
  
  const dataRng = clientSheet.getRange(8, 1, lastRow - 7, clientSheet.getLastColumn());
  const values = dataRng.getValues();
  const companies = values.map(row => row[0] || '');
  
  // Get latest prefix for new companies
  const latestPrefix = getLatestPrefix(values);
  let nextPrefix = incrementDualPrefix(latestPrefix);

  for (let i = 0; i < values.length; i++) {
    let company = companies[i];
    if (!company) continue; // Skip empty company rows
    
    // Count occurrences of the current company
    const companyOccurrences = companies.filter(c => c === company).length;
    
    if (companyOccurrences > 1) {
      // For duplicate companies: increment the numeric suffix with the same prefix
      const existingId = values[i][2];
      if (existingId) {
        const prefix = existingId.substring(0, 2);
        const suffix = parseInt(existingId.substring(2));
        // Format suffix to 3 digits (001, 002)
        const newSuffix = String(suffix + 1).padStart(3, '0');
        const newId = prefix + newSuffix;
        // Write new ID back to the sheet (column 3)
        clientSheet.getRange(8 + i, 3).setValue(newId);
      }
    } else {
      // For unique companies: use next sequential prefix with 001 suffix
      const newId = nextPrefix + '001';
      clientSheet.getRange(8 + i, 3).setValue(newId);
      // Update prefix for next unique company
      nextPrefix = incrementDualPrefix(nextPrefix);
    }
  }
  
  Logger.log('ID generation completed successfully');
}

Key improvements: #

1. Dual-letter prefix logic: The incrementDualPrefix function handles full sequences like AZ → BA and wraps from ZZ → AA (adjust this behavior if you don't want wrap-around).
2. Latest prefix detection: getLatestPrefix extracts and sorts existing prefixes to ensure we always generate the next sequential one.
3. Duplicate handling: For repeated companies, we increment the numeric suffix while retaining the same prefix, formatted to 3 digits.
4. Sheet integration: The code now writes generated IDs directly back to the appropriate cells in the Clients sheet.

内容的提问来源于stack exchange，提问作者onit