Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Google Spreadsheet as a DB for Google Apps Script.
JavaScript
branch: master
Failed to load latest commit information.
example add example
src Migrate from Xml to XmlService via http://googleappsdeveloper.blogspo…
README.md Update README.md

README.md

SpreadsheetDB

What's SpreadsheetDB?

  • This Script can be Spreadsheet as a DB for Google Apps Script.
  • It can do select/insert/update/delete Row(s) on Spreadsheet by query.
  • It can full text query to Spreadsheet rows.

How it work?

  • It use List Base Feed on Google Spreadsheet API.

How use it?

  • You can use it as a Library
    • Library ID: MlMEyCNWeCV97wefWrBRcEQV62A3znfOO
  • Do you need code assist? You can use mock library and mini hack for code assist.
    • Mock Library ID: MORnDiFLK6dWUCyoTNsS1HQV62A3znfOO
    • Use both adobe library ids and write below code.
  • var spreadsheetService = GSSDB.createService(spreadsheetId , consumerKey , consumerSecret);
    //var spreadsheetService = SpreadsheetService; it's hack for gas editor. you can do code assist after code.
    

Example

Initializer

var spreadsheetService = GSSDB.createService(ss.getId());
//if you need set ConsumerKey and ConsumerSecret
var spreadsheetService = GSSDB.createService(ss.getId() , "consumerKey" , "consumerSecret");
  • args1 spreadsheet id
  • args2 consumerKey
  • args3 consumerSecret

insert row.

var entry = {
  "id":1,
  "name":"soundTricker"
};

var entry2 = {
  "id":2,
  "name": "soundTricker2"
};

//insert
//If your need insert row, you user insert method
spreadsheetService.insert(ss.getSheets()[0].getName(), entry);
  • arg0 : sheetName
  • arg1 : insert object, {column : value}

select row(s).

var rows = spreadsheetService.query(ss.getSheets()[0].getName(), ""); //if you need all rows,arg1 set empty string.

//maybe rows length is 2, and rows[0]'is 1, rows[1]'id is 2
//query result always return as a array. if result length is 1.
var row = spreadsheetService.query(ss.getSheets()[0].getName() , "id=1");
  • arg0 : sheetName
  • arg1 : query. query reference is http://code.google.com/intl/ja/apis/spreadsheets/data/3.0/reference.html#ListParameters
  • arg2 : advanceObject
  • if you need sorted result or revesed result or full-text query,you set advanceObject.
  • advanceObject field is

    { orderby : "column:columnName" ,//Specifies what column to use in ordering the entries in the feed. reverse:true/false , // Specifies whether to sort in descending or ascending order. q: full-text-query for rows };

update row

spreadsheetService.update(ss.getSheets()[0].getName() , row[0]);
  • arg0 : sheetName
  • arg1 : update object. it shoud be selected object by query method.

delete row

spreadsheetService.deleteEntry(ss.getSheets()[0].getName() , row[0]);
  • arg0 : sheetName
  • arg1 : delete object. it shoud be selected object by query method.

if you update sheetName or add sheet. you should call refleshListKey method

 spreadsheetService.refleshListKey();
Something went wrong with that request. Please try again.