Google Sheets：使用Apps Script实现基于模板生成员工专属工作表并添加引用的技术问询

Got it, let's tackle this step by step. Here's a complete, easy-to-implement solution using Google Apps Script that checks all your boxes:

First, head to your Google Sheets document. Click Extensions > Apps Script to launch the script editor—this is where we'll write our code.

Replace any existing code in the editor with this snippet (I've added comments to explain what each part does):

function generateEmployeeSheets() {
  // Get the active spreadsheet and reference our core sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const startSheet = ss.getSheetByName("Start");
  const templateSheet = ss.getSheetByName("Template");
  
  // Adjust this range if your employee names are in a different column (e.g., B2:B)
  const nameRange = startSheet.getRange("A2:A" + startSheet.getLastRow());
  // Extract non-empty names from the range
  const employeeNames = nameRange.getValues().flat().filter(name => name.trim() !== "");
  
  // Track generated names to update references in the Start sheet
  const processedNames = [];

  employeeNames.forEach(name => {
    // Check if a sheet for this employee already exists (avoid duplicates)
    let employeeSheet = ss.getSheetByName(name);
    
    if (!employeeSheet) {
      // Copy the Template sheet to the spreadsheet
      employeeSheet = templateSheet.copyTo(ss);
      // Rename the copied sheet to the employee's full name
      employeeSheet.setName(name);
    }
    
    // Write the employee's name to cell A2 of their personal sheet
    employeeSheet.getRange("A2").setValue(name);
    processedNames.push(name);
  });

  // Update the Start sheet with names and INDIRECT references
  // Clear old entries first (adjust columns C:D to your preferred location)
  startSheet.getRange("C2:D" + startSheet.getLastRow()).clearContent();
  
  // Write names and references to the Start sheet
  processedNames.forEach((name, index) => {
    const row = index + 2;
    // Write employee name to column C
    startSheet.getRange(row, 3).setValue(name);
    // Add INDIRECT formula linking to B2 of their sheet (column D)
    const referenceFormula = `=INDIRECT("'${name}'!B2")`;
    startSheet.getRange(row, 4).setFormula(referenceFormula);
  });

  // Show a confirmation alert when done
  SpreadsheetApp.getUi().alert("Employee sheets generated successfully!");
}

Now let's make this script accessible with a button in your sheet:

• Go back to your Google Sheets document
• Click Insert > Drawing, then draw a simple rectangle (or any shape you like) and add the text "Generate"
• Once you insert the drawing, click the three dots on its top-right corner > Assign script
• Type generateEmployeeSheets (exact match to the function name in the script) and click OK

• Adjust ranges: If your employee names are in column B instead of A, modify getRange("A2:A"... to getRange("B2:B".... To move the generated names/references to different columns, change the numbers in startSheet.getRange(row, 3) (3 = column C) and startSheet.getRange(row, 4) (4 = column D).
• Avoid duplicates: The script checks for existing sheets with the employee's name, so you can safely re-run it after adding new names without overwriting existing data.
• Permissions: The first time you run the script, Google will flag it as an unverified app—this is normal. Click Advanced > Go to [your script name] to grant the necessary permissions (since you wrote the code, it's completely safe).
• Protect the Template: If you don't want others to edit the original Template sheet, right-click it > Protect sheet to set editing restrictions.

内容的提问来源于stack exchange，提问作者hunter-gatherers