如何修改Google Script实现基于单元格动态值插入行？

Got it, let's sort out this static row problem so your buttons always insert rows right below the correct proposal section—even when you add or delete rows above those sections. Here's how to adjust your script to use dynamic row numbers pulled from a cell, plus keep your data validation rules intact:

Step 1: Set Up Dynamic Row Number Cells #

First, create a dedicated sheet (let's call it RowTracker for clarity) to store the dynamic row numbers for each section. In this sheet:

• For your first section (originally row 40), enter =ROW('YourProposalSheet'!A40) in cell A1 (replace YourProposalSheet with your actual proposal table sheet name)
• For the second section (originally row 43), enter =ROW('YourProposalSheet'!A43) in cell A2
• For the third section (originally row 46), enter =ROW('YourProposalSheet'!A46) in cell A3

These formulas will automatically update whenever rows are inserted/deleted above your sections, so they'll always hold the current row number of each section's header.

Step 2: Modified Script with Dynamic Row Reading #

Replace your static scripts with these dynamic versions—one for each button. Each script reads the corresponding row number from RowTracker and inserts a row below it, plus copies the data validation and formatting from the section row:

// For first section button (originally startRow=40)
function addRowBelowSection1() {
  // Configure your sheet names
  const proposalSheetName = "YourProposalSheet";
  const trackerSheetName = "RowTracker";
  
  // Get the dynamic row number from RowTracker!A1
  const trackerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(trackerSheetName);
  const targetRow = trackerSheet.getRange("A1").getValue();
  
  // Get the proposal sheet and insert row
  const proposalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(proposalSheetName);
  proposalSheet.insertRowsAfter(targetRow, 1);
  
  // Copy data validation and formatting from the section row to the new row
  const sourceRange = proposalSheet.getRange(targetRow, 1, 1, proposalSheet.getLastColumn());
  const newRowRange = proposalSheet.getRange(targetRow + 1, 1, 1, proposalSheet.getLastColumn());
  
  // Paste validation rules first
  sourceRange.copyTo(newRowRange, SpreadsheetApp.CopyPasteType.PASTE_VALIDATION, false);
  // Paste formatting to match the section
  sourceRange.copyTo(newRowRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

// For second section button (originally startRow=43)
function addRowBelowSection2() {
  const proposalSheetName = "YourProposalSheet";
  const trackerSheetName = "RowTracker";
  
  const trackerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(trackerSheetName);
  const targetRow = trackerSheet.getRange("A2").getValue();
  
  const proposalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(proposalSheetName);
  proposalSheet.insertRowsAfter(targetRow, 1);
  
  const sourceRange = proposalSheet.getRange(targetRow, 1, 1, proposalSheet.getLastColumn());
  const newRowRange = proposalSheet.getRange(targetRow + 1, 1, 1, proposalSheet.getLastColumn());
  sourceRange.copyTo(newRowRange, SpreadsheetApp.CopyPasteType.PASTE_VALIDATION, false);
  sourceRange.copyTo(newRowRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

// For third section button (originally startRow=46)
function addRowBelowSection3() {
  const proposalSheetName = "YourProposalSheet";
  const trackerSheetName = "RowTracker";
  
  const trackerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(trackerSheetName);
  const targetRow = trackerSheet.getRange("A3").getValue();
  
  const proposalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(proposalSheetName);
  proposalSheet.insertRowsAfter(targetRow, 1);
  
  const sourceRange = proposalSheet.getRange(targetRow, 1, 1, proposalSheet.getLastColumn());
  const newRowRange = proposalSheet.getRange(targetRow + 1, 1, 1, proposalSheet.getLastColumn());
  sourceRange.copyTo(newRowRange, SpreadsheetApp.CopyPasteType.PASTE_VALIDATION, false);
  sourceRange.copyTo(newRowRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

How It Works #

• Dynamic Row Targeting: Instead of hardcoding startRow, the script pulls the current row number from your RowTracker sheet. Since those cells use the ROW() formula tied to your proposal sections, they'll update automatically when rows are added or deleted.
• Preserve Data Validation: The copyTo lines ensure the new row inherits the same dropdown rules and formatting as the section above it, which keeps your proposal table consistent.

Final Setup #

1. Update the sheet names in each script to match your actual sheet names
2. Assign each function to its corresponding button in your proposal sheet (right-click the button → Assign script → enter the function name like addRowBelowSection1)

Now, no matter how many rows you insert or delete above your sections, each button will always insert a new row directly below the correct section.

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