# simple-sheets

> Provides a simple, promise-based API for reading and writing Google Sheets data.

Latest version **1.1.2** (published 2018-04-05) · MIT license · 0 weekly downloads

## Install

```sh
npm install simple-sheets
pnpm add simple-sheets
yarn add simple-sheets
bun add simple-sheets
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.1.2 |
| Published | 2018-04-05 |
| First published | 2017-11-26 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 1 |
| Unpacked size | 18.1 KB |
| Known vulnerabilities | 0 (+1 in 1 direct dependencies) |
| Install scripts | no |
| GitHub stars | 4 |
| Author | Kyle Coberly |
| Maintainers | kylecoberly |
| Keywords | google, sheets, oauth2, simple, reading, writing |

## Links

- npm: https://www.npmjs.com/package/simple-sheets
- Repository: https://github.com/kylecoberly/simple-sheets
- Homepage: https://github.com/kylecoberly/simple-sheets#readme
- Issues: https://www.github.com/kylecoberly/simple-sheets/issues
- npm.io page: https://npm.io/package/simple-sheets

## Dependencies (1)

- [googleapis](https://npm.io/package/googleapis.md) ^16.1.0

## Alternatives

- [@clerk/clerk-expo](https://npm.io/package/@clerk/clerk-expo.md) — 133.6K weekly downloads
- [@pothos/plugin-authz](https://npm.io/package/@pothos/plugin-authz.md) — 12.4K weekly downloads
- [@bounded-sh/client](https://npm.io/package/@bounded-sh/client.md) — 3.2K weekly downloads
- [@luigi-project/plugin-auth-oauth2](https://npm.io/package/@luigi-project/plugin-auth-oauth2.md) — 2.3K weekly downloads
- [@nocobase/plugin-verification](https://npm.io/package/@nocobase/plugin-verification.md) — 2.0K weekly downloads

## Recent versions

- 1.1.2 (latest) — 2018-04-05
- 1.1.1 — 2018-01-08
- 1.0.4 — 2018-01-07
- 1.0.3 — 2018-01-01
- 1.0.2 — 2017-11-26
- 1.0.1 — 2017-11-26
- 1.0.0 — 2017-11-26

## README

# Simple Sheets

Reads and writes Google Sheets row data, perfect for sheets populated by Google Forms. This is a wrapper for the very-powerful-yet-overwhelming official Sheets API.

Authenticates via a [Google Service Account](https://cloud.google.com/iam/docs/understanding-service-accounts) by passing in the `client_email` and `private_key` values provided by the `.json` file that Google Service Accounts generate. See [service-account-credentials.json](service-account-credentials.json) for an example.

Now includes tools for clearing and seeding sheets with data, which is useful for writing automated tests against spreadsheets.

## API

### `getRows`

```js
getRows(rows, options).then();
```

It returns a promise that resolves to an object that has arrays with the requested mappings.

`rows` is an array of objects that have the following properties:

```js
[{
    label: "people", // This will be the label of the array
    range: "A2:B", // In A1 format
    mapping: ["firstName", "lastName"] // These are what the columns will be labeled
},{
    label: "locations",
    range: "'Cities'!C2:C",
    mapping: ["city"]
}]
```

`options` include:

* `spreadsheetId` (required): The ID of the Google sheet, which is the long string in the URL of the page
* `clientEmail` (required): The authorized `client_email` for your service account (remember to add permissions for this email to your sheet!)
* `privateKey` (required): the authorized `private_key` for your service account
* `dateTimeFormat`: An optional override of the date format that will return

#### Usage

```js
const {getRows} = require("simple-sheets-reader");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

getRows([
    label: "people",
    range: "A2:B",
    mapping: ["firstName", "lastName"]
},{
    label: "locations",
    range: "'Cities'!C2:C",
    mapping: ["city"]
], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "test-account@fast-ability-145401.iam.gserviceaccount.com",
    privateKey: PRIVATE_KEY,
    dateTimeFormat: "FORMATTED_STRING" // Default value, can be overridden to "SERIAL_NUMBER"
}).then(response => {
    /*
    {
        people: [{
            firstName: "Kyle",
            lastName: "Coberly"
        },{
            firstName: "Elyse",
            lastName: "Coberly"
        }],
        cities: [{
            city: "Denver"
        },{
            city: "Seattle"
        }]
    }
    */
}).catch(console.error);
```

### `updateRows`

```js
updateRows(data, options).then();
```

`data` is an array of objects, following the following format:

```js
[{
    range: "A2:A",
    values: [
        ["A"],
        ["B"]
    ]
}]
```

`options` include:

* `spreadsheetId` (required): The ID of the Google sheet, which is the long string in the URL of the page
* `clientEmail` (required): The authorized `client_email` for your service account (remember to add permissions for this email to your sheet!)
* `privateKey` (required): the authorized `private_key` for your service account
* `valueInputOption`: Whether input should be taken literally (`"RAW"`), or as if a user entered them (`"USER_ENTERED"`, default)

It returns a count of modified rows.

#### Usage

```js
const {updateRows} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

