node-red-contrib-publicgooglesheets

Node-RED

public-google-sheets-parser wrapper.

Install

Run the following command in the root directory of your Node-RED install:

npm install node-red-contrib-publicgooglesheets

Usage

publicgooglesheets

publicgooglesheets api request node.

It is a simple parser that helps you use public Google sheets document as if they were a database.

The document to be used must be a Google Sheets document in the 'public' state and have a header in the first row. (e.g. Google sheets for example)

There is a limitation that only the data of the first sheet can be imported, but it seems that it can be fully utilized for simple purposes, so I made it.

It does not work in browsers where the fetch API is not available.

No API key required. This means that the server does not need to use the private key to use the SDK.

If you have a public spreadsheet document, and the first row is a header and you have more than one row of data, you can call it free of charge through this API and use the result as a JSON response.

spreadsheetId

• need only public spreadsheet ID

sample flow

• Ctrl+c & Ctrl+v by import function

[{"id":"5287f509.66249c","type":"inject","z":"27616db5.40b352","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":620,"y":60,"wires":[["572df9fa.39c148"]]},{"id":"a499660e.12d2e8","type":"debug","z":"27616db5.40b352","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1010,"y":60,"wires":[]},{"id":"82807216.1063c","type":"http in","z":"27616db5.40b352","name":"","url":"/googlesheets","method":"get","upload":false,"swaggerDoc":"","x":150,"y":100,"wires":[["e7300abe.757e98"]]},{"id":"3a9e7400.38055c","type":"http response","z":"27616db5.40b352","name":"","statusCode":"","headers":{},"x":990,"y":100,"wires":[]},{"id":"e7300abe.757e98","type":"function","z":"27616db5.40b352","name":"set spreadsheetId param","func":"// sample public google sheets id\n// 10WDbAPAY7Xl5DT36VuMheTPTTpqx9x0C5sDCnh4BGps\n\nmsg.spreadsheetId = msg.payload.id;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":390,"y":100,"wires":[["7a5aa419.7fa13c"]]},{"id":"572df9fa.39c148","type":"publicgooglesheets","z":"27616db5.40b352","spreadsheetId":"","x":790,"y":100,"wires":[["3a9e7400.38055c","a499660e.12d2e8"]]},{"id":"7a5aa419.7fa13c","type":"delay","z":"27616db5.40b352","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"30","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":600,"y":100,"wires":[["572df9fa.39c148"]]}]

result

[
    {
        "a": 1,
        "b": 2,
        "c": 3
    },
    {
        "a": 4,
        "b": 5,
        "c": 6
    },
    {
        "a": 7,
        "b": 8,
        "c": 9
    }
]