Steps to connect your spools' remaining filament in the AMS to a Google Sheet

README.md

AMS to GSheet

Prerequisites

• Bambulab printer (A1 mini, A1, P1P, P1S, X1, X1C, X1E).
• Home Assistant with experience how to use it.
• Google account (i.e. Gmail)

Steps

1. Google Sheets
   1. Create a Google Sheet.
   2. Add columns: Brand, Type, Color, Quantity, Price, Note, Opened, Last Used, Color, RFID, Desired Use, URL, Months (since opening).
   3. Put this in M2 (Column Months) and drag down: =IFERROR(IF($G2="";""; DATEDIF($G2; NOW(); "M"));"")
   4. Menu Extensions, Apps Script.
   5. Add the content of apps_script.js to it.
   6. Deploy the Apps Script. Important: Give everyone access. Note the URL.
2. Google Cloud
   1. Create a Google Cloud Project.
   2. Follow https://support.google.com/cloud/answer/6158849?hl=en#zippy=%2Cnative-applications%2Cdesktop-apps to create a Oauth Client Secret as a Desktop Application.
3. Home Assistant
   1. Install https://github.com/greghesp/ha-bambulab in Home Assistant.
   2. Edit Home Assistant's configuration.yaml. Update it with the apps script URL and the Oauth client id secret and your HA sensors names.
   3. Restart Home Assistant.
   4. In Home Assistant via Developer Tools, Service, call the service update_spool, see if it works in the sheet.
   5. Create an Home Assistant's automations.yaml so it's called every time the values are changed. You can do it via Home Assistant's automation UI.

You now have a Google Sheets that is continuously up to date as you use the spools! 🛞

apps_script.js

// Copyright Marc-Antoine Ruel 2024.

const ColBrand = 1; // A
const ColFilamentType = 2; // B
const ColColor = 3; // C
const ColGrams = 4; // D
const ColLastUpdate = 8; // H
const ColColorHex = 9; // I
const ColRFID = 10; // J

// Handles HTTP post from Home Assistant.
//
// See https://developers.google.com/apps-script/guides/logging#use_the_execution_log
function doPost(e) {
    let results = [];
    try {
      const spools = JSON.parse(e.postData.contents).spools;
      if (!spools) {
        return ContentService.createTextOutput(JSON.stringify(["Bad json parameter?", e.postData.contents]));
      }
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stocks");
      for (let i = 0; i < spools.length; i++) {
        const spool = spools[i];
        if (spool.empty) {
          results.push("Slot #" + (i+1) + ": empty");
          continue;
        }
        let tag_uid = spool.tag_uid;
        if (!tag_uid || tag_uid == "0000000000000000") {
          results.push("Slot #" + (i+1) + ": no RFID");
          continue;
        }
        const s = spool.name.indexOf(" ");
        const brand = spool.name.substring(0, s);
        const type = spool.name.slice(s+1);
        const color = spool.color.substring(0, 7);

        // See AMSTray in
        // https://github.com/greghesp/ha-bambulab/blob/main/custom_components/bambu_lab/pybambu/models.py
        let match = sheet.createTextFinder(tag_uid).matchEntireCell(true).findNext();
        if (!match) {
          // Often the RFID LSB bit is toggled. :(
          // This causes 0<->1, 2<->3, ..., C<->D, E<->F to be reported interchangeably
          // on the first character. Ignore it.
          match = sheet.createTextFinder("^." + tag_uid.substring(1) + "$").useRegularExpression(true).findNext();
          if (match) {
            tag_uid = match.getValue();
            results.push("Slot #" + (i+1) + ": Tag UID " + spool.tag_uid + " found as " + tag_uid);
          }
        }
        if (!match) {
          results.push("Slot #" + (i+1) + ": New roll! " + tag_uid);
          console.log("New roll! %s", tag_uid);
          // Sadly appendRow() doesn't return the row number (!!)
          var newRow = [...Array(ColRFID-1)].map(() => "");
          newRow.push(tag_uid);
          newRow[ColColor] = "<add color>";
          sheet.appendRow(newRow);
          match = sheet.createTextFinder(tag_uid).matchEntireCell(true).findNext();
        }
        const row = match.getRow();
        results.push("Found roll at row " + row);
        console.log("Found roll at row %d", row);
        sheet.getRange(row, ColBrand).setValue(brand);
        sheet.getRange(row, ColFilamentType).setValue(type);
        sheet.getRange(row, ColGrams).setValue(spool.remain * 10);
        sheet.getRange(row, ColColorHex).setValue(color);
        const today = new Date();
        sheet.getRange(row, ColLastUpdate).setValue(today.getFullYear() + "-" + String(today.getMonth() + 1).padStart(2, "0") + "-" + String(today.getDate()).padStart(2, "0"));
        setRowSpoolColor(sheet, row, color);
      }
      return ContentService.createTextOutput(JSON.stringify(results));
  } catch (err) {
    console.log("Failed with error %s", err.message);
    return ContentService.createTextOutput(JSON.stringify(results + [err.message]));
  }
}

