CODEWITHSUNDEEP
node.jsgoogle-sheetsgoogle-sheets-api
brownveg
brownveg

Reputation: 23

google sheet api nodejs

i have data in google sheet fist column is id with every id there are associated data . i want to get multiple rows in one search by using specific id no. for that i want to use batchGetByDataFilter i dont know how to use because i am new to google sheet . kindly help me . async function gsrun(cl){

const gsapi=google.sheets({version:'v4',auth:cl}) 
let data = await gsapi.spreadsheets.values.batchGetByDataFilter({ 
  spreadsheetId: '1rRS3jugb-txthDdZ0x0nGSzyLna64mBmKnUVkditeTM',
  range: 'Sheet1!a1:g100'
})       

console.log(data.data.values)
}

Upvotes: 0

Views: 1140

Answers (1)

Tanaike
Tanaike

Reputation: 201693

I believe your goal as follows.

  • You want to retrieve the rows by searching an ID at the column "A" using Node.js.
  • In your script, you have already done the authorization process for retrieving the values from Spreadsheet.

For this, how about this answer?

In this case, I would like to propose to use the query language instead of batchGetByDataFilter. By this, the searched rows can be directly retrieved by the query language. I thought that this method is simpler. The sample script is as follows.

Sample script:

In this script, the access token is used from cl of const gsapi=google.sheets({version:'v4',auth:cl}). In this case, the modules of request and csv-parse are used.

const request = require("request");
const csvParse = require("csv-parse");

const spreadsheetId = "###";  // Please set the Spreadsheet ID.
const sheetId = "###";  // Please set the sheet ID.
const searchId = "###";  // Please set the search ID.

cl.getRequestHeaders().then((authorization) => {
  const query = `select * where A='${searchId}'`;
  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURI(query)}`;
  let options = {
    url: url,
    method: "GET",
    headers: authorization,
  };
  request(options, (err, res, result) => {
    if (err) {
      console.log(err);
      return;
    }
    csvParse(result, {}, (err, ar) => console.log(ar));
  });
});

References:

Upvotes: 2

Related Questions