Google Apps Script采购订单ID重复填充逻辑优化及代码修改求助

Hey there! Let's sort out that PO ID filling issue you're dealing with. The core problem in your current code is the empty row check—you're comparing an entire row array to an empty string, which doesn't work because ProductValues[i] is a 2-element array (from columns B and C). Here's the revised code with proper logic, plus some small performance tweaks:

function exportPO() { 
  var myGooglSheet = SpreadsheetApp.getActive(); 
  var shSource = myGooglSheet.getSheetByName("Source"); 
  var shDatabase = myGooglSheet.getSheetByName("Database"); 
  var lastRow = shDatabase.getLastRow();
  var ProductValues = shSource.getRange("B5:C14").getValues(); 
  shDatabase.getRange(lastRow+1,2,ProductValues.length,2).setValues(ProductValues); 
  
  // Get PO as a single value instead of a 2D array for cleaner use
  var PO = shSource.getRange("C1").getValue(); 
  var lines = 0; 
  var ProductRowCount = ProductValues.length; 

  for(var i=0; i<ProductRowCount; i++){
    // Check if either column B or C in the current row has content
    var hasProductData = ProductValues[i][0] !== '' || ProductValues[i][1] !== '';
    if (hasProductData){ 
      lines++; 
      // Use setValue since PO is a single value, not an array
      shDatabase.getRange(lastRow+lines,1).setValue(PO); 
      shDatabase.getRange(lastRow+lines,4)
        .setValue(new Date())
        .setNumberFormat('yyyy-mm-dd h:mm'); // Submission timestamp
    }
  }
}

Key Fixes & Improvements: #

• Proper Empty Row Check: Instead of comparing the entire row array to '', we check if either the first (ProductValues[i][0], column B) or second (ProductValues[i][1], column C) cell in the row has content. This ensures we only fill the PO ID for rows that actually have product data.
• Simplified PO Value Retrieval: Changed getValues() to getValue() for the PO cell—this gives us a single value instead of a 2D array, making setValue() more intuitive (though setValues(PO) would still work, this is cleaner).
• Clearer Variable Names: Renamed ProductRow to ProductRowCount to make the code more readable.

This code will now only populate the Purchase Order ID and submission timestamp for rows in the Source sheet (B5:C14) that have actual product data, instead of filling every row in the range.

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