Inspired by 55sketch/gsx2Json, preserve all functinality of origin and implement additional features.
One useful feature of Google Spreadsheets is the ability to access the data as JSON by using a particular feed URL. However, this is a bit fiddly to do, and the resulting JSON is pretty unreadable, with usable data buried deep inside objects.
This API connects to your spreadsheet and santizes the data, providing simple, readable JSON for you to use in your app.
Make sure docker engine has already install in your operating-system.
In this example below I'm going to use 5000 as port, and output log file under bin/volume directory.
-
Launch with a remote image
docker pull deflinhec/gsx2jsonpp:latestdocker run -it -d --expose 5000 \ -v ${PWD}/bin/volume:/workspace \ -e ARGUMENTS="--host 0.0.0.0 --port 5000" \ --name gsx2jsonpp-latest deflinhec/gsx2jsonppCommand line for Windows platform
docker run -it -d --expose 5000 ` -v ${PWD}\bin\volume:/workspace ` -e ARGUMENTS="--host 0.0.0.0 --port 5000" ` --name gsx2jsonpp-latest deflinhec/gsx2jsonpp -
Launch with a local image
Follow instructions below, 🧰 Build from source.
docker build --rm -t gsx2jsonpp .docker run -it -d --expose 5000 \ -v ${PWD}/bin/volume:/workspace \ -e ARGUMENTS="--host 0.0.0.0 --port 5000" \ --name gsx2jsonpp gsx2jsonppCommand line for Windows platform
docker run -it -d --expose 5000 ` -v ${PWD}\bin\volume:/workspace ` -e ARGUMENTS="--host 0.0.0.0 --port 5000" ` --name gsx2jsonpp gsx2jsonpp
After launched, Gsx2Jsonpp should be accessable in your browser localhost:5000.
Supervisor have been setup within the container to guarantee an auto restart after accidentially crashed(hopefully not).
CMake version must greater than 3.14.0, requires openssl, zlib, brotli installed.
Comparing to docker image, executable size will be way more smaller than using an docker image.
-
Using build script
git submodule update --initAvaliable argument: Release|Debug|Project (Default: Release)
bash scripts/build.sh ReleaseCommand line for Windows platform
Push-Location scripts; .\build.ps1; Pop-Location-
Launch with executable
bin/Gsx2Jsonpp --host 0.0.0.0 -p 5000Command line for Windows platform
Push-Location bin; .\Gsx2Jsonpp.exe --host 0.0.0.0 -p 5000; Pop-Location
-
First, you must publish your spreadsheet to the web, using File -> Publish To Web in your Google Spreadsheet.
Second, locate to your spreadsheet id and sheet number.
https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit#gid=[SHEET_NUMBER]
You can then access your readable JSON API using the /api endpoint.
http://example.com/api?id=SPREADSHEET_ID&sheet=SHEET_NUMBER
-
Column name begin with
NOEX_will not export to the final result. -
Make sure to add a left most column represents as an unique integer key.
-p, --port (default: 8080): Depends on your network environment.
--host (default: localhost): Depends on your network environment.
--cache ( file | memory | none - default: none): This feature allows client to query on specific data version. When cache is configure with file, cache file will be preserve under cache/ folder. In order to trigger an update, you will have to removes files under cache/ folder manaually or restarts your container if configured with memory.
--ssl ( true | false - default: false): Copy your certification and key into ${PWD}/bin/volume and rename as cert.pem, key.pem. Self-signed certification will be auto generates if absent.
id (required): The ID of your document. This is the big long aplha-numeric code in the middle of your document URL.
sheet (optional): The number of the individual sheet you want to get data from. Your first sheet is 1, your second sheet is 2, etc. If no sheet is entered then 1 is the default.
q (optional): A simple query string. This is case insensitive and will add any row containing the string in any cell to the filtered result.
integers (optional - default: true): Setting 'integers' to false will return numbers as a string.
dict (optional - default: true): Setting 'dict' to false will return rows and columns view.
rows (optional - default: true): Setting 'rows' to false will return dictionary and columns view.
columns (optional - default: true): Setting 'columns' to false will return dictionary and rows view.
meta (optional - default: false): Setting 'meta' to true will return only meta data.
pretty (optional - default: false): Pretty print the result if sets to true.
Test localhost:5000 with example spreadsheet
There are four sections to the returned data.
- Columns (containing the names of each column)
- Dictionary (used left most column as primary key)
- Rows (containing each row of data as an object)
- Meta (contains short brief of target data)
Returned data
{
"columns": {
"key": [
1,
2,
3,
4
],
"column1": [
"1b",
"2b",
"3b",
"4b"
],
"column2": [
11,
22,
33,
44
]
},
"rows": [
{
"key": 1,
"column1": "1b",
"column2": 11
},
{
"key": 2,
"column1": "2b",
"column2": 22
},
{
"key": 3,
"column1": "3b",
"column2": 33
},
{
"key": 4,
"column1": "4b",
"column2": 44
}
],
"dict": {
"1": {
"key": 1,
"column1": "1b",
"column2": 11
},
"2": {
"key": 2,
"column1": "2b",
"column2": 22
},
"3": {
"key": 3,
"column1": "3b",
"column2": 33
},
"4": {
"key": 4,
"column1": "4b",
"column2": 44
}
},
"meta": {
"columns": {
"md5": "EAC2F0EF3EA62CEEDD3B65B627B06CBA",
"bytes": 73
},
"rows": {
"md5": "7767981744A818A7574B4A0B8EBE1C25",
"bytes": 153
},
"dict": {
"md5": "76C73EAEAFC8BA2ACD890C50E20C1613",
"bytes": 169
},
"time": "2021-02-07T20:00:04.552Z"
}
}
-
✅ docker image
-
✅ md5 checksum
-
✅ cache