Automatically track your weight from a BT scale in Google Sheets

Share your Projects!
1

I did a thing, now I’m fat :confused:

image
image1108×546 55.3 KB

But now I can make charts in Google Sheets. So there’s that.

Synopsis

Step on scale - weight auto-records to a Google Sheet for daily tracking

Requirements

None. I was bored, found a bargain bin BT scale and then this just happened. I learned a few things along the way.

The scale

A SPRI branded scale that broadcasts weight to the world using a Cheapsea manufacturer id. I learned how to extract hex byte array data, so that’s good.

The important ESPHome yaml bits;

sensor:
  - platform: template
    name: "embarrassment"
    id: myweight
    unit_of_measurement: 'lb'
    
esp32_ble_tracker:
  on_ble_advertise: 
    - mac_address: '5c:ca:d3:ff:60:45' # MAC address in lowercase of Cheapsea-BLE
      then:
        - lambda: |-
            /*
            ESP_LOGD("ble_adv", "  Advertised manufacturer data:");
            for (auto data : x.get_manufacturer_datas()) {
                ESP_LOGD("ble_adv", "    - %s: (length %i)", data.uuid.to_string().c_str(), data.data.size());
                ESP_LOGD("ble_adv", "    - %s:", format_hex_pretty(data.data).c_str());
            }
            */
            for (auto data : x.get_manufacturer_datas()) {
              if(data.data.size() == 18) {                  
                uint16_t weight = uint16_t(data.data[2]) | (uint16_t(data.data[3]) << 8);
                weight = weight / 4.533333333333333;
                id(myweight).publish_state(weight);
              }
            }

There are a bunch of Github repo’s/gists based around these scales (OpenScale…) but they didn’t match the bytearray length or indexes. So be prepared to run the dev branch of the ESPHome Add-On if you need to use format_hex_pretty()

HA Automation

As straighforward as they get…

  trigger:
  - platform: state
    entity_id: sensor.embarrassment
  condition:
  - condition: template
    value_template: '{{ states(''sensor.embarrassment'') not in [''unavailable'',''unknown'',''none'']
      }}'
  action:
  - service: shell_command.submit_weight_form
    data: {}
  mode: single

The Google parts

Getting the data into HA is pretty straightforward. Getting the data “into Google” took some investigation as it was a new endeavor for me. Sometimes when there are lots of libraries, API’s, modules, etc, it can be overwhelming.

I started looking at API keys, restricting IP’s and POST’ing data, but then found this idea. Use a Google Form to append a Google Sheet. Is it open and insecure? Yes. Do I have a requirement for security? No. Will people complain about this? Yes. Find my form :stuck_out_tongue:

The great part about Google Chrome is that the Developer Tools literally gives you a copy/paste curl command for any form submission.

shell_command:
  submit_weight_form: curl 'https://docs.google.com/forms/u/0/d/e/top_secret_form_id/formResponse' --data-raw 'entry.11235325423={{states("sensor.embarrassment")}}' --silent >/dev/null

Success!

image

4 Likes