Google Spreadsheet脚本编辑器：按指定范围批量设边框（代码简化求助）

Got it, let's get this sorted! The core issue here is that you're targeting individual cells instead of a range, and we can clean up the code to be more efficient and work across multiple cells at once.

First, let's simplify the basic version where you want to apply borders to a defined range (like a row or block of cells starting at lastRow + 4):

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Form Responses 1');
var lastRow = sheet.getLastRow(); // Assuming this was defined earlier in your code

// Define your target range: start row, start column, number of rows, number of columns
// Example: 1 row spanning columns B to F (columns 2-6 = 5 columns total)
var targetRange = sheet.getRange(lastRow + 4, 2, 1, 5);

// Apply border to the entire range in one go
targetRange.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);

Key improvements here: #

• Removed redundant variable declarations (you were redefining cell twice, which is messy and unnecessary)
• Used getRange() with four parameters to target a block of cells instead of a single one
• Applied the border to the entire range in one go, which is far more efficient than cell-by-cell operations

If you need to dynamically set the range based on the first empty cell in a specific column (say column B), here's how to add that logic:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Form Responses 1');
var columnBValues = sheet.getRange('B:B').getValues();

// Find the first empty row in column B (rows are 1-indexed)
let firstEmptyRow;
for (let i = 0; i < columnBValues.length; i++) {
  if (!columnBValues[i][0]) { // Check if cell is empty
    firstEmptyRow = i + 1;
    break;
  }
}

// Define your range using the first empty row (adjust rows/columns as needed)
// Example: 3 rows tall, 4 columns wide starting at the first empty row in B
var targetRange = sheet.getRange(firstEmptyRow, 2, 3, 4);

// Apply borders
targetRange.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);

Quick notes on setBorder: #

The parameters are in this order: top, right, bottom, left, verticalInner, horizontalInner, color, style. If you don't want inner grid lines (just a box around the range), set verticalInner and horizontalInner to false.

Pro tip: If you find yourself applying this border style multiple times, wrap it in a helper function to avoid repeating code:

function applyFullBorder(range) {
  range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
}

// Use it like this:
applyFullBorder(targetRange);

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