请求编写Google Apps Script实现Google Sheets按动态数值插入指定行数

Here's how you can replicate your Excel VBA functionality in Google Apps Script, tailored to Google Sheets' API:

Step-by-Step Code Explanation #

First, here's the full script that matches your VBA logic, including handling the format origin from the row below:

function insertStaffRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the staff count value from the named range "staff_count" (adjust if your range is different)
  const staffCountRange = ss.getRangeByName("staff_count");
  if (!staffCountRange) {
    SpreadsheetApp.getUi().alert("Named range 'staff_count' not found!");
    return;
  }
  const staffCount = staffCountRange.getValue();
  
  // Validate the count is a positive whole number
  if (typeof staffCount !== "number" || staffCount <= 0 || !Number.isInteger(staffCount)) {
    SpreadsheetApp.getUi().alert("Staff count must be a positive whole number!");
    return;
  }
  
  // Access the Master sheet
  const masterSheet = ss.getSheetByName("Master");
  if (!masterSheet) {
    SpreadsheetApp.getUi().alert("Sheet 'Master' not found!");
    return;
  }
  
  // Store the row number below the insertion point to copy formatting from (matches VBA's xlFormatFromRightOrBelow)
  const formatSourceRow = 4; // Row below A3 (original row 4 before insertion)
  
  // Insert the required number of rows after row 3
  masterSheet.insertRowsAfter(3, staffCount);
  
  // Copy formatting from the original row 4 (now shifted down by staffCount rows) to the new rows
  const newRowsRange = masterSheet.getRange(4, 1, staffCount, masterSheet.getLastColumn());
  const formatSourceRange = masterSheet.getRange(formatSourceRow + staffCount, 1, 1, masterSheet.getLastColumn());
  formatSourceRange.copyTo(newRowsRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

Key Differences from Your VBA Code #

• No "Select" required: Google Apps Script interacts directly with sheets and ranges without needing to activate them, which is more efficient and cleaner.
• Format handling: To match VBA's CopyOrigin:=xlFormatFromRightOrBelow, we explicitly copy the formatting from the row that was originally below the insertion point (row 4) to the newly inserted rows.
• Error safeguards: Added checks to ensure the staff count is valid and required ranges/sheets exist, so you get clear alerts instead of silent failures.

How to Use This Script #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to launch the script editor.
3. Replace any existing code with the script above.
4. Save the project (give it a name like "StaffRowInserter").
5. Run the function insertStaffRows for the first time—you’ll need to authorize the script to access your sheet (follow the prompts, click "Advanced" to allow access if needed).

Automating the Script (Optional) #

If you want this to run automatically:

• On edit trigger: Go to Edit > Current project's triggers, add a new trigger, select insertStaffRows, and choose "On edit" as the event type. This will run the script whenever any cell in the sheet is edited.
• Daily trigger: Choose "Time-driven" as the event type, set the frequency to daily at your desired time to sync with daily staff count updates.

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