Can I query a googleSheet using googleapis npm package?

I have a sheet that has thousands of rows and I am trying to query it instead of fetching everything every time.
I took a look at the Query language, but it doesn't seem to fit my case.

I'm using a Service account to authenticate:

const auth = new google.auth.GoogleAuth({
credentials: googleCredentials,
scopes: "https://www.googleapis.com/auth/spreadsheets",
});

and using the batchGet to get multiple tabs at once.

const standardSpreadsheetData = await sheets.spreadsheets.values.batchGet({
auth,
spreadsheetId: regularSpreadsheetId,
ranges: regularRanges,
});

But I can't seem to figure where I would be able to add the query.

What I am trying to query is something like this:

select * where B = ''9831"

Any ideas on where to look?

I believe your goal is as follows.

• You want to retrieve the row values by the query like select * where B = ''9831".
• You want to achieve this using the service account with googleapis for Node.js.

Modification points:

• I guessed that your query of select * where B = ''9831" might be select * where B = '9831'.

• Unfortunately, in the current stage, the query like select * where B = '9831' cannot be used with Sheets API. In this case, it is required to use the endpoint for the query language using the access token. Ref

When these points are reflected in a sample script, how about the following sample script?

Sample script:

const { google } = require("googleapis");
const request = require("request");

// Please use your script here.
const auth = new google.auth.GoogleAuth({
  credentials: googleCredentials,
  scopes: "https://www.googleapis.com/auth/spreadsheets",
});

const spreadsheetId = "###"; // Please set the Spreadsheet ID.
const sheetId = "###"; // Please set the search text. In your case, it's ID.
const query = "select * where B='9831'"; // This is the query for searching.

auth
  .getAccessToken()
  .then((accessToken) => {
    const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURIComponent(query)}`;
    request(
      {
        url,
        method: "GET",
        headers: { authorization: `Bearer ${accessToken}` },
      },
      (err, res, result) => {
        if (err) {
          console.log(err);
          return;
        }
        if (result != "") {
          const [, ...res] = result.split("\n").map((e) =>
            e.split(",").map((f) => {
              const t = f.replace(/"/g, "");
              return isNaN(t) ? t : Number(t);
            })
          );
          console.log(res);
        }
      }
    );
  })
  .catch((err) => console.log(err));

• The access token is retrieved from the service account. And, the access token is used for the request. When this script is run, an array including the searched row values is shown.

Note:

• In this script, the result value is exported as CSV data. And it is parsed with a simple script.
• This script is a simple sample script. So, if parsing the CSV data is not correct, please use the parser of CSV data for Node. js.

Reference

• Query Language Reference