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

Question

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..

I cannot understand about your goal. I apologize for this. From your question, I understood that Google Sheet To Read From is the sample Spreadsheet. If that is used, can I ask you about the expected input and output values? — Tanaike
– Tanaike, Commented Dec 26, 2020 at 0:08
so my main goal is to copy the entire row than contains a certain string. in the sheet i provided in the question there is only one entry, but when i full use there will be more. user enters “>Read 33p2bdlfto9g45“ now this is the bit i can’t get my head around. i now want to query my sheet for any cells that contain the runid, in this case that is “ 33p2bdlfto9g45”. once found the bot will reply saying the runid has been matched and then proceed to save the entire contents of the row which contains the runid as a variable. thanks for your reply and time to look over my questions! — Ben
– Ben, Commented Dec 26, 2020 at 0:32
@Tanaike I have managed to find a solution to my problem, It happens to be a question you answered for somebody else with the same issue as myself. I can now retrieve the data I am querying for perfectly fine. — Ben
– Ben, Commented Dec 26, 2020 at 17:35
Thank you for replying. I'm glad your issue was resolved. When your issue was resolved, when you post it as an answer, it will be useful for other users who have the same issue. — Tanaike
– Tanaike, Commented Dec 27, 2020 at 1:23

Ben · Accepted Answer · 2020-12-27 15:13:04Z

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
    ````

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related