Uh oh!
There was an error while loading.Please reload this page.
- Notifications
You must be signed in to change notification settings - Fork10
coderofsalvation/Gexpress-middleware-RESTsheet
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Gexpress middleware to expose spreadsheet as REST endpoints
var app = new Gexpress.App() // see https://github.com/coderofsalvation/Gexpressvar sheet = SpreadsheetApp.openById('1AImZywpGLsOWZafgyHUHBo')var person = GexpressTamotsu.middleware('/person', {sheet:sheet,tab:'persons'})app.use( person )Voila! now the following urls are exposed:
| url | will return |
|---|---|
GEThttps://{scripturl}/?path=/person | all rows from 'persons'-sheettab |
GEThttps://{scripturl}/?path=/person/123 | get row with value '123' in column '#' |
DELETEhttps://{scripturl}/?path=/person/123&method=DELETE | remove row with value '123' in column '#' |
POSThttps://{scripturl}/?path=/person&method=POST {...} | append (person) jsondata to 'persons'-sheettab |
PUThttps://{scripturl}/?path=/person/123&method=PUT {...} | update person '123' with jsondata |
- Include thelatest version of this library (
1u4tNXyogsenLfbzOYk7JCyxzgxvJSo2GtdmI3pfUKWtodYIyWMXQ89NX) (see screenshot) - IncludeGexpress in similar fashion
Setup the sheet:
NOTE: make sure to format the '#'-column as 'plain text'. Also extract the sheet id from the url (https://docs.google.com/spreadsheets/d/{id}/edit#gid=0 and put it into the openById(..)-call.
OPTIONAL: you can put json-strings in columns for nested data (it will be parsed automatically).
| query param | example | info |
|---|---|---|
| ?query=.. | {active:1} | mongodb-ish query to match candidates in sheet |
| ?limit=.. | 4 | return max 4 results |
| ?offset=.. | 0 | skip n items from result, for pagination purposes |
| ?order=.. | 'date_modify DESC' | sort results on date_modify column |
EXAMPLE:
https://{scripturl}/?path=/person&limit=5&offset=0&order=[date_modify]&query={"active":1}
Gexpress automatically generate a JS client, so here's how to extend it:
app.get('/client.js', app.client(function(code){ return code + person.generateClientCode() }) )Voila, now you can run the following in yourjquery/vue/react/whatever-app after including
<script src="https://script.google.com/{SCRIPTID}/exec?path=/client.js"></script>in your html:
gclient.user.get('l2k3l').then( console.dir ).catch( console.error ) gclient.user.delete('l2k3l').then( console.dir ).catch( console.error ) gclient.user.put('l2k3l',{...data..}).then( console.dir ).catch( console.error ) gclient.user.post({...data..}).then( console.dir ).catch( console.error ) // the following assumes columns '#', 'date_created' and 'active' to exist in your spreadsheet gclient.user.find({active:1},{offset:0,limit:10,order:['date_created']}).then( console.dir ).catch( console.error )NOTE: this middleware is based ontamotsu
var opts = { sheet:sheet, tab:'foo', query: {active:1}, // default 'where'-query limit: 25, // default limit on .all() results order: ['date_modify'] // default order on .all() results } opts.tamotsu = { // generated output properties fullName: function() { // for options see https://github.com/itmammoth/Tamotsu return [this['First Name'], this['Last Name']].join(' '); } } var person = GexpressTamotsu.middleware('/foo',opts ) // lets hook into GET /person person.get = function(req,res,handler){ if( req.route == '/person/:id' ){ var result = handler() // to access sheetdata: handler.table.where({foo:12}).all() return result; } if( req.url == '/person' ){ var result = handler() result.items = result.items.map( function(person){ var forbidden = ['email','phone'] forbidden.map(function(f){ delete person[f] }) return person }) return result }else return handler() } app.use(person)This would be a basic query:
https://{scripturl}/?path=/person&limit=5&offset=0&order=[date_modify]&query={"active":1}
Which could be extended further like this:
https://{scripturl}/?path=/person&limit=5&offset=0&order=['-date_modify','price']&query={"$or":[{price:5},{name:"foo"}]}
As you can see['-date_modify','price']: ordering can take place using an array of properties (the minus-sign flips between ASC/DESC).
| Greater than | $gt |
| Greater Equal than | $gte |
| Less than | $lt |
| Less Equal than | $lte |
| Strict equality | $eq |
| Strict inequality | $ne |
| Like | $like |
| Not like | $nlike |
| RegExp | $regex |
| In | $in |
| Not in | $nin |
| And | $and |
| Or | $or |
| Nor | $nor |
| Not | $not |
For detailed usage seemongoqueries. To convert searchterms to mongoqueries seehuman-search-mongoquery
- ✓ GET /foo
- ✓ GET /foo/:id
- ✓ POST /foo
- ✓ PUT /foo/:id
- ✓ DELETE /foo/:id
- ✓ support for 'query'-arg
- ✓ support for 'limit'-arg
- ✓ support for 'order'-arg
- ✓ support for 'offset'-arg
- ✓ automatically parse JSON in columns
- ✓ added mongoquery support
- ✓ added multiple orderby support
- ◔ more tests
About
Gexpress middleware to expose Gspreadsheet as REST endpoints in 4 lines of appscript
Resources
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Releases
Sponsor this project
Uh oh!
There was an error while loading.Please reload this page.