Node.js调用Google Sheets API筛选符合指定条件的行（节省流量）

问题背景 #

需要在Node.js后端通过Google Sheets API获取仅符合指定条件的行：CustomerID列匹配传入参数，且Status列等于"New"，避免获取全部数据后本地过滤以节省流量。原代码使用spreadsheets.values.get无法实现远程筛选，尝试添加filter参数或使用values.batchGetByDataFilter时因参数格式错误报错：

GaxiosError: Invalid JSON payload received.
Unknown name "filter": Cannot bind query parameter.
Field 'filter' could not be found in request message.

解决方案 #

Google Sheets API的spreadsheets.values.get不支持筛选参数，需使用spreadsheets.values.batchGetByDataFilter方法，并正确构造dataFilters参数来定义筛选条件。以下是修改后的完整代码：

async function getDraftOrderByID(customerID) {
    try {
        const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets.readonly' });
        const sheets = google.sheets({ version: 'v4', auth });
        const spreadsheetId = 'MY-SPREADSHEET-ID';

        const response = await sheets.spreadsheets.values.batchGetByDataFilter({
            spreadsheetId,
            requestBody: {
                dataFilters: [
                    {
                        range: {
                            sheetId: 0, // 替换为Orders工作表的实际ID，可在表格URL的gid参数中获取
                            startRowIndex: 2, // 对应A3，行索引从0开始计数
                            endRowIndex: 9, // 对应D9，结束索引不包含目标行
                            startColumnIndex: 0,
                            endColumnIndex: 4
                        },
                        filterSpecs: [
                            {
                                columnIndex: 1, // CustomerID列（B列，列索引从0开始计数）
                                filterCriteria: {
                                    condition: {
                                        type: 'EXACT_MATCH',
                                        values: [{ userEnteredValue: customerID }]
                                    }
                                }
                            },
                            {
                                columnIndex: 3, // Status列（D列，列索引从0开始计数）
                                filterCriteria: {
                                    condition: {
                                        type: 'EXACT_MATCH',
                                        values: [{ userEnteredValue: 'New' }]
                                    }
                                }
                            }
                        ]
                    }
                ]
            }
        });

        const filteredRows = response.data.valueRanges[0].values;
        console.log('筛选后的订单数据: ', filteredRows);
        return filteredRows;
    } catch (error) {
        console.error('获取订单数据失败: ', error);
        throw error;
    }
}

关键说明 #

1. 工作表ID（sheetId）：可在Google Sheets的URL中提取，格式为https://docs.google.com/spreadsheets/d/[spreadsheetId]/edit#gid=[sheetId]，替换代码中的0为实际值。
2. 行/列索引规则：API中索引从0开始计数，A3对应startRowIndex:2，D9对应endRowIndex:9（结束索引不包含目标行）；B列（CustomerID）对应columnIndex:1，D列（Status）对应columnIndex:3。
3. 筛选逻辑：filterSpecs数组中的多个条件默认是**逻辑与（AND）**关系，刚好匹配需求中的(CustomerID == customerID) && (Status == 'New')。

测试结果 #

当传入customerID = 'cid444'时，将仅返回符合条件的行：

[ [ 'V3B4R3', 'cid444', '18MAR2024', 'New' ] ]

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