updateRows([{
    range: "A2:A",
    values: [["A"], ["B"]]
},{
    range: "Users!A2:B",
    values: [["C", "D"], ["E", "F"]]
}], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "test-account@fast-ability-145401.iam.gserviceaccount.com",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    {
        updatedRows: 4
    }
    */
}).catch(console.error);
```

### `addRows`

```js
addRows(range, data, options);
```

* `range` is an A1 range (eg "A2:A") that will be searched to find something table-like to append to the end of.
* `data` is an array of arrays of values to add:

```js
[
    ["column 1", "column 2"],
    ["column 1", "column 2"]
]
```

`options` include:

* `spreadsheetId` (required): The ID of the Google sheet, which is the long string in the URL of the page
* `clientEmail` (required): The authorized `client_email` for your service account (remember to add permissions for this email to your sheet!)
* `privateKey` (required): the authorized `private_key` for your service account
* `valueInputOption`: Whether input should be taken literally (`"RAW"`), or as if a user entered them (`"USER_ENTERED"`, default)

It returns an object with the count of modified rows:

#### Usage

```js
const {addRows} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

addRows("'Form Responses'!A2:B", [
    ["column 1", "column 2"],
    ["column 1", "column 2"]
], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "test-account@fast-ability-145401.iam.gserviceaccount.com",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    {
        updatedRows: 2
    }
    */
}).catch(console.error);
```

### `clearSheets`

```js
clearSheets(sheets, options).then();
```

`sheets` is an array of sheet names, following the following format:

```js
["First Sheet Name", "Second"]
```

`options` include:

* `spreadsheetId` (required): The ID of the Google sheet, which is the long string in the URL of the page
* `clientEmail` (required): The authorized `client_email` for your service account (remember to add permissions for this email to your sheet!)
* `privateKey` (required): the authorized `private_key` for your service account

It returns an array containing the counts of modified rows in each sheet.

#### Usage

```js
const {clearSheets} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

clearSheets(["First Sheet Name", "Second"], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "test-account@fast-ability-145401.iam.gserviceaccount.com",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    [{
        updatedRows: 4
    },{
        updatedRows: 2
    }]
    */
}).catch(console.error);
```

### `seedSheets`

```js
seedSheets(sheets, options).then();
```

`sheets` is an array of sheet objects, following the following format:

```js
[{
    sheetName: "Sheet 1",
    mapping: ["column1", "column2"],
    seedData: [{
        column1: "a",
        column2: "b",
    },{
        column1: "c",
        column2: "d",
    }]
},{
    sheetName: "Sheet 2",
    mapping: ["column1", "optionalColumn"],
    seedData: [{
        column1: "e",
        optionalColumn: "f"
    },{
        column1: "g"
    }]
}]
```

Please note the following:

* This method assumes the first row is headers
* The mapping must include all column labels, in order
* The seed columns can be entered in any order (and can even be omitted), but must have a matching mapping value

`options` include:

* `spreadsheetId` (required): The ID of the Google sheet, which is the long string in the URL of the page
* `clientEmail` (required): The authorized `client_email` for your service account (remember to add permissions for this email to your sheet!)
* `privateKey` (required): the authorized `private_key` for your service account

It returns an array containing the counts of modified rows in each sheet.

#### Usage

```js
const {clearSheets} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

seedSheets([{
    sheetName: "Sheet 1",
    mapping: ["column1", "column2"],
    seedData: [{
        column1: "a",
        column2: "b",
    },{
        column1: "c",
        column2: "d",
    }]
},{
    sheetName: "Sheet 2",
    mapping: ["column1", "optionalColumn"],
    seedData: [{
        column1: "e",
        optionalColumn: "f"
    },{
        column1: "g"
    }]
}],{
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "test-account@fast-ability-145401.iam.gserviceaccount.com",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    [{
        updatedRows: 2
    },{
        updatedRows: 2
    }]
    */
}).catch(console.error);
```

---

Look at the [Google Sheets `batchGet` API Docs](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet), the [Google Sheets `batchUpdate` API Docs](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet) and the [Google Sheets `append` API Docs](ihttps://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append) for more information.

## Testing

`npm test`

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