How to query the Google Sheets with Timestamp using Node

Question

I need to query the google sheet from my node application where I need to mention the query for start and end date's timestamp. My code is as follows,

const GoogleSpreadsheet = require('google-spreadsheet');
const {promisify} = require('util');
const moment = require('moment');

const credentials = require('./credentials.json');

const startofday = moment().startOf('day').format('M/D/YYYY HH:mm:ss').toString();
const endofday = moment().endOf('day').format('M/D/YYYY HH:mm:ss').toString();


async function accessSpreadsheet(){
    const doc = new GoogleSpreadsheet('1EEhdBIdFy6S2dwgqMrZhJXfsAmmCaZpXuWUsJwIFOew');
    await promisify(doc.useServiceAccountAuth)(credentials);
    const info = await promisify(doc.getInfo)();
    const sheet = info.worksheets[0];
    const rows = await promisify(sheet.getRows)({
        query: `timestamp = ${startofday}:${endofday}`
    });
    console.log(rows);
}

accessSpreadsheet();

I found your question very vague. What is the error you are getting? What is exactly what you want from the query? Is your timestamp a column? What library are you using? Also It would be nice if you could share a sample sheet. [How to Ask](https://stackoverflow.com/help/how-to-ask) — Raserhin, Dec 09 '19 at 10:25
@Raserhin I have added sample image and my code block for reference. — Raphael Ragul, Dec 09 '19 at 14:06
It seems like this library is using an [old version of the API](https://developers.google.com/sheets/api/v3/data#send_a_structured_query_for_rows). To do what you want to do should try to call the correct endpoint of the new version. In this case you could use [getByDataFilter](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/getByDataFilter) — Raserhin, Dec 09 '19 at 14:56

score 2 · Answer 1 · edited Apr 16 '20 at 09:23

You want to retrieve the rows including the column "A" to "H" by searching with the range of date time.
You want to achieve this using Node.js.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In this answer, the searched values are retrieved by Query Language for Google Spreadsheet. Because in the current stage, The Google Sheets v3 API will be shut down on March 3, 2020. This has already been mentioned by Raserhin's comment.

Usage:

In this script, googleapis for Node.js is used. So if you had never installed it, please install it as follows. You can also see the sample script for this at Node.js Quickstart.

$ npm install googleapis

or

$ npm install -g googleapis

Sample script:

Before you run the script, please set the variables of ### client_email of service account ###, ### private_key of service account ###, spreadsheetId and sheetId. And then, please confirm whether the Spreadsheet is shared with the email of a service account.

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

const auth = new google.auth.JWT(
  "### client_email of service account ###",
  null,
  "### private_key of service account ###",
  ["https://www.googleapis.com/auth/spreadsheets.readonly"]
);

auth.getRequestHeaders()
.then((authorization) => {
  const spreadsheetId = "###";  // Please set the Spreadsheet ID.
  const sheetId = "###";  // Please set the sheet ID.

  const startofday = moment().startOf('day').format('YYYY-MM-DD HH:mm:ss').toString();
  const endofday = moment().endOf('day').format('YYYY-MM-DD HH:mm:ss').toString();
  let qs = {
    "gid": sheetId,
    "tqx": "out:csv",
    "tq": `Select A,B,C,D,E,F,G,H Where A >= datetime '${startofday}' and A <= datetime '${endofday}'`,
  };
  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(err);
      return;
    }
    console.log(result);
  });
});

Note:

This sample script can be used for your sample Spreadsheet in your question. So if your actual Spreadsheet is different from the sample image, this script might not work. At that time, please modify the script. Please be careful about this.
About ### private_key of service account ###, please set your private key like "-----BEGIN PRIVATE KEY-----\n###\n-----END PRIVATE KEY-----\n".
In this case, the query is Select A,B,C,D,E,F,G,H Where A >= datetime '${startofday}' and A <= datetime '${endofday}'.
In this case, the result is returned as CSV format.
At the sample script, the searched values are directly retrieved. But also you can retrieve the values from the Spreadsheet using Sheets API and can retrieve the searched values by processing the retrieved values. In this case, I think that googleapis will be useful.

References:

If I misunderstood your question and this was not the result you want, I apologize.

How to query the Google Sheets with Timestamp using Node

1 Answers1

Usage:

Sample script:

Note:

References:

Linked