Google Sheets Apps Script 搜索后清除旧高亮背景色问题咨询

• 原代码仅在本次搜索无匹配结果时才尝试清除背景，且此时操作的是空匹配列表，操作完全无效
• 当本次搜索有匹配结果时，没有任何清除上一轮高亮背景的逻辑，导致旧的黄色背景持续残留

在遍历单元格匹配搜索条件之前，先将当前工作表的所有数据区域背景重置为默认值，再对本次匹配到的单元格设置黄色高亮即可，逻辑简单且不会出现残留问题。

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  }
  quickFind_(e);
}

/**
* Finds cells that match the regular expression entered in a magic cell.
*
* @param {Object} e The onEdit() event object.
*/
function quickFind_(e) {
  const sheets = /./i; // use /./i to make the function work in all sheets
  const magicCell = 'C1';
  const sheet = e.range.getSheet();
  if (!e.value
    || e.range.getA1Notation() !== magicCell
    || !sheet.getName().match(sheets)) {
    return;
  }
  let searchFor;
  try {
    searchFor = new RegExp(e.value, 'i');
  } catch (error) {
    searchFor = e.value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  }
  const magicCellR1C1 = 'R' + e.range.rowStart + 'C' + e.range.columnStart;
  const data = sheet.getDataRange().getDisplayValues();

  // 新增：先清除所有之前的高亮背景
  sheet.getDataRange().setBackground(null);

  const matches = [];
  data
    .forEach((row, rowIndex) => row
      .forEach((value, columnIndex) => {
        if (value.match(searchFor)) {
          const cellR1C1 = 'R' + (rowIndex + 1) + 'C' + (columnIndex + 1);
          if (cellR1C1 !== magicCellR1C1) {
            matches.push(cellR1C1);
          }
        }
      }));
      
  if (matches.length) {
    sheet.getRangeList(matches).activate();
    sheet.getRangeList(matches).setBackground("yellow");
  }
}

内容的提问来源于stack exchange，提问作者Liepājas Liedaga vidusskola