调用Google Sheets API v4的values:batchGet接口时遇Invalid JSON Payload Error

Let's break down why you're hitting that 400 error and how to properly filter your sheet data by username.

Why the Error Happens #

The values:batchGet endpoint of Google Sheets API v4 doesn't recognize a username query parameter—that's exactly why you're seeing the "Field 'username' could not be found" message. This endpoint only accepts its predefined parameters (like ranges, majorDimension, valueRenderOption, etc.), so tacking on custom filter parameters directly to the URL won't work.

Correct Ways to Filter Data by Username #

Here are two reliable methods to get the filtered data you need:

1. Use the query Parameter with values:get #

The spreadsheets.values.get endpoint supports a query parameter that lets you run a Google Sheets QUERY function-style filter directly via the API. This is the most efficient way to handle server-side filtering.

Example request URL (replace placeholders with your actual values):

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{sheetRange}?query=select * where {usernameColumn} = 'andika_1317'&key={yourAPIKey}

• {spreadsheetId}: Your target spreadsheet's unique ID
• {sheetRange}: The range to query (e.g., Sheet1!A:Z to cover all columns in Sheet1)
• {usernameColumn}: The column letter where usernames are stored (e.g., B if usernames live in column B)

2. Fetch All Data and Filter Client-Side #

If you need to use values:batchGet (for example, to pull multiple ranges at once), you'll have to:

• Fetch the full dataset using values:batchGet with valid range parameters
• Write client-side code (in your preferred language) to filter the returned rows where the username matches andika_1317

This method works but is less efficient for large datasets, since you're pulling more data than you need upfront.

Key Takeaway #

Always stick to the API's predefined parameters when constructing requests—adding custom fields like your username parameter will trigger "Invalid JSON Payload" errors because the API has no way to process them.

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