Google Apps Script采购订单生成器数量单元格无法填充问题求助

Hey there! Let's dig into why your quantity value isn't showing up when you click the "Add Item" button in your PO generator. I looked over your AddItem() function and spotted a few key issues that could be causing this, plus some optimizations to make your code more reliable.

First, Let's Diagnose the Quantity Problem #

The core issue with quantity not populating likely stems from one of these scenarios:

• Incorrect cell reference: Double-check that B14 in your "POS" sheet is actually where you're entering the quantity (it's easy to misclick a cell!).
• Data type mismatch: If the B14 cell is formatted as text instead of a number, getValue() might pull in a string that doesn't display properly in the project row.
• Unreliable row calculation: The original getLastRow() + 1 might not target the correct empty row if there are hidden rows or blank cells in your PO sheet's item section.

Fixed & Optimized AddItem() Function #

Here's a revised version of your function with fixes for the quantity issue and performance improvements:

function AddItem() {
  // Define active sheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var poSheet = ss.getSheetByName("POS");
  var itemSheet = ss.getSheetByName("ITEMS");

  // Get input values (convert quantity to number to avoid type issues)
  var part = poSheet.getRange('B13').getValue();
  var quantity = Number(poSheet.getRange('B14').getValue());

  // Validate quantity input first
  if (isNaN(quantity) || quantity <= 0) {
    SpreadsheetApp.getUi().alert("Please enter a valid positive number for quantity!");
    return;
  }

  // Find the correct next empty row in the PO sheet's item section (starts at row 17)
  var poColumnA = poSheet.getRange("A:A").getValues();
  var lastrowPO = 17; // Start at the first item row
  while (lastrowPO < poColumnA.length && poColumnA[lastrowPO - 1][0] !== "") {
    lastrowPO++;
  }

  // Fetch all item data at once (way faster than looping getRange calls)
  var lastrowItem = itemSheet.getLastRow();
  var itemsData = itemSheet.getRange(2, 1, lastrowItem - 1, 3).getValues();
  var description = "";
  var unitCost = 0;

  // Match the part to its details
  for (var i = 0; i < itemsData.length; i++) {
    if (part === itemsData[i][0]) {
      description = itemsData[i][1];
      unitCost = itemsData[i][2];
      break; // Exit loop once we find the match
    }
  }

  // Check if we found the part details
  if (description === "" && unitCost === 0) {
    SpreadsheetApp.getUi().alert("No matching part found in the ITEMS sheet!");
    return;
  }

  // Populate the PO sheet with item data
  poSheet.getRange(lastrowPO, 1).setValue(part);
  poSheet.getRange(lastrowPO, 2).setValue(description);
  poSheet.getRange(lastrowPO, 3).setValue(quantity);
  poSheet.getRange(lastrowPO, 4).setValue(unitCost).setNumberFormat("$#,###.00");

  // Clear input fields for next entry
  poSheet.getRange('B13').setValue("");
  poSheet.getRange('B14').setValue("");
}

Key Changes Made: #

1. Quantity type conversion: Using Number() ensures we're working with a numeric value, even if the input cell is formatted as text.
2. Input validation: Adds alerts for invalid quantities or missing part matches, so you know exactly what's wrong when something fails.
3. Reliable row targeting: Instead of relying on getLastRow(), we explicitly start at row 17 (your item section) and find the first empty row in column A.
4. Performance boost: Fetches all item data in one call instead of looping through individual getRange() requests (Google Apps Script hates repeated getRange calls!).
5. Cleanup: Clears the input fields after adding an item, making it easier to add the next one.

Quick Checks to Verify: #

• Confirm the quantity input cell in your "POS" sheet is definitely B14.
• Make sure the part name in B13 exactly matches the part name in column A of the "ITEMS" sheet (no extra spaces or case differences!).
• Ensure the item rows in your "POS" sheet (starting at row 17) aren't protected or locked.

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