Google Sheets中GetValue/SetValue、循环及skuNO取值为空问题求助

Hey Eric! Let's break down your two questions—fixing that blank skuNO issue and clarifying how getValue()/setValue() and do while loops work in Google Apps Script for Sheets.

Let's start with the most pressing problem. Here are the most common reasons your skuNO variable is writing blank cells, plus how to troubleshoot:

• Check the source cell first: Head to your 'POTemplate' sheet and confirm the SKU cells you're targeting actually have values. If they're blank in the source, getValue() will return null, which translates to an empty cell in 'POHistory'.
• Verify your column/index references: Double-check that your loop is pointing to the correct column for SKUs. Remember, Google Sheets uses 1-indexed columns—so if SKUs are in column C, you need getRange(row, 3), not getRange(row, 2). An off-by-one error here will pull blank values from the wrong column.
• Debug with logging: Add Logger.log(skuNO) right after you fetch the value. Run the script, then check the logs (View > Logs in the script editor) to see what's actually being pulled. If it shows null, the source is empty; if it shows a valid value but still writes blank, there might be a data type mismatch or an error in how you're writing to 'POHistory'.
• Ensure your row increment works: Make sure your loop is correctly increasing the row variable each iteration. If it's stuck on the same row, you might be overwriting with a blank value from an empty row further down.

getValue() & setValue() Basics #

These methods are for single-cell operations, and they behave predictably once you know their quirks:

• getValue(): Pulls the value from a single cell, preserving its original data type (numbers stay numbers, dates stay Date objects, blank cells return null). It's great for one-off cell reads, but slow for bulk processing—use getValues() (plural) if you need to fetch a range of values at once (it returns a 2D array).
  Example:

  const potemplateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('POTemplate');
  const skuNO = potemplateSheet.getRange(2, 3).getValue(); // Gets value from row 2, column C
  

• setValue(): Writes a single value to a cell, automatically handling data types (e.g., passing a Date object will format it as a date in Sheets). For bulk writes, use setValues() with a 2D array—it's way more efficient and avoids hitting script execution limits.
  Example:

  const poHistorySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('POHistory');
  const nextRow = poHistorySheet.getLastRow() + 1;
  poHistorySheet.getRange(nextRow, 3).setValue(skuNO); // Writes skuNO to the next empty row in column C
  

do while Loops in Apps Script #

A do while loop runs the code block first, then checks if the condition is true to repeat. This is perfect for scenarios where you need to execute code at least once (like processing order rows until you hit a blank cell).

Syntax Example (tailored to your use case): #

let row = 2; // Start at row 2 (assuming row 1 is headers)
const potemplateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('POTemplate');

do {
  const skuNO = potemplateSheet.getRange(row, 3).getValue();
  // Fetch other variables (order date, quantity, etc.) here
  
  // Only write to POHistory if skuNO isn't blank
  if (skuNO !== null) {
    const poHistorySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('POHistory');
    const nextRow = poHistorySheet.getLastRow() + 1;
    poHistorySheet.getRange(nextRow, 3).setValue(skuNO);
    // Write other variables to their respective columns
  }
  
  row++; // Move to the next row to avoid infinite loops
} while (potemplateSheet.getRange(row, 3).getValue() !== null); // Stop when SKU cell is blank

Key Tips: #

• Always include an exit condition (like incrementing row) to avoid infinite loops.
• Use do while instead of a regular while loop when you need to run the code at least once—even if the initial condition is false.

内容的提问来源于stack exchange，提问作者Eric K.