So what I’m trying to do is convert a google form powered spreadsheet to a HA sensor.
I’m at the stage that I’m able to expose my spreadsheet as a JSON API, which should enable me to use the HA REST integration to convert this data into a sensor https://www.home-assistant.io/integrations/rest/
However, the JSON google spits out is… complex. Did some research and found this article which converts the output to something more readable: https://benborgers.com/posts/google-sheets-json/. I think If I’m able to do something like this, the actual conversion to a sensor would be more easy
Can you give me some pointers in how I can convert this data into a similar format doing it the HA native way instead of the javascript way?
By HA native way, if you mean Python, I use code based on this to read Google Spreadsheet for mapping MAC address to human devices names as I monitor devices coming and going on my home network. Not a sensor per se. But I think you could create a sensor in AppDaemon pretty easily using these python Google Sheet libraries:
first pass at python routine to access mikrotik network devices
using python library gspread to read mac address, names and IP's of known addresses
https://github.com/burnash/gspread
https://gspread.readthedocs.io
https://console.developers.google.com/apis/credentials?project=core-folio-33221100
import gspread
#google sheet : IP addresses v3
gc = gspread.service_account()
sh = gc.open("IP addresses v3")
sb_net = sh.sheet1.get_all_values()
for i in sb_net :
print(i[0],i[3],i[2])
ip address Mac Address machine
192.168.2.001 38:70:0C:22:F3:44 ARRIS Cable Modem TG2472G
192.168.2.002 88:3D:24:BB:A2:1B Mikrotik RB4011iGS+5HacQ2HnD-IN-US
192.168.2.003 Mikrotik CRS326-24G-2S+RM
192.168.2.004 Mikrotik MikroTik hAP ac2 ONE
192.168.2.005 00:11:32:A2:EE:0F Synology DS918+ NAS Eth1 nas00
192.168.2.006 00:11:32:A2:D0:40 Synology DS918+ NAS Eth2 nas00
192.168.2.007 48:8F:5A:42:DD:26 Mikrotik MikroTik hAP ac2 TWO