基于条件移动指定背景色单元格？Google Apps Script实现求助

我需要给浅蓝色矩形绑定脚本，让指定范围内（图中B19:S38）背景色为#cc0000的单元格每次向目标方向移动一格。

移动逻辑 #

• 在指定范围（B19:S38）内找出背景色为#cc0000的单元格，存入数组
• 计算W35单元格记录的目标坐标与有色单元格的距离，取最近有色单元格到目标的距离
• 若只需横向或纵向移动，就让单元格向对应方向移动一格
• 若需同时横向和纵向移动，则两个方向各移动一格；如果目标与有色单元格距离为一格，不移动并输出提示“Stop”
• 移动时单元格值保持不变，清除原位置颜色，重叠位置保留颜色

移动状态示例 #

首次点击按钮后红色单元格位置：

再次点击后纵向移动至：

当红色单元格处于以下位置时，显示“Stop”：

原报错代码 #

我结合自身知识和ChatGPT编写了AppScript，但持续报错无法运行，代码如下：

function moveCellsByColor() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('B19:S36');
  var backgroundColors = range.getBackgrounds();
  var targetColor = '#cc0000';
  var rows = backgroundColors.length;
  var columns = backgroundColors[0].length;
  var coloredCells = [];
  for (var i = 0; i < rows; i++) {
    for (var j = 0; j < columns; j++) {
      if (backgroundColors[i][j] === targetColor) {
        coloredCells.push({ row: i + 1, col: j + 1 });
      }
    }
  }

  var targetCell = sheet.getRange('W35').getValue();
  var targetRow = parseInt(targetCell.match(/\d+/)[0]);
  var targetColumn = targetCell.charCodeAt(0) - 65;
  var minDistance = Number.MAX_SAFE_INTEGER;
  coloredCells.forEach(cell => {
    var distance = Math.abs(targetRow - cell.row) + Math.abs(targetColumn - cell.col);
    minDistance = Math.min(minDistance, distance);
  });
  if (minDistance === 1) {
    Logger.log('1 cell left, dont move.');
    return;
  }

  var cellsToMove = [];
  var cellsToKeep = [];

  coloredCells.forEach(cell => {
    var rowDiff = targetRow - cell.row;
    var colDiff = targetColumn - cell.col;

    var newRow = cell.row + (rowDiff !== 0 ? (rowDiff > 0 ? 1 : -1) : 0);
    var newCol = cell.col + (colDiff !== 0 ? (colDiff > 0 ? 1 : -1) : 0);

    if (newRow >= 1 && newRow <= rows && newCol >= 1 && newCol <= columns) {
      cellsToMove.push({ row: cell.row, col: cell.col });

      if (backgroundColors[newRow - 1][newCol - 1] !== targetColor) {
        cellsToKeep.push({ row: newRow, col: newCol });
      }
    }
  });
  cellsToMove.forEach(cell => {
    var moveCell = range.getCell(cell.row, cell.col);
    moveCell.setBackground(null);
  });

  cellsToKeep.forEach(cell => {
    var keepCell = range.getCell(cell.row, cell.col);
    keepCell.setBackground(targetColor);
  })
}

问题分析与修复代码 #

原代码存在范围行号错误、目标列解析逻辑缺陷、边界判断错误等问题，修复后的代码如下：

function moveCellsByColor() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 正确指定目标范围：B19:S38
  const targetRange = sheet.getRange('B19:S38');
  const rangeStartRow = targetRange.getRow();
  const rangeStartCol = targetRange.getColumn();
  const rangeEndRow = rangeStartRow + targetRange.getNumRows() - 1;
  const rangeEndCol = rangeStartCol + targetRange.getNumColumns() - 1;
  const backgroundColors = targetRange.getBackgrounds();
  const targetColor = '#cc0000';
  
  // 收集所有红色单元格的绝对行列号
  const coloredCells = [];
  for (let i = 0; i < backgroundColors.length; i++) {
    for (let j = 0; j < backgroundColors[i].length; j++) {
      if (backgroundColors[i][j] === targetColor) {
        coloredCells.push({
          row: rangeStartRow + i,
          col: rangeStartCol + j
        });
      }
    }
  }

  // 解析目标单元格坐标（如"A1"）为绝对行列号
  const targetCoord = sheet.getRange('W35').getValue().trim();
  const colMatch = targetCoord.match(/[A-Za-z]+/)[0].toUpperCase();
  const rowMatch = parseInt(targetCoord.match(/\d+/)[0], 10);
  // 计算目标列的绝对列号（支持多字母列如AA）
  let targetCol = 0;
  for (let c of colMatch) {
    targetCol = targetCol * 26 + (c.charCodeAt(0) - 64);
  }
  const targetRow = rowMatch;

  // 计算最近红色单元格到目标的曼哈顿距离
  let minDistance = Number.MAX_SAFE_INTEGER;
  coloredCells.forEach(cell => {
    const distance = Math.abs(targetRow - cell.row) + Math.abs(targetCol - cell.col);
    minDistance = Math.min(minDistance, distance);
  });
  if (minDistance === 1) {
    SpreadsheetApp.getUi().alert('Stop');
    Logger.log('Stop');
    return;
  }

  // 计算每个红色单元格的新位置
  const newPositions = new Set(); // 用Set避免重复位置
  const oldPositions = new Set();

  coloredCells.forEach(cell => {
    const rowDiff = targetRow - cell.row;
    const colDiff = targetCol - cell.col;

    // 计算移动后的新位置
    let newRow = cell.row;
    let newCol = cell.col;
    if (rowDiff !== 0) {
      newRow += rowDiff > 0 ? 1 : -1;
    }
    if (colDiff !== 0) {
      newCol += colDiff > 0 ? 1 : -1;
    }

    // 检查新位置是否在指定范围内
    if (newRow >= rangeStartRow && newRow <= rangeEndRow && newCol >= rangeStartCol && newCol <= rangeEndCol) {
      oldPositions.add(`${cell.row},${cell.col}`);
      newPositions.add(`${newRow},${newCol}`);
    }
  });

  // 清除原位置的颜色
  oldPositions.forEach(pos => {
    const [row, col] = pos.split(',').map(Number);
    sheet.getRange(row, col).setBackground(null);
  });

  // 设置新位置的颜色
  newPositions.forEach(pos => {
    const [row, col] = pos.split(',').map(Number);
    sheet.getRange(row, col).setBackground(targetColor);
  });
}

修复说明 #

• 范围处理：改用绝对行列号判断边界，确保移动范围严格在B19:S38内
• 目标列解析：支持多字母列（如AA、AB）的正确转换
• 位置去重：用Set存储新旧位置，避免重复设置颜色，处理重叠情况
• 用户提示：将日志输出改为UI弹窗，更直观显示“Stop”提示
• 变量命名：优化变量名，提升代码可读性

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