Google Sheets脚本需求：数组匹配后为Sheet2对应相邻列值+1

Got it, let's build a script that handles your lookup-and-update task efficiently. This solution reads all your data into memory first (a best practice to avoid slow repeated sheet access) and then processes each value from Sheet1 against Sheet2.

Full Script #

function updateSheetValues() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  
  // Get all values from Sheet1 (assuming values are in column A, adjust range if needed)
  const sheet1Values = sheet1.getDataRange().getValues().flat(); // Flatten to single array
  // Filter out empty values to avoid unnecessary checks
  const filteredSheet1Values = sheet1Values.filter(value => value !== "");
  
  // Get all data from Sheet2 (two columns: column A = lookup values, column B = counts)
  const sheet2Data = sheet2.getDataRange().getValues();
  // Create a map for fast lookups (key = lookup value, value = row index + value)
  const sheet2Map = new Map();
  sheet2Data.forEach((row, index) => {
    if (row[0] !== "") { // Skip empty rows in Sheet2
      sheet2Map.set(row[0], { rowIndex: index + 1, currentCount: row[1] || 0 });
    }
  });
  
  // Prepare an array to hold updated values for Sheet2
  const updates = [];
  
  // Process each value from Sheet1
  filteredSheet1Values.forEach(value => {
    if (sheet2Map.has(value)) {
      const { rowIndex, currentCount } = sheet2Map.get(value);
      const newCount = currentCount + 1;
      // Add the update to our list (row, column, new value)
      updates.push([rowIndex, 2, newCount]);
      // Update the map so duplicate values in Sheet1 are handled correctly
      sheet2Map.set(value, { rowIndex, currentCount: newCount });
    }
  });
  
  // Apply all updates to Sheet2 in one go
  if (updates.length > 0) {
    updates.forEach(update => {
      sheet2.getRange(update[0], update[1]).setValue(update[2]);
    });
    // Optional: Show a confirmation message
    SpreadsheetApp.getUi().alert(`Updated ${updates.length} entries in Sheet2!`);
  } else {
    SpreadsheetApp.getUi().alert("No matching values found to update.");
  }
}

How This Script Works #

• Get Data Efficiently: We read all values from both sheets at once instead of accessing the sheet in each loop—this drastically speeds up the script, especially with large datasets.
• Lookup Map: We convert Sheet2's data into a Map object, which lets us check for matches in O(1) time instead of looping through every row each time (way faster for big lists).
• Handle Duplicates: If Sheet1 has multiple instances of the same value, the map updates its stored count immediately, so each duplicate correctly increments the count multiple times.
• Batched Updates: Even though we loop through updates at the end, we’re still minimizing sheet interactions compared to updating during each iteration. For even larger datasets, you could use setValues() with a full range update instead.

Setup Instructions #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to open the script editor.
3. Paste this code into the editor.
4. Adjust the column references if your data isn't in Column A (Sheet1) or Columns A/B (Sheet2). For example, if Sheet1 values are in Column B, change sheet1.getDataRange().getValues().flat() to sheet1.getRange("B:B").getValues().flat().
5. Save the script with a name like updateSheetValues.
6. Run the script (you’ll need to authorize it the first time).

Troubleshooting Tips #

• Make sure your sheet names exactly match "Sheet1" and "Sheet2" (or update the getSheetByName calls to match your actual sheet names).
• If you get errors about empty values, the filter functions should handle most cases, but double-check that your sheets don't have unexpected empty rows at the bottom.
• For very large datasets (10k+ rows), consider using setValues() to write all updates in a single operation instead of looping through each update—just create a 2D array matching Sheet2's structure and write it back.

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