TqSheet

Query, fetch, and export Google Sheets data without any setup using GViz API.

Overview

TqSheet helps you fetch, query, and export data from public Google Sheets without any setup like API keys or OAuth.

• Query data using a simple SQL-like syntax
• Export data to PDF, XLSX, CSV, or TSV
• Data is parsed internally and ready to use

Installation & Usage

Installation

Browser

Include the script tag:

<script src="https://cdn.jsdelivr.net/gh/rezzvy/tqsheet@c2d3449/dist/tqsheet.min.js"></script>

Node

Install via npm:

npm install tqsheet

const TqSheet = require("tqsheet");

Usage

(async () => {
  const result = await TqSheet.get("https://docs.google.com/spreadsheets/d/1-aoJ48IO31blJEW-0lzNj282pp-HKNAKTzT9Bl-7AUo/edit?usp=sharing", {
    query: "SELECT *",
  });

  console.log(result);
})();

Examples

Query specific data and return as object

(async () => {
  const result = await TqSheet.get("https://docs.google.com/spreadsheets/d/1-aoJ48IO31blJEW-0lzNj282pp-HKNAKTzT9Bl-7AUo/edit?usp=sharing", {
    query: "SELECT * WHERE A = 'Reza'",
    asObj: true,
  });

  const row = result.data.rows[0];

  console.log(row.Name);
  console.log(row.Gender);
  console.log(row.Age);
  console.log(row.City);
  console.log(row["Registration Date"]);
})();

Export to PDF (Download)

(async () => {
  const blob = await TqSheet.export("https://docs.google.com/spreadsheets/d/1-aoJ48IO31blJEW-0lzNj282pp-HKNAKTzT9Bl-7AUo/edit?usp=sharing", "pdf");
  const url = URL.createObjectURL(blob);

  const a = document.createElement("a");
  a.href = url;
  a.download = "sheet.pdf";
  document.body.appendChild(a);
  a.click();
})();

Documentation

API Reference

get()

Fetches and parses data from a Google Sheet.

Parameters

| Param | Type | Description | | :-------- | :------- | :------------------------------------------------------ | | url | string | A valid Google Sheets URL. | | options | object | (Optional) Options to customize the query and output. |

options properties

| Property | Type | Default | Description | | :--------- | :-------- | :----------- | :-------------------------------------------------------------------- | | query | string | "SELECT *" | Query string to filter or select data. | | sheet | string | "Sheet1" | Name of the sheet to use. | | asObj | boolean | false | Return rows as objects using column labels as keys. | | skipNull | boolean | false | Skip null values. Automatically returns rows as objects when enabled. |

Returns

On success:

{
  data: {
    columns: [...],
    rows: [...]
  },
  message: "ok"
}

On error:

{
  data: null,
  message: "ERR_MSG" // e.g. "Invalid query or empty response" or "HTTP Error {CODE}"
}

raw()

Fetches data directly from the Google Visualization API and returns the parsed response as-is, without additional formatting.

Parameters

| Param | Type | Description | | :-------- | :------- | :------------------------------------ | | url | string | A valid Google Sheets URL. | | options | object | (Optional) Options for the request. |

options properties

| Property | Type | Default | Description | | :------- | :------- | :----------- | :------------------------------------- | | query | string | "SELECT *" | Query string to filter or select data. | | sheet | string | "Sheet1" | Name of the sheet to use. |

Returns

Returns a parsed JSON object from the Google gviz endpoint.

export()

Exports a Google Sheet into different file formats.

Parameters

| Param | Type | Description | | :---------- | :------- | :------------------------------------------------------------------------------------ | | url | string | A valid Google Sheets URL. | | type | string | (Optional) File format: "csv", "tsv", "pdf", or "xlsx". Default is "csv". | | sheetName | string | (Optional) Name of the sheet to export. |

Returns

• Returns a Blob for binary formats (pdf, xlsx)
• Returns a string for text formats (csv, tsv)

Data Querying

To learn more about queries and available syntax, check out the official documentation: https://developers.google.com/chart/interactive/docs/querylanguage

Contributing

There's always room for improvement. Feel free to contribute!

Licensing

The project is licensed under MIT License. Check the license file for more details.