[GAS] TextFinder

在 Google Apps Script 內可以透過 TextFinder 來尋找或取代 Google Sheets 內資料

SampleData

.gs Code

總共紀錄 5 個使用範例，後續會一一紀錄

let searchText = "蘋果";
let sheetName = "SampleData"
let columnBRange = "B1:B10";
let highlightColor = "lightgreen";

function FindFirstData() {

  let range = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(columnBRange)
    .createTextFinder(searchText)
    .findNext();

  let exist = range ? true : false
  Logger.log(exist);

  if (exist)
    Logger.log(range.getA1Notation());
}

function AppleInColumnB() {
  
  let rangeList = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(columnBRange)
    .createTextFinder(searchText)
    .matchEntireCell(true)
    .findAll()
    .map((r) => r.getA1Notation());

  Logger.log(rangeList);
}

function FullTextSearch() {

  let rangeList = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .createTextFinder(searchText)
    .findAll();

  rangeList.forEach(range => {
    range.setBackground(highlightColor);
  });
}

function Regular() {
  
  let regex = "紅|綠"; 

  let rangeList = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(columnBRange)
    .createTextFinder(regex)
    .useRegularExpression(true)
    .findAll()
    .map((r) => r.getA1Notation());

  Logger.log(rangeList);
}

function Update() {

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    Logger.log("找不到名為 SampleDate 的工作表。請檢查工作表名稱。");
    return;
  }

  let rangeToSearch = sheet.getRange(columnBRange);
  let replaceText = "Apple";
  let textFinder = rangeToSearch.createTextFinder(searchText);

  let rangesFound = textFinder.findAll();
  if (rangesFound.length === 0) {
    Logger.log(`在範圍 "${columnBRange}" 中沒有找到 "${searchText}"。`);
    return;
  }

  let replacementsCount = textFinder.replaceAllWith(replaceText);
  Logger.log(`已完成更新，總共更新 ${replacementsCount} 筆資料`);
  
  rangesFound.forEach(range => {
    range.setBackground(highlightColor);
  });
}

尋找第一筆資料 (模糊搜尋)

在指定範圍內透過 findNext 進行模糊搜尋來搜尋第一筆相符合資料

findNext

Returns the next cell matching the search criteria.

function FindFirstData() {

  let range = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(columnBRange)
    .createTextFinder(searchText)
    .findNext();

  let exist = range ? true : false
  Logger.log(exist);

  if (exist)
    Logger.log(range.getA1Notation());
}

精確搜尋

在指定範圍內尋找全部 (findAll) 完全相符 (matchEntireCell) 資料

matchEntrieCell

If true, configures the search to match the entire contents of a cell; otherwise, the search defaults to partial matching.

findAll

Returns all cells matching the search criteria.

function AppleInColumnB() {
  
  let rangeList = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(columnBRange)
    .createTextFinder(searchText)
    .matchEntireCell(true)
    .findAll()
    .map((r) => r.getA1Notation());

  Logger.log(rangeList);
}

全文檢索

針對指定 SheetName 內全部欄位進行模糊搜尋，想針對一個 Sheets 內的全部 SheetName 進行模糊搜尋的話，就把 getSheetByName 移除就行

function FullTextSearch() {

  let rangeList = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .createTextFinder(searchText)
    .findAll();

  rangeList.forEach(range => {
    range.setBackground(highlightColor);
  });
}

正則表示式

在指定範圍內透過正則表示式來自定搜尋文字，一定要開啟 useRegularExpression 才會用上正則表示式，要不然就當成文字來搜尋，該範例要尋找紅或綠文字

useRegularExpression

If true, configures the search to interpret the search string as a regular expression; otherwise the search interprets the search string as normal text.

function Regular() {
  
  let regex = "紅|綠"; 

  let rangeList = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(columnBRange)
    .createTextFinder(regex)
    .useRegularExpression(true)
    .findAll()
    .map((r) => r.getA1Notation());

  Logger.log(rangeList);
}

取代資料

該範例把每一個步驟都註記文字說明，要把 [蘋果] 更新為 [Apple] 並把 cell 背景顏色設定為淺綠色

replaceAllWith

Replaces all matches with the specified text.

function Update() {

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    Logger.log("找不到名為 SampleDate 的工作表。請檢查工作表名稱");
    return;
  }

  let rangeToSearch = sheet.getRange(columnBRange);

  // 取代文字
  let replaceText = "Apple";

  // 建立 TextFinder
  let textFinder = rangeToSearch.createTextFinder(searchText);

  // 尋找所有符合條件的的儲存格
  let rangesFound = textFinder.findAll();

  if (rangesFound.length === 0) {
    Logger.log(`在範圍 "${columnBRange}" 中沒有找到 "${searchText}"。`);
    return;
  }

  // 取代所有搜尋到的文字
  let replacementsCount = textFinder.replaceAllWith(replaceText);
  Logger.log(`已完成更新，總共更新 ${replacementsCount} 筆資料`);

  // 標記有取代的儲存格
  rangesFound.forEach(range => {
    range.setBackground(highlightColor);
  });
}

• 參考資料
• Taking Advantage of TextFinder for Google Spreadsheet
• Find and replace text in Google Sheets using TextFinder and Apps Script
• Day13【ES6 小筆記】Array.map() - 處理陣列最佳選擇