Google Sheets脚本修改需求：指定列复制、清除及批量操作

Got it, let's tweak your existing script to meet all three requirements. Here's the updated code along with step-by-step explanations:

1. Updated onEdit Function (Single Row Handling) #

This function triggers automatically when you enter "Y" in column G of the "IN" sheet, copying only columns B, C, E to the "ORDERS" sheet and clearing columns E & F of the processed row:

function onEdit(event) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = event.source.getActiveSheet();
  const r = event.source.getActiveRange();

  // Check if edit is in "IN" sheet, column G, and value is "Y"
  if (s.getName() === "IN" && r.getColumn() === 7 && r.getValue() === "Y") {
    const row = r.getRow();
    const targetSheet = ss.getSheetByName("ORDERS");
    const targetRow = targetSheet.getLastRow() + 1;

    // Copy only columns B, C, E (columns 2, 3, 5) to target sheet
    const bValue = s.getRange(row, 2).getValue();
    const cValue = s.getRange(row, 3).getValue();
    const eValue = s.getRange(row, 5).getValue();
    targetSheet.getRange(targetRow, 1).setValue(bValue);
    targetSheet.getRange(targetRow, 2).setValue(cValue);
    targetSheet.getRange(targetRow, 3).setValue(eValue);

    // Clear columns E & F (columns 5 & 6) of the processed row
    s.getRange(row, 5, 1, 2).clearContent();
  }
}

2. Batch Processing Function (One-Click Bulk Copy) #

Add this function to handle all rows in "IN" sheet that have "Y" in column G:

function processAllYRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("IN");
  const targetSheet = ss.getSheetByName("ORDERS");
  const lastRow = sourceSheet.getLastRow();
  // Get all data from column G to check for "Y"
  const gColumn = sourceSheet.getRange(1, 7, lastRow).getValues();

  // Iterate from bottom to top to avoid row shifting issues
  for (let i = lastRow; i >= 1; i--) {
    if (gColumn[i-1][0] === "Y") {
      const targetRow = targetSheet.getLastRow() + 1;
      // Copy B, C, E columns
      const bValue = sourceSheet.getRange(i, 2).getValue();
      const cValue = sourceSheet.getRange(i, 3).getValue();
      const eValue = sourceSheet.getRange(i, 5).getValue();
      targetSheet.getRange(targetRow, 1).setValue(bValue);
      targetSheet.getRange(targetRow, 2).setValue(cValue);
      targetSheet.getRange(targetRow, 3).setValue(eValue);

      // Clear E & F columns
      sourceSheet.getRange(i, 5, 1, 2).clearContent();
    }
  }
  SpreadsheetApp.getUi().alert("Batch processing completed!");
}

How to Add the One-Click Button #

1. Open your spreadsheet, go to Extensions > Apps Script, paste both functions above, and save the project (name it something like "OrderProcessor").
2. Return to your spreadsheet, click Insert > Drawing.
3. Draw a button shape (e.g., a rectangle with text like "Bulk Copy Y Rows"), then click Save and Close.
4. Click the new drawing, then the three-dot menu on the drawing, select Assign script, and type processAllYRows (no parentheses).
5. Now you can click this button anytime to process all rows marked with "Y" in column G.

Key Modifications Explained: #

• Selective Column Copy: Instead of copying the entire row, we explicitly grab values from columns 2 (B), 3 (C), and 5 (E) and paste them into the first three columns of the target sheet.
• Clear Specific Columns: After processing, s.getRange(row, 5, 1, 2).clearContent() clears columns E (5) and F (6) of the processed row.
• Batch Processing: The processAllYRows function iterates from the last row up to avoid issues with row indices shifting when processing multiple rows, and adds an alert to confirm completion.

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