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

**URL:** <https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661>\
**Category:** General\
**Created:** [17 December 2020 16:57 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661 "2020-12-17T16:57:07Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dudleyjosh](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dudleyjosh/32/105799_2.png) [@dudleyjosh](https://discourse.nodered.org/u/dudleyjosh)\
**Post date:** [17 December 2020 16:57 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/1 "2020-12-17T16:57:07Z")

</div>

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.16.21](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/455e0fa7d3184ab8d5617ee5bb4a5c3034b8fc45.png)

 ![Screen Shot 2020-12-17 at 11.21.28](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7bb4f2315ed52f01cd9bbc8132e4d11487ab9f79.png)

![Screen Shot 2020-12-17 at 11.21.50](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/5/c5f85e22b566d8901e7b822e200306f36fada978.png)

 ![Screen Shot 2020-12-17 at 11.30.39](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/86da46bc142ef7dd305e498ec8fe482807392906.png)

 ![Screen Shot 2020-12-17 at 11.47.37](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/2/221a1452594a27255c6a6d22bb76beba7d688277.png)

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2020 17:09 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/2 "2020-12-17T17:09:06Z")

</div>

> [@dudleyjosh](#):
>
> I often find it much more convenient to use Google Sheets

More convenient than what?

---

<div class="post-metadata">

**Author:** ![dudleyjosh](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dudleyjosh/32/105799_2.png) [@dudleyjosh](https://discourse.nodered.org/u/dudleyjosh)\
**Post date:** [17 December 2020 17:33 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/3 "2020-12-17T17:33:26Z")

</div>

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.

 ![Screen Shot 2020-12-17 at 12.35.33](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/1/e17ae55d2059d9befd35bb8e6a5492d28a9e544d.png)

 ![Screen Shot 2020-12-17 at 12.39.12](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/4/646e03179a9734165d2da90174501e0328ae6dbd.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2020 17:54 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/4 "2020-12-17T17:54:01Z")

</div>

Do you need internet access to read Google sheets?

---

<div class="post-metadata">

**Author:** ![dudleyjosh](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dudleyjosh/32/105799_2.png) [@dudleyjosh](https://discourse.nodered.org/u/dudleyjosh)\
**Post date:** [17 December 2020 18:00 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/5 "2020-12-17T18:00:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [17 December 2020 18:07 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/6 "2020-12-17T18:07:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [15 February 2021 18:07 UTC](https://discourse.nodered.org/t/using-google-sheets-to-manage-configs-and-array-of-objects-data/37661/7 "2021-02-15T18:07:40Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.