Google Sheets脚本开发需求：跨表格匹配订单号并设置单元格样式

Hey there! Let's walk through how to implement this functionality step by step. The key is using Google Apps Script's TextFinder for efficient searching, then applying your desired style to the matching cells and their left neighbors.

Full Working Code #

function highlightMatchingOrderNumbers() {
  // 1. Access the active spreadsheet and target sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetAds = ss.getSheetByName("Ads_sent_in");
  const sheetMagazine = ss.getSheetByName("Magazine_name_03-2019");
  
  if (!sheetAds || !sheetMagazine) {
    Logger.log("One or both sheets could not be found!");
    return;
  }

  // 2. Get the latest Ordernumber from Ads_sent_in (fix from your initial code)
  // Use getValue() instead of getValues() to get the scalar value (not a 2D array)
  const ordernumber = sheetAds.getRange(sheetAds.getLastRow(), 1).getValue();
  
  if (!ordernumber) {
    Logger.log("No Ordernumber found in the last row of Ads_sent_in!");
    return;
  }

  // 3. Search for the Ordernumber in the Magazine sheet
  // Assuming Ordernumber is in the 3rd column (adjust if your sheet uses a different column)
  // Use matchEntireCell(true) to avoid partial matches (e.g., "123" vs "1234")
  const textFinder = sheetMagazine.createTextFinder(ordernumber)
    .matchEntireCell(true)
    .findNext();

  // 4. If a match is found, apply styling
  if (textFinder) {
    // Get the A1 notation of the matching Ordernumber cell (e.g., "C5")
    const ordernumberLoc = textFinder.getA1Notation();
    Logger.log(`Found Ordernumber at: ${ordernumberLoc}`);

    // Get the range covering the Ordernumber cell and its left two columns
    const targetRow = textFinder.getRow();
    const startCol = textFinder.getColumn() - 2;
    // Range parameters: row, start column, number of rows, number of columns
    const targetRange = sheetMagazine.getRange(targetRow, startCol, 1, 3);

    // Create your desired text style
    const greenBoldStyle = SpreadsheetApp.newTextStyle()
      .setForegroundColor("green")
      .setBold(true)
      .build();

    // Apply the style to the target range
    targetRange.setTextStyle(greenBoldStyle);
  } else {
    Logger.log(`Ordernumber ${ordernumber} not found in Magazine_name_03-2019!`);
  }
}

Key Details Explained #

• Sheet Validation: We first check if both sheets exist to avoid errors if sheet names are misspelled.
• Fixing Ordernumber Extraction: Your original code used getValues() which returns a 2D array (e.g., [[123]]). Switching to getValue() gives us the direct scalar value (123).
• Efficient Search: createTextFinder is far more efficient than looping through every row manually, especially for large sheets. matchEntireCell(true) ensures we only get exact matches.
• Range Selection: We calculate the start column by subtracting 2 from the matching cell's column index, then select a 1-row, 3-column range (left two cells + the Ordernumber cell).
• Style Application: We reuse your style code but apply it to the combined range for simplicity.

Optional Adjustments #

• Handle Multiple Matches: If you need to highlight all matching Ordernumbers instead of just the first, replace findNext() with findAll() and loop through the results:

  const matches = textFinder.findAll();
  matches.forEach(match => {
    const targetRow = match.getRow();
    const startCol = match.getColumn() - 2;
    const targetRange = sheetMagazine.getRange(targetRow, startCol, 1, 3);
    targetRange.setTextStyle(greenBoldStyle);
  });
  

• Adjust Column Indices: If your Ordernumber column in the Magazine sheet isn't the 3rd column, update the logic to find the correct column dynamically (e.g., search the header row for "Ordernumber" to get its index).

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