My webapp backend is in Node.js, used for reading/writing to/from a Google Sheets spreadsheet.
I want to use a query that will retrieve only rows which meet specific conditions, but haven’t been able to do this.
[Note: I’d like to save on traffic, and fetch only the filtered data rather than fetch everything and filter them locally]
The following method receives a single (string) parameter, customerID.
Ultimately I want retrieve the single row where the cutomerID column matches the supplied parameter, and the status column equals New
(column2==customerID) && (column4==’New’)
Here’s what I have so far:
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 range = 'Orders!A3:D9';
const filter = 'column2 = "cid444"';
const myRetrievedData = await sheets.spreadsheets.values. get({
spreadsheetId,
range,
});
console.log('Retrieved from ORDERS tab: ', myRetrievedData.data.values);
} catch (error) {
console.error('Error retrieving draft Order ID :((( ', error);
throw error;
}
Sample contents of the sheet:
| OrderID | CustomerID | OrderDate | Status |
|---|---|---|---|
| X8G3F7 | cid222 | 23FEB2024 | Complete |
| V3B4R3 | cid444 | 18MAR2024 | New |
| E5T2A2 | cid9999 | 18APR2023 | Complete |
| Y5H5J6 | cid444 | 27DEC2023 | progress |
if method is run for cid444, it should retrieve the contents of row 2, but currently running this method results in returning all rows in range which are 3,4,5:
[
[ ‘V3B4R3’ , ‘cid444′ , ’18MAR2024’ , ‘New’ ]
[ ‘E5T2A2’ , ‘cid9999′ , ’18APR2023’ , ‘Complete’ ]
[ ‘Y5H5J6’ , ‘cid444′ , ’27DEC2023’ , ‘progress’ ]
]
in an attempt to evaluate proposed solutions, I added the “filter” variable as a third parameter to values.get,
also tried using “values.batchGetByDataFilter” and defining a filter criteria JSON. None worked so far.
Mostly, errors would look like:
GaxiosError: Invalid JSON payload received.
Unknown name “filter”: Cannot bind query parameter.
Field ‘filter’ could not be found in request message.
Suggestion:
Since myRetrievedData.data.values already returns a 2D array, you can instead use filter to only return rows with a matching CustomerId and a status of New
I was able to get your desired result by replacing:
console.log('Retrieved from ORDERS tab: ', myRetrievedData.data.values);
with:
console.log('Retrieved from ORDERS tab: ',
myRetrievedData.data.values.filter(
row => row[1] == customerID && row[3] == 'New'
));
Edit:
Filtering results based on values is currently not possible via Sheets API. As a workaround, You can query using Google Visualization API instead.
Do try this modified version of your script:
function getDraftOrderByID(customerID) {
const auth = new GoogleAuth({
scopes: "https://www.googleapis.com/ auth/spreadsheets.readonly",
});
auth.getRequestHeaders().then((authorization) => {
const spreadsheetId = "spreadsheet-id"; //Replace this with the spreadsheet ID .
const sheetId = 0; // Please set the sheet ID.
//The Sheet ID is the number that appears after the "gid=" in the spreadsheet URL
let qs = {
gid: sheetId,
tqx: "out:csv",
tq: `Select A,B,C,D Where B = '${customerID}' and D = 'New' Label A '', B '', C '', D ''`,
};
let options = {
url: `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq`,
qs: qs,
method: "get",
headers: authorization,
};
request(options, (err, res, result) => {
if (err) {
console.log("Error retrieving draft Order ID :((( ", err);
return;
}
let data = result.replaceAll('"', '').split(",");
console.log("Retrieved from ORDERS tab:", data);
});
});
}
Reference:
- filter()
2