基于重复ID更新Google Sheets行：多审批阶段脚本优化

Got it, let's solve that duplicate row problem you're facing. The core issue is that your script is appending a new row every time the approval stage changes, but we can fix this by using the fixed Id to find the existing row and update it instead. Here's a practical, step-by-step solution tailored to your scenario:

1. First, Map Product Types to Target Worksheets #

Since you're routing data to different sheets based on product type, start by creating a helper function to grab the correct worksheet for each submission:

// Example using Google Apps Script (common for sheet automation; adjust if using another platform)
const getTargetSheet = (productType) => {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  switch(productType) {
    case "Electronics":
      return spreadsheet.getSheetByName("Electronics Approvals");
    case "Clothing":
      return spreadsheet.getSheetByName("Clothing Approvals");
    // Add cases for all your product types
    default:
      throw new Error(`Unrecognized product type: ${productType}`);
  }
};

2. Build a Function to Locate Rows by ID #

Next, write a helper that searches the target sheet for your fixed Id value. Let's assume IDs are stored in column A (adjust the column index if yours is different):

const findRowByID = (sheet, targetID) => {
  const allRows = sheet.getDataRange().getValues();
  
  // Skip header row (start loop at index 1, which is sheet row 2)
  for (let i = 1; i < allRows.length; i++) {
    if (allRows[i][0] === targetID) { // Column A = index 0
      return i + 1; // Return 1-indexed sheet row number
    }
  }
  return null; // Return null if ID isn't found (first-time submission)
};

3. Main Logic: Update or Append (Fallback) #

Now tie it all together in your main processing function. Check if the ID exists—if yes, update the row; if no, append a new one for first-time submissions:

const processApprovalSubmission = (jsonData) => {
  const submission = jsonData.data;
  const targetSheet = getTargetSheet(submission.productType);
  const existingRow = findRowByID(targetSheet, submission.Id);

  // Define the range to write to: either existing row or new row at the bottom
  const writeRange = existingRow 
    ? targetSheet.getRange(existingRow, 2, 1, Object.keys(submission).length - 1) // Start at column B (skip ID column)
    : targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, Object.keys(submission).length);

  // Prepare values in the exact order they appear in your sheet columns
  const valuesToWrite = [
    submission.Id,
    submission.productType,
    submission.approvalStage,
    submission.customerName,
    submission.requestedAmount
    // Add all your submission fields here, matching sheet column order
  ];

  // Write the updated data
  writeRange.setValues([valuesToWrite]);

  console.log(existingRow 
    ? `Updated row ${existingRow} for submission ID ${submission.Id}` 
    : `Added new row for submission ID ${submission.Id}`);
};

4. Key Tips to Avoid Headaches #

• Header Row Check: Make sure your sheet has a header row, and the findRowByID function skips it (the loop starts at i=1 to ignore row 1).
• Column Order Accuracy: Double-check that valuesToWrite matches the exact column order in your worksheet—mismatches will send data to the wrong columns.
• Performance for Large Sheets: If you have thousands of rows, use a cached ID-to-row map instead of looping every time. Here's a quick optimization:

  const createIDRowMap = (sheet) => {
    const rows = sheet.getDataRange().getValues();
    const idMap = new Map();
    for (let i = 1; i < rows.length; i++) {
      idMap.set(rows[i][0], i + 1);
    }
    return idMap;
  };
  

  Use this map in findRowByID to speed up lookups for frequent submissions.

5. Test with Approval Stages #

Now, every time your form moves through a new approval stage, the script will locate the existing row using the fixed Id and update it with the latest data (like the new approval stage) instead of creating a duplicate row. This keeps each submission's history consolidated in one place.

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