如何使用GAS生成带前导零且可自动扩展位数的序列号？

Hey there! I've adjusted your Google Apps Script to handle both the fixed 6-digit leading zero requirement and automatic digit expansion when your sequence exceeds 999999. Here's the updated code and a quick breakdown of the key changes:

Key Modifications #

• Dynamic Digit Length: We calculate the required number of digits based on the next ID value. It starts at 6 digits, and automatically increases to 7, 8, etc., once the sequence surpasses 999999.
• Leading Zero Preservation: Used Utilities.formatString() to pad the ID with leading zeros, and set the column to plain text format to ensure Google Sheets doesn't strip those zeros.
• Simplified ID Calculation: Streamlined the logic for getting the next ID to avoid redundant checks and handle edge cases more reliably.

Updated Script #

function addAutoNumber() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Form responses 1");
  var lastRow = sheet.getLastRow();
  const initialStart = 100; // Store as raw number, we'll handle formatting later

  // ---- First Run Setup ----
  if (sheet.getRange(1, 1).getValue() === "Timestamp") {
    sheet.insertColumnBefore(1);
    sheet.getRange(1, 1).setValue("Auto Number");
    // Set entire column to plain text to preserve leading zeros
    sheet.getRange("A:A").setNumberFormat("@");

    if (lastRow > 1) {
      let currentId = initialStart;
      for (let ii = 2; ii <= lastRow; ii++) {
        // Calculate digit length: minimum 6, auto-expand if number is longer
        let digitLength = Math.max(6, String(currentId).length);
        let formattedId = Utilities.formatString("%0" + digitLength + "d", currentId);
        sheet.getRange(ii, 1).setValue(formattedId);
        currentId++;
      }
    }
  }

  // ---- Add New Auto Number for Latest Submission ----
  if (sheet.getRange(lastRow, 1).isBlank()) {
    let nextId;
    if (lastRow === 2) {
      nextId = initialStart;
    } else {
      // Extract numeric value from last ID to avoid formatting issues
      let lastIdStr = sheet.getRange(lastRow - 1, 1).getValue().toString().replace(/\D/g, "");
      nextId = parseInt(lastIdStr) + 1;
    }

    // Calculate required digit length and format the ID
    let digitLength = Math.max(6, String(nextId).length);
    let formattedId = Utilities.formatString("%0" + digitLength + "d", nextId);
    sheet.getRange(lastRow, 1).setValue(formattedId);
  }
}

How It Works #

1. First Run: Inserts the "Auto Number" column, sets it to plain text (so leading zeros stay visible), and backfills all existing rows with properly formatted IDs starting from 000100.
2. New Submissions: Checks if the latest row's Auto ID is blank. If so, it grabs the last valid ID, increments it, calculates the needed digit length (ensuring at least 6 digits), and formats it with leading zeros.
3. Auto-Expansion: When the sequence hits 1000000, the digit length automatically jumps to 7—so you'll get IDs like 1000000 instead of being stuck at 6 digits. For smaller numbers in this phase (e.g., 1234), it will format to 0001234 to maintain consistent length.

This setup keeps your IDs clean, consistent, and scalable as your dataset grows.

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