# json2gsheet

> Serializes JSON data to Google Sheet, and vice versa

Latest version **2.0.3** (published 2025-12-09) · MIT license · 0 weekly downloads

> **Deprecated.** This package is deprecated.

## Install

```sh
npm install json2gsheet
pnpm add json2gsheet
yarn add json2gsheet
bun add json2gsheet
```

Provides the command `json2gsheet`.

## Health

**Score 10/100 (F)** — status: deprecated.

Negative: deprecated.

## Facts

| | |
|---|---|
| Version | 2.0.3 |
| Published | 2025-12-09 |
| First published | 2017-12-04 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | ESM |
| Node | ^12.20.0 \|\| ^14.13.1 \|\| >=16.0.0 |
| Dependencies | 3 |
| Unpacked size | 14.5 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 6 |
| Author | Ling Cong Xiang |
| Maintainers | raphlcx |
| Keywords | json, google-sheets |

## Links

- npm: https://www.npmjs.com/package/json2gsheet
- Repository: https://github.com/raphlcx/json2gsheet
- Homepage: https://github.com/raphlcx/json2gsheet#readme
- Issues: https://github.com/raphlcx/json2gsheet/issues
- npm.io page: https://npm.io/package/json2gsheet

## Dependencies (3)

- [flat](https://npm.io/package/flat.md) ^5.0.2
- [googleapis](https://npm.io/package/googleapis.md) ^109.0.1
- [google-auth-library](https://npm.io/package/google-auth-library.md) ^8.6.0

## Alternatives

- [@mapbox/jsonlint-lines-primitives](https://npm.io/package/@mapbox/jsonlint-lines-primitives.md) — 5.3M weekly downloads
- [reftools](https://npm.io/package/reftools.md) — 3.5M weekly downloads
- [@hey-api/openapi-ts](https://npm.io/package/@hey-api/openapi-ts.md) — 3.5M weekly downloads
- [@mapbox/geojson-rewind](https://npm.io/package/@mapbox/geojson-rewind.md) — 2.4M weekly downloads
- [turbo-stream](https://npm.io/package/turbo-stream.md) — 1.7M weekly downloads

## Recent versions

- 2.0.3 (latest) — 2025-12-09
- 2.0.2 — 2022-11-08
- 2.0.1 — 2022-01-28
- 2.0.0 — 2022-01-20
- 1.0.11 — 2022-01-13
- 1.0.10 — 2021-11-02
- 1.0.9 — 2021-09-16
- 1.0.8 — 2021-06-27
- 1.0.7 — 2021-05-11
- 1.0.6 — 2021-04-30
- 1.0.5 — 2020-09-20
- 1.0.4 — 2020-09-12
- 1.0.3 — 2020-09-10
- 1.0.2 — 2020-07-17
- 1.0.1 — 2020-04-07
- … 18 more at https://npm.io/package/json2gsheet/versions

## README

# json2gsheet

[![npm](https://img.shields.io/npm/v/json2gsheet.svg?style=flat-square)](https://www.npmjs.com/package/json2gsheet)

Serializes JSON data to Google Sheets, and vice versa.

## Installation

```
npm install json2gsheet
```

## Concept

`json2gsheet` pushes JSON keys to a column, and values to another column, and more values for the same key to subsequent columns.

`json2gsheet` uses a token, which is called an `id` in this context, to relate the JSON file and the sheet column where values are pushed to.

For example, given these JSON files, with `id` as the file name without the `.json` suffix:

```
// person1.json
{
  "name": "John",
  "likes": "puppy"
}

// person2.json
{
  "name": "Jane",
  "likes": "cat"
}

// person3.json
{
  "name": "Russell",
  "likes": "bear"
}
```

When pushed to the sheet, this is the result:

```
 Key  | person1 | person2 | person3
-----------------------------------
name  | John    | Jane    | Russell
likes | puppy   | cat     | bear
```

For nested JSON object, it is first flattened when pushed to the sheet. For example:

```
// someCol.json
{
  "parent": {
    "child": "some value",

    "childtwo": {
      "grandchild": "more value"
    }
  }
}
```

becomes:

```
           Key             |  someCol
---------------------------------------
parent.child               | some value
parent.childtwo.grandchild | more value
```

When pulled from the sheet, it is de-flattened to restore the initial nested structure.

## Scope

`json2gsheet` only works with JSON strings, objects, and arrays.

## Usage

### Preparation

In a working directory, prepare these files:

  - `json2gsheet.config.json`

    Configuration file for this application.

  - `client_secret.json`

    Google API credential in JSON format.

To get your `client_secret.json`:

  1. On a Google Cloud Platform project, enable Google Sheets API.
  1. Create a service account, note its email address.
  1. Download the JSON credential file and name it as `client_secret.json`.

On your sheet, grant Editor access to the service account, via its email address.

### Pushing JSON to sheet

```
json2gsheet push <id>
```

What it does:

  1. Read the JSON file identified by `id`
  1. Flatten it to have a single level key-value pairs
  1. Push the list of keys and values to their respective sheet column as specified in the configuration file

### Pulling from sheet to JSON

```
json2gsheet pull <id>
```

What it does:

  1. Pull data from the sheet
  1. De-flatten the data
  1. Write the JSON to a file identified by `id`

Basically the opposite of `push` subcommand.

## Configuration

`json2gsheet` is heavily driven by configurations. You can find a copy of [sample configuration](json2gsheet.config.json) in this repository.

### App configurations

  - `app.jsonFileName`

    The file name template for the JSON file. This is where the position of `id` token is specified, using the placeholder `$id`.

  - `app.command.pull.skipEmptyValue`

    For `pull` subcommand only. If set to `true`, when a cell is empty, the key-value pair represented by this cell will not be inserted in the resulting JSON object.

### Sheets configurations

  - `sheets.spreadsheetId`

    The Google Sheets ID.

  - `sheets.sheetName`

    The name of the sheet to read from or write to. Note, this is not the spreadsheet's file name, but the name of an individual sheet in the spreadsheet file.

 - `sheets.keyColumn`

    The column to push JSON keys to. It is an object containing:

      - `label` for column header label
      - `column` to push to
      - `cellStart` on which cell to start writing from

 - `sheets.valueColumns`

    The columns to push JSON values to.

    This is an array of `valueColumn`. Each object contains:

      - `id` to identify the JSON file
      - `label` for column header label
      - `column` to push to
      - `cellStart` on which cell to start writing from

## Development

Run the tests:

```
npm test
```

To prepare for a new version:

  1. Create a new branch.
  1. Update version on `package.json` and `package-lock.json`.
  1. Make a commit.
  1. Merge the branch.
  1. Create an annotated tag.
  1. Push the tag.

To publish the new version:

```
npm pack
npm publish
```

## License

[MIT](LICENSE)

---
_Source: https://npm.io/package/json2gsheet · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._