GDB

Library for using Google Sheets as a database

Installing

Using npm:

$ npm install @drumtj/gdb

Using cdn:

<script src="https://unpkg.com/@drumtj/[email protected]/dist/gdb.js"></script>

CommonJS

import GDB from '@drumtj/gdb';

const GDB = require('@drumtj/gdb');

Caution

Only Google Sheet with shared link is working.

How To

query language syntax document

https://developers.google.com/chart/interactive/docs/querylanguage

test google sheet

https://docs.google.com/spreadsheets/d/1Ha9S6ZjGobRoIzzIHJeeUKcLBYqooRZ7V7PMCEN3qKg/edit#gid=0

code

var db = new GDB("1Ha9S6ZjGobRoIzzIHJeeUKcLBYqooRZ7V7PMCEN3qKg");
db.query({
  ////// input sheetname or sheetid(gid)
  sheetName: "data",
  //gid: 122345642, //sheetid

  ////// sql query
  // sql: "SELECT *"
  sql : "SELECT B, C, D, E where D is not null"
}).then(function(data){
  console.log("data", data);
  console.log('findColumnKeyByName("author") =>', data.findColumnKeyByName("author"));
  console.log('findColumnKeyByName("author, email") =>', data.findColumnKeyByName("author, email"));
  console.log('findColumnKeyByName(["author", "email"]) =>', data.findColumnKeyByName(["author", "email"]));
  console.log('getColumn("E") =>', data.getColumn("E"));
  console.log('getColumn(findColumnKeyByName("author, email")) =>', data.getColumn(data.findColumnKeyByName("author, email")));
  console.log('getRow(0) =>', data.getRow(0));
  console.log('getHeader() =>', data.getHeader());
});

Interface

class GDB {
  interface QueryOption {
  	sheetName?: string;
  	gid? 			: string | number;
  	sql				: string;
  };

  interface GDBRowData {
    [column: string]: string|number;
  }

  constructor(sheetID:string=null, header:number=2);
  setId(sheetID:string);
  getId();
  query(option:QueryOption):Promise<GDBRowData[]>;
}

data.findColumnKeyByName(columnName:string|string[]):string|string[];
data.getColumn(columnKey:string):any[]|any[][];
data.getRow(index:number):Object;
data.getHeader():any[];

Test

git clone https://github.com/drumtj/gdb.git
cd gdb
npm install
npm start

License

MIT