CODEWITHSUNDEEP
node.jsgoogle-sheetsgoogle-api
Ben
Ben

Reputation: 23

using node.js to query a google sheet and return row

So I am building a discord bot which is using google sheets like a database. I would like the user to be able to enter a command with a unique ID which every row will have and then return all the values of said row and store than as a variable. which will then later be used elsewhere.

The command the user would run is ">Read (UNIQUEID)", then I would like the bot to reply with, "(UNIQUEID) has been found" if it is found and "(UNIQUEID) can not be found".

The user would then use the second command ">Upload" to essentially upload that entire row to a new spreadsheet.

I am confident I can finish the >Upload code by myself, but for the life of me I dont know how to query using the google sheets api. for some reference below I have attached an image of my sheet I want to read from.

Google Sheet To Read From

There would also be a lot more information in the google sheet, but for testing purposes I left it with only one entry.

Edit 1: I have been looking into the Google Visualization API, I did a bit of research but Im not sure this would actually work with sheets? From some of the documentation it seems like its for charts..

Upvotes: 0

Views: 1180

Answers (1)

Ben
Ben

Reputation: 23

I found the answer to my issue from another stackoverflow question which was also answered by @Tanaike. Below is the code.

const request = require("request");
        const csvParse = require("csv-parse");
        const spreadsheetId = "DOCUMENT_ID";  // Document ID
        const sheetId = "SHEET_ID";  // sheet ID
        const searchId = `${QUERY}`;  // what im searching for
        
        spread.getRequestHeaders().then((authorization) => {
          const query = `select * where K='${searchId}'`; // K can be changed to the col the data is on
          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;
            }
            if (result === ("")) {
            return message.channel.send(`\`${QUERY}\` Did not match to any records.`); //ID didnt match
            } else {
            message.channel.send(`\`${QUERY}\` has been matched with; ${result}`) //confirms ID has matched
            csvParse(result, {}, (err, ar) => console.log(ar)) // console logs results
    ````

Upvotes: 1

Related Questions