google-sheets-data-fetcher v1.1.7

google-sheets-data-fetcher

Fetch data from one or multiple google sheets, without any API key.

Highlights

• no api key required
• fetch multiple sheets and/or "sub sheets" at once
• output as json objects or csv
• write data to separate output files
• 2 usages:
  • as classical package/dependency
  • via cli/command line
• small package size ( < 40kB )
• written in typescript
• tested via Jest

Introduction

Installation

For cli / command line usage

If you want to use the google-sheet-data-fetcher as a command line tool you have 2 options - you can either use it directly via npx or install it globally on your system.

npx

npx google-sheets-data-fetcher [options]

global install

Via npm:

npm i -g google-sheets-data-fetcher

Via yarn:

yarn global add google-sheets-data-fetcher

For classical package / dependency usage

Just install via npm...

npm i google-sheets-data-fetcher --save

... or for yarn use:

yarn add google-sheets-data-fetcher

Usage

First of all, you need to determine a google sheet's ids, simply by looking at its URL.

Example URL:

In this example URL, the part that is underlined in green, would be the main sheet id, while the blue part would be the currently viewed "sub sheet" id.

Use these kind of id's according to your desired outcomes (command line fetch, etc. - details below).

For cli / command line usage

If you installed it for cli usage as described above, you can afterwards just use it on the command line by typing gsdf (short for google-sheet-data-fetcher), followed by appropriate options. To see available options, type gsdf --help, or check the following table:

Getting data from multiple google sheets at once

The -s and -o flags above can be provided multiple times, in case you need to fetch data from multiple google sheets at once.

Example: gsdf -f JSON_COLUMNS -s 19VnV0Hu0IcwkIqUJndKeUNSSne2OvpLKSSccDiHPmw4,0,1086688112 -s 197U_3ZQaL7jN0sPZvKPhQRN-1tKfwr_VXL09b8W5zpI -o a1.json,a2.json -o b.json

For package / dependency usage

Import the main function like so:

import { fetchGoogleSheetsData } from "google-sheets-data-fetcher";

You can then use the fetchGoogleSheetsData function by providing 2 arguments, where the 1st describes the sheets to be fetched, and the second one describes the desired output format(s).

Example:

const result = fetchGoogleSheetsData(
  // 1st argument:
  [
    {
      sheetId: "19VnV0Hu0IcwkIqUJndKeUNSSne2OvpLKSSccDiHPmw4", // from the example URL above
      subSheetsIds: [
        "0",
        "1086688112", // from the example URL above
        "2100601117",
      ],
      outputFileDestinations: [
        "./1stSubSheet.json",
        "./2ndSubSheet.json",
        "./3rdSubSheet.json",
      ],
    },
    {
      sheetId: "197U_3ZQaL7jN0sPZvKPhQRN-1tKfwr_VXL09b8W5zpI",
      subSheetsIds: ["0", "201058147"],
      outputFileDestinations: ["./firstSubSheet.json", "./secondSubSheet.json"],
    },
  ],
  // 2nd argument:
  ["JSON_COLUMNS"]
);

According to the 1st argument the example above would fetch data from 2 Google Sheets at once, and will also fetch the individual "sub sheets" accordingly, because the subSheetIds are being provided. You can also omit this prop - in that case it would only fetch the first sub sheet's data, which always corresponds to "0".

The optional prop outputFileDestinations will allow you to not only retrieve the data within the function's return value (as shown here, to be stored in const result) but also write the data to separate json files. If you provide this prop, you need to make sure, that the number of provided output file names exactly matches the number of (sub)sheets to be fetched. Also make sure, that you are not re-using certain filenames, as this would lead to overwriting files.

In the 2nd argument you need to specify an array of one or multiple desired output formats.

Details regarding output formats can be found below.

If you provide more than one desired output format, the return value will be an object, holding the desired multiple formats, like so:

const outputFormats = ["JSON_COLUMNS", "JSON_ROWS"];
// ...
// => result:
// {
//   JSON_COLUMNS: { ... data in column oriented format ... },
//   JSON_ROWS: { ... data in row oriented format ... }
// }

Output formats

"JSON_RAW"

interface RawFormat {
  reqId: string;
  sig: string;
  status: string;
  table: {
    cols: { id: string; label: string; type: string }[];
    rows: { c: { v: string }[] }[];
  };
}

"JSON_COLUMNS"

interface ColumnFormat {
  [columnKey: string]: {
    id: string;
    label: string;
    type: string;
    rows: {
      [rowKey: string]: { id: number; data: string };
    };
  };
}

"JSON_ROWS"

interface RowFormat {
  [rowKey: string]: {
    id: number;
    columns: {
      [columnKey: string]: {
        id: string;
        label: string;
        type: string;
        data: string;
      };
    };
  };
}

"CSV"

, as cell separators,

" as cell value holders

and newlines \n as data entry separators