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

README.md

AMS to GSheet

Prerequisites

• Bambulab printer
• 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, Use, URL, Color, RFID.
   3. Menu Extensions, Apps Script.
   4. Add the Apps Script
   5. 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.
   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 automation trigger so it's called every time the values are changed.

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

Apps Script

Copy as-is.

// Handles HTTP post from Home Assistant.
function doPost(e) {
  const spools = JSON.parse(e.postData.contents).spools;
  if (!spools) {
    return ContentService.createTextOutput(JSON.stringify(["Bad json parameter?", e.postData.contents]));
  }
  const sheet = SpreadsheetApp.getActiveSheet();
  let results = [];
  for (let i = 0; i < spools.length; i++) {
    const spool = spools[i];
    if (spool.empty) {
      continue;
    }
    // See AMSTray in
    // https://github.com/greghesp/ha-bambulab/blob/main/custom_components/bambu_lab/pybambu/models.py
    const match = sheet.createTextFinder(spool.tag_uid).matchEntireCell(true).findNext();
    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);
    if (match) {
      const row = match.getRow();
      results.push("Found roll at row " + row);
      sheet.getRange(row, 1).setValue(brand);
      sheet.getRange(row, 2).setValue(type);
      sheet.getRange(row, 4).setValue(spool.remain * 10);
      sheet.getRange(row, 9).setValue(color);
      setRowSpoolColor(sheet, row, color);
    } else {
      results.push("New roll!");
      // Sadly appendRow() doesn't return the row number (!!)
      sheet.appendRow([brand, type, "<add color>", spool.remain * 10, "", "", "", "", color, spool.tag_uid]);
      setRowSpoolColor(sheet, sheet.createTextFinder(spool.tag_uid).matchEntireCell(true).findNext().getRow(), color);
    }
  }
  return ContentService.createTextOutput(JSON.stringify(results));
}

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

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, 3).setBackground(color).setFontColor((y < 0.36) ? "#ffffff" : "#000000");
}

Home Assistant

Use the apps script URL and the Oauth client id secret and your HA 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_emplacement_1.attributes|to_json}},
        {{states.sensor.x1c_00m00a_ams_1_emplacement_2.attributes|to_json}},
        {{states.sensor.x1c_00m00a_ams_1_emplacement_3.attributes|to_json}},
        {{states.sensor.x1c_00m00a_ams_1_emplacement_4.attributes|to_json}}
      ]}
    timeout: 60

Trigger

Update with your sensor names.

alias: "Workshop: Spools in AMS => GSheets"
description: ""
trigger:
  - platform: state
    entity_id:
      - sensor.x1c_00m00a_ams_1_emplacement_1
      - sensor.x1c_00m00a_ams_1_emplacement_2
      - sensor.x1c_00m00a_ams_1_emplacement_3
      - sensor.x1c_00m00a_ams_1_emplacement_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