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

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.

Screen Shot 2020-12-17 at 11.21.50

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 :slight_smile:

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

1 Like

More convenient than what?

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.

Do you need internet access to read Google sheets?

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.

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.