如何在Google Sheet中为A列有数据的行批量插入前置空白行？

Your current code only handles a single row because it’s hardcoded to insert before row 2. To do this in bulk, we need to iterate through rows from bottom to top—if we go top to bottom, inserting rows will shift the remaining rows down, which breaks our loop index and leads to missed or duplicated rows.

Here’s the revised function that does exactly what you need:

function InsertBlankRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var lastRow = s.getLastRow();
  
  // Iterate from the last data row up to row 1 (adjust if you have a header row)
  for (var i = lastRow; i >= 1; i--) {
    var cellValue = s.getRange(i, 1).getValue();
    // Only insert a row if column A has non-empty content
    if (cellValue !== "") {
      s.insertRowsBefore(i, 1);
    }
  }
}

Key Notes: #

• Bottom-Up Iteration: Starting at the last row and moving upward ensures that inserting a row doesn’t affect the rows we haven’t processed yet. This avoids the index-shifting problem that plagues top-down loops.
• Non-Empty Check: We only add a blank row if column A in the current row has content, so we don’t waste time inserting rows above already blank rows.
• Header Row Adjustment: If your sheet has a header in row 1, modify the loop to start at i = lastRow; i >= 2; i--—this skips inserting a row above your header.

How to Use: #

1. Open your Google Sheet.
2. Navigate to Extensions > Apps Script to open the script editor.
3. Replace your existing Insert function with this new InsertBlankRows function.
4. Save the script, then run it (you’ll need to authorize the script on its first run).

This will automatically insert a blank row before every row with data in column A, making your sheet far more readable!

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