// Handle text editing.
//
// Update cell color based on hex value.
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet != "Stocks" && sheet != "Want" && range.getColumn() != ColColorHex) {
    return;
  }
  setRowSpoolColor(sheet, range.getRow(), range.getValue());
}

// Handle sheet opening.
//
// Add a menu item to reset colors.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Spools')
      .addItem('Reset colors', 'menuResetColor')
      .addToUi();
}

function setRowSpoolColor(sheet, row, color) {
  const r = Number("0x"+color.substring(1, 3));
  const g = Number("0x"+color.substring(3, 5));
  const b = Number("0x"+color.substring(5, 7));
  const y = Math.pow(r/255.0, 2.2) * 0.2126 + Math.pow(g/255.0, 2.2) * 0.7152 + Math.pow(b/255.0, 2.2) * 0.0722;
  sheet.getRange(row, ColColor).setBackground(color).setFontColor((y < 0.36) ? "#ffffff" : "#000000");
}

// Reset all the filmanet cell background colors.
function menuResetColor() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const name = sheet.getName();
  if (name != "Stocks" && name != "Want") {
    SpreadsheetApp.getUi().alert("Can't work on that sheet");
    return;
  }
  const start = 2;
  const end = sheet.getLastRow()+1;
  const all = sheet.getRange(start, ColColorHex, end-1, 1).getValues();
  for (let i = start; i < end; i++) {
    //Logger.log("Row:" + i + "; Value:" + all[i-start][0]);
    //setRowSpoolColor(sheet, i, sheet.getRange(i, ColColorHex).getValue());
    setRowSpoolColor(sheet, i, all[i-start][0]);
  }
}

automations.yaml

# Update with your Home Assistant sensors names.

alias: "Workshop: Spools in AMS => GSheets"
description: ""
trigger:
  - platform: state
    entity_id:
      - sensor.x1c_00m00a_ams_1_tray_1
      - sensor.x1c_00m00a_ams_1_tray_2
      - sensor.x1c_00m00a_ams_1_tray_3
      - sensor.x1c_00m00a_ams_1_tray_4
    attribute: remain
condition:
  - condition: template
    value_template: |-
      {{ not trigger.to_state.attributes.empty and
         trigger.to_state.attributes.remain|int(-1) >= 0 and
         trigger.from_state.attributes.remain != trigger.to_state.attributes.remain }}
action:
  - service: rest_command.update_spool
    data: {}
mode: single

configuration.yaml

# Use the apps script URL and the Oauth client id secret and your Home Assistant sensors names.

# https://www.home-assistant.io/integrations/rest_command
rest_command:
  update_spool:
    url: "https://script.google.com/macros/s/<appscriptid>/exec"
    method: POST
    headers:
      authorization: "Bearer <GCP OAuth Client ID Desktop Secret code>"
      accept: "application/json, text/html"
    content_type: "application/json; charset=utf-8"
    payload: |-
      {"spools":[
        {{states.sensor.x1c_00m00a_ams_1_tray_1.attributes|to_json}},
        {{states.sensor.x1c_00m00a_ams_1_tray_2.attributes|to_json}},
        {{states.sensor.x1c_00m00a_ams_1_tray_3.attributes|to_json}},
        {{states.sensor.x1c_00m00a_ams_1_tray_4.attributes|to_json}}
      ]}
    timeout: 60

Unfortunately still no good :(

The automation "Spools in AMS => GSheets" (automation.spools_in_ams_gsheets) is not active because the configuration has errors.

Error:Unexpected value for condition: 'None'. Expected and, device, not, numeric_state, or, state, sun, template, time, trigger, zone @ data['condition'][1]. Got {'service': 'rest_command.update_spool', 'data': {}} required key not provided @ data['action']. Got None.

Also this error in log:

Logger: homeassistant.components.websocket_api.http.connection
Source: components/websocket_api/connection.py:307
integration: Home Assistant WebSocket API (documentation, issues)
First occurred: 23:12:49 (1 occurrences)
Last logged: 23:12:49

[547928044976] Error handling message: TypeError: Type is not JSON serializable: LoggingUndefined (home_assistant_error) Edwin from 2a02:a46b:5da:1:7506:c7d1:aff7:6825 (Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Safari/537.36)

The error you pasted complains about the condition section. Which means it's about the automation. Double check it.

Im getting an access denied error when I try to run the service. I presume its because my OAuth is in testing, but how do I do this without publishing the app?

I realize this requires a proper blog post. I'll try to do in a few months.
Yes you need to publish the app.

I realize this requires a proper blog post. I'll try to do in a few months. Yes you need to publish the app.

That would be great.