Skip to content

Instantly share code, notes, and snippets.

@maruel

maruel/README.md

Last active September 20, 2024 16:36
Show Gist options
  • Save maruel/7e1d21b5209d9b0202a602764ffc6c2f to your computer and use it in GitHub Desktop.
Save maruel/7e1d21b5209d9b0202a602764ffc6c2f to your computer and use it in GitHub Desktop.
Download ZIP
Steps to connect your spools' remaining filament in the AMS to a Google Sheet
Raw
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! 🛞

Raw
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]);
}
}
Raw
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
Raw
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
@EdKo66
Copy link

EdKo66 commented Jul 25, 2024

I would like this, but whenever I go to HA Services and do a 'update_spool' it errors with Selected service is invalid, please select a valid service

I know it must be an user-error, but can't seem to find it.

@maruel
Copy link
Author

maruel commented Jul 25, 2024

@EdKo66 double check your change in configuration.yaml, since this is where it is defined. In particular check the sensor names, they will be different.

@EdKo66
Copy link

EdKo66 commented Jul 25, 2024 via email •
edited
Loading

@maruel
Copy link
Author

maruel commented Jul 25, 2024

You should ``` quotes so the indentation is kept. it's important for yaml. You can try to evaluate the payload in the developer tool UI to confirm it's valid.

@EdKo66
Copy link

EdKo66 commented Aug 2, 2024 •
edited
Loading

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)

@maruel
Copy link
Author

maruel commented Aug 3, 2024

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

@Cedge21
Copy link

Cedge21 commented Sep 19, 2024

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?

@maruel
Copy link
Author

maruel commented Sep 20, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment