Using Google Sheets to Manage Configs and 'Array of Objects' Data

[dudleyjosh]

I'm just sharing a pattern I've used over-and-over with my various Node-RED projects that has worked really well for me in case others might find it useful (or perhaps this is commonplace and I am just late to the game).

Recently I've done quite a bit of work in the manufacturing automation space and I've been using Node-RED to communicate with industrial PLCs via Modbus... and I've found it much simpler to organize "objects" related to that data using Google Sheets and then use Node-RED to pull it using a web service driven by a Google Apps Script that converts the spreadsheet data to an array of objects (each row is an object with the column header as the key). And I simply use the "sheet names" to reference which data I want to pull in to Node-RED and then typically store the data as a global or flow level object.

I'm sure you could also accomplish this by using one of the Google Sheets Node-RED nodes instead of the Google Apps Script ContentService.

Anyhow, the point is that I often find it much more convenient to use Google Sheets to organize & update data objects and it's simple enough to automate pulling that data over to Node-RED

Hope others might find this helpful and perhaps spark other ideas.

[Colin]

@dudleyjosh

I often find it much more convenient to use Google Sheets

More convenient than what?

[dudleyjosh]

More convenient than coding large arrays of objects directly in JSON. When you have a thousand or more Modbus addresses (or even just a 100) to deal with, it's much easier to manage in a spreadsheet format... at least it is for me. For example, I'm working on the one below today... and I wouldn't want to manage all 144 of these objects directly in JSON... so I have them in a spreadsheet and pull them over as needed.

[Colin]

Do you need internet access to read Google sheets?

[dudleyjosh]

Yes, but if that is an issue... you could still use Google Sheets and an instance of Node-RED that does have internet access to pull down the sheet data as JSON and then just copy and paste... could still be useful... just adds one manual step.

[TotallyInformation]

You could, of course simply use a local CSV file instead of Google sheets. That removes the Internet requirement though you would probably also want a locally installed CSV editor. Too many options to mention for that. Including Office, Open Office/Libre Office but there are plenty of smaller, simpler tools as well.