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/1mrsgBk4IAdSs8Ask5H1z3bWYDlPTKplDIU_FzyktrGk/edit

And this is url to the API: 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:

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).

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:

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:

// 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() JavaScript function.

Create

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

// 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);
});

By default, all create operations are performed on the first sheet (worksheet). 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).

// 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).

// Delete all rows where 'player' equals 'Smith' in sheet 'Sheet2'
client.delete(
  'player', // column name
  'Smith', // value to search for
  '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:

// 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 and make a pull request.

Have question?

If you have any questions feel free to ask us via chat or .