Getting Google spreadsheet data by using like SQL.
This node module can extract user data from google spreadsheet.
And you can use SQL like syntax. e.g:
SELECT * WHERE A = "user1".
Of course we support private spreadsheet data also.
$ npm install g-spreadsheet-sql
Google Spreadsheet Query Language Specification
You can use the syntax like SQL according to google spreadsheet query language specification.
var PublicSpreadsheet = PublicSpreadsheet;// first argument is spreadsheet key, second argument is worksheet name.// spreadsheet key is included spreadsheet URL.// e.g: ""var spreadsheet = 'SPREADSHEET_KEY' 'WORKSHEET_NAME';return spreadsheet;
Executing above snippet, you can get json format result.
For private spreadsheet you need to get google OAuth 2.0 credentials before using this module. Please see Using OAuth 2.0 to Access Google APIs page for details. In almost cases you can generate new credentials in API console. After generating new OAuth 2.0 credential you usually have 3 credential values.
- Client ID
- Client Secret
- Redirect URN
And also you can get manually Oauth 2.0 refresh token optionally.
You need to give above 3 values to the constructor of
PrivateSpreadSheet. From v0.3.0 refresh token became to be optional.
var PrivateSpreadsheet = PrivateSpreadsheet;var spreadsheet ='SPREADSHEET_KEY''WORKSHEET_NAME''CLIENT_ID''CLIENT_SECRET''REDIRECT_URN''REFRESH_TOKEN' // refresh token is optional;return spreadsheet;
About Handling Expiry of Access Token and Refresh Token
As we mentioned refresh token is optional from v0.3.0.
googleapis/google-auth-library-nodejs module can handle access token expiry.
So when you prefer use this module for getting spreadsheet data just only once, we recommend you to use without refresh token. Though when you need to get spreadsheet data repeatedly, you should use this module with refresh token.
googleapis/google-auth-library-nodejs tries to refresh access token when the access token is over expiry. If you would like to understand in code level, please check here.
Declaring Columns Like RDB
This module assumes first row as headers. So you have to create following structure on spreadsheet.
We use ava test framework.
npm run build
This is a forked project from jupemara/spreadsheet-sql