Identifying switches and sensors (for home automation config)

I'm looking for advice on the best way to load settings that identify which type of device will be connected to each pin on an Arduino Mega.

This is for my home automation system. I'll have multiple Mega's around my home which detect sensors (light switch presses, temperature changes or motion etc) and control things (lights on/off, relays etc). I'm using MQTT for communication between the Arduinos and the controlled devices.

The Megas will all have different devices connected to them and I want to have only one sketch that's used by them all, I don't want to write a custom sketch for each one just to set up the pin configs.
I'd like to have something like a spreadsheet with a list of the device and type assigned to each pin of each Arduino. The Arduino's will all have POE sheilds so I can identify them all by IP or mac address in the spreadsheet.
Each Arduino will load its list of assigned devices and types from the list, saving me from writing unique code for each Arduino.
Ideally the list will be in a simple format that can easily be loaded from my local network and is accessible when my internet connection is down.

What's the best way to store such a list?
Can this be done with a Google docs sheet, synced on a local PC?
What other options are there and how can I keep this really simple?

Thanks!

[I renamed this topic from "Loading a list from a local network (for home automation config)" because the discussion has taken a different direction]

Ideally, I think you would want each Mega to work out for itself what was connected to it, so no configuration was needed at all.

For some types of sensor, that's easy. If you could use only i2c sensors, for example, each sensor should have an address on the bus which would identify what type it was. For example, an sht21 temp/humidity sensor has address 0x40.

Many i2c I/o chips have a number of addresses (e.g. 8 ) which you can choose by setting pins of the chip. If you use those, you could decide that address 5 was for a chip that controls relays, and address 3 for a chip to which simple sensors are attached, such as door or window open sensors.

Your sketch could scan the bus at startup and record what addresses responded, which would tell it which devices are connected to this Mega.

If you simply want a single sketch to load onto all your Mega's with a fixed config then maybe you can write code that reads the mac address from the shield on startup and configures itself appropriately. This requires no external storage but its obviously not re-configurable on the fly. Its also going to have the problem that you will have to update code if you have to replace a shield or something else changes.

If you want a system that can be re-configured on the fly then you have a harder task. As far as I can tell an MQTT broker cannot easily obtain the IP or MAC address of the client. You may find this thread useful as an alternative.

If you can overcome that particular issue then the retrieval of information from google docs and caching on whatever machine you are using as your local MQTT broker shouldnt really be that hard. If you provide more detail in what you are trying to achieve you are likely to get more detailed answers.

Thanks for the info.

Those i2c sensors look interesting. I will be connecting cat6 wires between the Arduino pins and the existing light switches (removing the mains power obviously) for detecting light switch presses. I don't know of any way to detect the device type here - is there an i2c board for this? (I tried searching but don't know what they're called.)
Or is there some other way to make a simple switch detectable by Arduino?

The reason I chose Mega's is all those pins can detect a lot of light switches and if I add an I2C shield or board it would need to have plenty of pins.
I'm guessing each Mega will have about 50% light switches and 50% other stuff (temp,humidity etc).

RW - I thought about loading something from an SD card whenever the config changes but it's so much nicer to do it over the network. I could even send an MQTT message telling the Arduinos to reload their config.
Of course, I like the idea of auto detecting the devices but I'm going to document all the devices anyway and the decision now is whether those docs are used to config the Arduinos. Doing so would force me to keep my docs up to date.

The MQTT broker does not need to know the addresses of the clients. If the config list includes the mac or IP then each Arduino will only load configs for it's own IP and ignore the rest. Thanks for that link though, nice to know that can be done.

To identify the light switch pins I suppose I could have a standard where they are all from D1-Dx and Dx+1 has some kind of simple identifier on it that would get picked up by a scan. Any I2C devices could start from the last pin and work backwards.

For example if I have 20 light switches they would be on D1-D20 and D21 would be the "end of light switches" device.
What kind of cheap and simple thing could I plug in to D21 for this purpose?

Thanks,
Phil

I have an idea for your "end of light switches" device. The device could be a simple resistor, say 220K, connected between the pin and ground. This is a higher value and therefore a weaker pull-down than the Arduino's internal pull-up resistor, which is around 20~50K. With this connected, if you read the pin having set pinMode(pin, INPUT) it would read LOW because of the 220K. If you then read it again having set pinMode(pin, INPUT_PULLUP) it will read HIGH because the internal pull-up resistor would overcome the external pull-down resistor.

Where real switches are connected, they would have to be wired to connect to either ground or 5V at all times. Reading that signal, using either INPUT or INPUT_PULLUP would make no difference, the same value would be read, and the Mega would know a real switch was connected to the pin.

To configure itself, the Arduino would need to scan the switches starting with the real switches, until it hit the "end of light switches" device and then stop. If it read an unconnected pin, that input would be floating, and reading it would not reliably indicate if it was a real switch or the end device.

As for i2c chips to read inputs or control relay modules, the cheapest around is pcf8574. It has 8 input/output pins. However, its internal pull-ups cannot be controlled in the same way as an Arduino pin, so it would not be able to detect the "end of light switches" device.

The two other common types are mcp23008 (8 input/outputs) and mcp23017 (16 input/outputs). Their internal pull-ups can be controlled like that of an Arduino pin, so the "end of light switches" device idea might work with them.

As a bonus, all i2c devices share the same 2 Arduino pins. So you might not need a Mega in some circumstances, an Uno or Nano would be ok.

Thanks Paul, the resistor idea sounds perfect. I'll give that a try.
I'd like to have as few boards and shields as possible so I'll come back to the mcp23017 if the resistor doesn't work.

Thanks,
Phil