Google Sheets脚本问题：实现Sheet1数据更新/新增至Sheet2

Let's walk through what's wrong with your script and fix it to match your requirements:

Key Issues in the Original Script #

• You're comparing Range objects, not cell values: When you write sheet2.getRange('C3') != sheet1.getRange('F5'), you're checking if two Range objects are different (they always are!), not if their contents match. You need to use .getValue() to get the actual cell content.
• Incorrect else-if logic: Your else-if condition also compares a Range to a string, and it doesn't handle the "update existing row" case from your requirement.
• Unnecessary cntr variable: Since you're only adding one row at a time, this variable doesn't serve a purpose here.

Corrected Script #

function emailongoing() {
  // Get references to both sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('OutageYellow');
  const sheet2 = ss.getSheetByName('test');

  // Get the values we need for comparison
  const sheet2C3Value = sheet2.getRange('C3').getValue();
  const sheet1F5Value = sheet1.getRange('F5').getValue();

  // Define the data we want to copy/update
  const dataToCopy = [
    sheet1.getRange('E5').getValue(), // Column B
    sheet1.getRange('F5').getValue(), // Column C
    sheet1.getRange('C7').getValue(), // Column D
    sheet1.getRange('C5').getValue(), // Column E
    sheet1.getRange('C8').getValue(), // Column I
    sheet1.getRange('C9').getValue()  // Column J
  ];

  // Check if values match
  if (sheet2C3Value === sheet1F5Value) {
    // Update existing row (row 3 in sheet2) with new data
    sheet2.getRange(3, 2, 1, 4).setValues([dataToCopy.slice(0,4)]); // B3:E3
    sheet2.getRange(3, 9, 1, 2).setValues([dataToCopy.slice(4)]);   // I3:J3
  } else {
    // Add new row at the end of sheet2
    const lastRow = sheet2.getLastRow() + 1;
    sheet2.getRange(lastRow, 2, 1, 4).setValues([dataToCopy.slice(0,4)]); // B:lastRow to E:lastRow
    sheet2.getRange(lastRow, 9, 1, 2).setValues([dataToCopy.slice(4)]);   // I:lastRow to J:lastRow
  }
}

What Changed & Why #

1. Used .getValue() for comparisons: This ensures we're checking the actual content of the cells, not the Range objects themselves.
2. Simplified data handling: We collect all values first into an array, then use setValues() instead of multiple copyTo() calls—this is more efficient and cleaner.
3. Implemented the "update" logic: When the values match, we now update row 3 in sheet2 (since that's where C3 is located) instead of adding a new row.
4. Removed redundant cntr variable: Since we're only adding one row per run, this variable wasn't needed.
5. Added const for variables: This makes the code more secure and readable by preventing accidental reassignment.

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