useGoogleSheets

A React Hook wrapper library for google-sheets-mapper for getting data from Google Sheets API v4

Installation

npm install use-google-sheets

pnpm add use-google-sheets

bun add use-google-sheets

Package available on unpkg.

Usage

1. Go to Google Cloud Console to get API key for Google Sheets API.
2. Create a Google Sheet and add some data. See example sheet.
3. Share it with "Anyone with this link can view".
4. Get sheet id from url of the sheet.

https://docs.google.com/spreadsheets/d/[THIS-IS-THE-SHEET-ID]/

5. I suggest adding API key and sheet id to .env file

Examples

Get data from all sheets inside the spreadsheet

import useGoogleSheets, { Status } from "use-google-sheets";

const App = () => {
  const { status, data, error } = useGoogleSheets({
    apiKey: process.env.REACT_APP_GOOGLE_API_KEY,
    sheetId: process.env.REACT_APP_GOOGLE_SHEETS_ID,
  });

  if (status === Status.pending) {
    return <div>Loading...</div>;
  }

  if (status === Status.error) {
    return <div>Error!</div>;
  }

  return <div>{JSON.stringify(data)}</div>;
};

Get data from specific sheets inside the spreadsheet

Don't use single quotes on sheet names, they will be removed because when using space in sheet name it will be returned wrapped with single quotes and plugin will remove them for clean string id.

import useGoogleSheets, { Status } from "use-google-sheets";

const App = () => {
  const { status, data } = useGoogleSheets({
    apiKey: process.env.REACT_APP_GOOGLE_API_KEY,
    sheetId: process.env.REACT_APP_GOOGLE_SHEETS_ID,
    sheetsOptions: [{ id: "Sheet1" }],
  });

  if (status === Status.pending) {
    return <div>Loading...</div>;
  }

  if (status === Status.error) {
    return <div>Error!</div>;
  }

  return <div>{JSON.stringify(data)}</div>;
};

Refetch data from all sheets inside the spreadsheet

import useGoogleSheets, { Status } from "use-google-sheets";

const App = () => {
  const { status, data, refetch } = useGoogleSheets({
    apiKey: process.env.REACT_APP_GOOGLE_API_KEY,
    sheetId: process.env.REACT_APP_GOOGLE_SHEETS_ID,
  });

  if (status === Status.pending) {
    return <div>Loading...</div>;
  }

  if (status === Status.error) {
    return <div>Error!</div>;
  }

  return (
    <div>
      <div>{JSON.stringify(data)}</div>

      <button onClick={refetch}>Refetch</button>
    </div>
  );
};

API Documentation

The useGoogleSheets hook takes an object with three properties:

| Name | Value | | ------------- | ------ | | apiKey | string | | sheetId | string | | sheetsOptions | array |

• apiKey is a Google Sheets API v4 key from Google Cloud Console.
• sheetId is the id of the sheet.
• sheetsOptions is an array of specific objects { id, headerRowIndex }. Can be left out then it will fallback to all sheets inside the spreadsheet and use first row from sheet as header.

Exposed Data

The hook produces an HookState object:

const { status, data, error, refetch, loading } = useGoogleSheets({
  apiKey,
  sheetId,
});

| Name | Value | | ------- | --------------------------------------- | | status | "pending" | "success" | "error" | | data | array | | error | null or object | | refetch | function | | loading | boolean (deprecated, use status) |

• status indicates the current state of the request:
  • pending - request is in-flight
  • success - data received successfully
  • error - request failed
• loading is true when status === "pending" (deprecated).
• data is an array of mapped data objects.

[
  {
    id: "Sheet1",
    data: [
      { key: "language", value: "et" },
      { key: "title", value: "Test sheet" },
    ],
  },
];

• error lets you know when there is something wrong. It returns an error object where you can get specific error properties from error.response

{
  status: '404',
  statusText: '',
  url: 'https://sheets.googleapis.com/v4/spreadsheets/...',
}

Status Constant

Import the Status constant for status comparisons:

import { Status } from "use-google-sheets";

Status.pending; // "pending"
Status.success; // "success"
Status.error; // "error"

Migration from v2 to v3

• New status field: "pending" | "success" | "error" (aligned with TanStack Query)
• loading is still available but deprecated (use status === "pending")
• called has been removed

Migration from v1 to v2

• Change sheetsNames array of string to sheetsOptions array of objects with { id: 'sheetName' }