Google Sheets中单元格值转移及隔列库存自动调整技术问询

Hey there! Let’s sort out this automated inventory transfer for your Google Sheets. I’ve put together a step-by-step solution using Apps Script, since we need to modify cells and clear inputs automatically—regular formulas can’t quite handle that.

How It’ll Work (Matching Your Example) #

• Column B input cells: When you enter a number (e.g., B5), it’ll add that value to the corresponding inventory cell (C5), then clear B5.
• Column C inventory cells: Show AMOUNT C when inventory is empty/0, or the current numeric inventory when there’s a value.
• Column D input cells: When you enter a number (e.g., D5), it’ll subtract that value from the corresponding inventory cell (C5), then clear D5.

Step 1: Set Up the Apps Script #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to open the script editor.
3. Delete the default myFunction() code, then paste the script below:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedCell = e.range;
  const row = editedCell.getRow();
  const col = editedCell.getColumn();
  const value = editedCell.getValue();

  // Skip header rows (adjust row number if your header isn't row 1)
  if (row <= 1 || typeof value !== 'number') return;

  // Handle "add to inventory" (Column B → Column C)
  if (col === 2) {
    const inventoryCell = sheet.getRange(row, col + 1);
    const currentInventory = inventoryCell.getValue() || 0;
    inventoryCell.setValue(currentInventory + value);
    editedCell.clearContent();
    updateInventoryDisplay(inventoryCell);
  }

  // Handle "deduct from inventory" (Column D → Column C)
  else if (col === 4) {
    const inventoryCell = sheet.getRange(row, col - 1);
    const currentInventory = inventoryCell.getValue() || 0;
    // Prevent negative inventory (remove Math.max if you want negative values allowed)
    const newInventory = Math.max(currentInventory - value, 0);
    inventoryCell.setValue(newInventory);
    editedCell.clearContent();
    updateInventoryDisplay(inventoryCell);
  }

  // Add more blocks here if you have additional columns (e.g., Column F → Column E, etc.)
}

// Helper: Update inventory cell display (show "AMOUNT X" if empty/0, else numeric value)
function updateInventoryDisplay(cell) {
  const colLetter = columnToLetter(cell.getColumn());
  const cellValue = cell.getValue();
  
  if (cellValue === 0 || cellValue === "") {
    cell.setValue(`AMOUNT ${colLetter}`);
  } else {
    cell.setNumberFormat('0'); // Ensure numeric formatting
  }
}

// Helper: Convert column number to letter (e.g., 3 → C)
function columnToLetter(column) {
  let temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

4. Click the save icon, name your project something like InventoryTransferAutomation, then close the script editor.

Step 2: Test It Out #

• Go to cell B5, enter a number (e.g., 10), and press Enter. You’ll see C5 update to 10, and B5 clears automatically.
• If you delete the value in C5, it’ll revert to showing AMOUNT C.
• Enter a number (e.g., 3) in D5, press Enter. C5 will drop to 7, and D5 clears.

Customization Tips #

• Add more columns: If you have additional inventory columns (e.g., E, G) with input columns between them (D, F), just copy the existing if/else if blocks and adjust the column numbers. For example, to handle Column F adding to Column E:

  else if (col === 6) {
    const inventoryCell = sheet.getRange(row, col - 1);
    const currentInventory = inventoryCell.getValue() || 0;
    inventoryCell.setValue(currentInventory + value);
    editedCell.clearContent();
    updateInventoryDisplay(inventoryCell);
  }
  

• Remove negative inventory protection: If you want to allow inventory to go negative, delete the Math.max() wrapper in the deduct block, so it’s just const newInventory = currentInventory - value;.

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