如何用Google Sheets API实现Apache POI同款行级读写逻辑？

Got it, let's map your Apache POI row-level logic to Google Sheets API step by step. The key difference is that Google Sheets API operates on value ranges rather than direct row/cell objects like POI, but we can replicate the exact behavior with a few adjustments.

Here's how to implement the same "read column 3, write PASS/FAIL to column 5 for rows 1-5" logic:

Step 1: Read Target Rows & Process Data #

First, we'll read the range that includes both the column we need to check (column 3, which is column C in Google Sheets) and the column we want to write to (column 5, column E). We'll process each row to determine the PASS/FAIL result.

// Define your spreadsheet ID and sheet name
String spreadsheetId = "YOUR_SPREADSHEET_ID";
String sheetName = "Sheet1";

// Corresponding to POI's row=1 to 5: assuming these are rows 2-6 in Google Sheets (skip header row)
String readRange = sheetName + "!A2:F6"; // Covers columns A-F for rows 2-6

// Fetch the data from Google Sheets
ValueRange response = service.spreadsheets().values()
    .get(spreadsheetId, readRange)
    .execute();
List<List<Object>> values = response.getValues();

// Prepare a list to hold our PASS/FAIL results for writing
List<List<Object>> writeData = new ArrayList<>();

if (values == null || values.isEmpty()) {
    System.out.println("No data found in the target rows.");
} else {
    for (List<Object> row : values) {
        // Read column 3 (index 2, which is column C) - handle cases where row is shorter
        String cellValue = row.size() > 2 ? row.get(2).toString() : "";
        System.out.println("Read value from column 3: " + cellValue);

        // Add your business logic here to decide PASS/FAIL
        String result = "PASS";
        // Example condition: if cellValue is empty, mark as FAIL
        // if (cellValue.trim().isEmpty()) { result = "FAIL"; }

        // Add the result to our write list (each entry is a single cell value for the row)
        writeData.add(Arrays.asList(result));
    }
}

Step 2: Write Results to Column 5 #

Now we'll write our collected PASS/FAIL results to column 5 (column E) for the same rows we processed:

// Define the write range: column E, rows 2-6 (matches our read range)
String writeRange = sheetName + "!E2:E6";

// Create the ValueRange object with our results
ValueRange body = new ValueRange().setValues(writeData);

// Execute the update
UpdateValuesResponse updateResult = service.spreadsheets().values()
    .update(spreadsheetId, writeRange, body)
    .setValueInputOption("RAW") // Use "USER_ENTERED" if you want Sheets to parse formulas/formatting
    .execute();

System.out.printf("Successfully updated %d cells in column 5.", updateResult.getUpdatedCells());

Key Notes to Match POI Behavior: #

• Row Alignment: Make sure your read and write ranges cover the exact same rows. In the example, we're using rows 2-6 (since POI's row=1 might skip a header row; adjust to row=1 in Sheets if you don't have a header).
• Index Safety: Always check the row's size before accessing columns to avoid IndexOutOfBoundsException (Google Sheets doesn't return empty cells for sparse rows).
• Batch Operations: Google Sheets API is optimized for bulk updates, so collecting all results first and writing once is far more efficient than writing row-by-row (unlike POI's direct cell access).

Dynamic Row Handling (If You Don't Know Exact Row Count) #

If you need to process all non-empty rows in column C instead of a fixed range:

// Read all values in column C to get the row count
String fullColumnRange = sheetName + "!C:C";
ValueRange fullColumnResponse = service.spreadsheets().values()
    .get(spreadsheetId, fullColumnRange)
    .execute();
List<List<Object>> fullColumnValues = fullColumnResponse.getValues();

if (fullColumnValues != null && !fullColumnValues.isEmpty()) {
    int totalRows = fullColumnValues.size();
    // Adjust ranges to skip header (row 1) and process rows 2 to totalRows
    String dynamicReadRange = sheetName + "!A2:F" + totalRows;
    String dynamicWriteRange = sheetName + "!E2:E" + totalRows;
    // Repeat the read/write logic above with these dynamic ranges
}

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