# Google Spreadsheet data with Node.js application

If you need to add Google Spreadsheet support to your Node.js application, you've come to the right place. SheetDB is a service that allows you to read, create, edit and delete Google Sheets rows. Using the REST API, we will be able to perform all these actions, let's start by adding a new API in the SheetDB panel. Create a new API using the URL sheet, you will receive a unique ID of your API.

In this article we're gonna use this spreadsheet: [https://docs.google.com/spreadsheets/d/1YiMOrIFnMaksK032-zv6Sch2tTVTRhEpsFom\_5qSLXM/edit](https://docs.google.com/spreadsheets/d/1YiMOrIFnMaksK032-zv6Sch2tTVTRhEpsFom_5qSLXM/edit)

And this is url to the API: [https://sheetdb.io/api/v1/58f61be4dda40](https://sheetdb.io/api/v1/58f61be4dda40)

## First steps

Ok, now let's start hacking with Node.js. We have prepared the `sheetdb-node` open source package for you. To install it, enter this command in the root folder of your Node application:

```bash
npm install sheetdb-node --save
```

Now we need to import this package and configure it. There should be only one reference to sheetdb-node, but you can create multiple clients - each client is one linked API (spreadsheet).

```javascript
const sheetdb = require('sheetdb-node');
// create a config file
let config = {
  address: '58f61be4dda40',
};
// Create new client
// This is linked to API you can call it like your spreadsheet
let client = sheetdb(config);
```

If you have HTTP Basic Authentication turned on for your API, you should pass `auth_login` and `auth_password` in a config:

```javascript
const sheetdb = require('sheetdb-node');
// create a config file
var config = {
  address: '58f61be4dda40',
  auth_login: 'BASIC_AUTH_login',
  auth_password: 'BASIC_AUTH_password',
};
// Create new client
var client = sheetdb(config);
```

## Reading data from SheetDB with Node.js

To read the spreadsheet we can use the `read()` method. You can pass the following attributes:

- `limit` - the number of rows that should be returned
- `offset` - row from which it should start (how many rows to skip)
- `search` - object with search params
- `sheet` - get data from named worksheet (tab), first sheet by default

This is easiest to explain in the code, so let me give you a few examples:

```javascript
// Get first two rows from the first worksheet
client.read({ limit: 2 }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

// Get 3rd and 4th record from worksheet named "Sheet2"
client.read({ limit: 2, offset: 2, sheet: 'Sheet2' }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

// Get all rows where column 'id' is '1' and column 'name' is 'Tom'
client.read({ search: { id: "1", name: "Tom" } }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

// Get first row where column 'player' is 'Smith',
// column 'score' is '41' from sheet named "Sheet2"
client.read({
  limit: 1,
  search: { 'player': 'Smith', 'score': 41 },
  sheet: 'Sheet2'
}).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});
```

Hope these examples are helpful. Of course, instead of `console.log(data)`, you can use whatever logic your application needs. The data variable contains a string in a JSON format, so if you want to work on the object use the [JSON.parse()](https://www.w3schools.com/js/js_json_parse.asp) JavaScript function.

## Create

To add data to Google Spreadsheets, send an object or an array of objects like this:

```javascript
// Add a single row
client.create({ name: "William", age: 25 }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

// Add bunch of rows
rows = [
  { name: "William", age: 25 },
  { name: "Jayden", age: 25 }
]
client.create(rows).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

// Pass name of a sheet as a 2nd param to add data to other worksheet.
// Adds single row to worksheet named "Sheet2"
client.create({ player: "William", score: 75 }, "Sheet2").then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});
```

## Update

To update row(s), pass column name and its value which is used to find row(s).

```javascript
// Update all columns where 'name' is 'Smith' to have 'score' = 99 and 'comment' = 'Updated'
// In sheet named 'Sheet2'
client.update(
  'name', // column name
  'Smith', // value to search for
  { 'score': 99, 'comment': 'Updated' }, // object with updates
  'Sheet2'
).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});
```

## Delete

To delete row(s), pass column name and its value which is used to find row(s).

```javascript
// Delete all rows where 'player' equals 'Smith' in sheet 'Sheet2'
client.delete(
  'player', // column name
  'Smith', // value to search
  'Sheet2'
).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});
```

## Node.js server

If you want to use SheetDB with a Node.js server, you have a small boilerplate here:

```javascript
// A BASIC Node server
const http = require("http");
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' })

const server = http.createServer(function(req, res) {
  res.setHeader("Content-type", "application/json");
  res.setHeader("Access-Control-Allow-Origin", "*");
  res.writeHead(200); //status code HTTP 200 / OK

if (req.url == '/get') {

// Node SheetDB - Get data
    client.read().then(function(data) {
      res.end(data); // return data
    }, function(error){
      console.log(error);
    });

}
});

server.listen(1234, function() {
  console.log("Listening on port 1234");
});
```

## Open source

This package is open source, if you would like to contribute to it please visit [GitHub page](https://github.com/sheetdb/sheetdb-node) and make a pull request.

## Have question?

If you have any questions feel free to ask us [via chat](https://sheetdb.io/use-cases/google-spreadsheet-data-with-node-js#) or [support@sheetdb.io](mailto:support@sheetdb.io).