Help Desk › Working with your AI
A dashboard, a menu board or a page of records stays current the same way. What it takes depends on where your numbers live.
Your AI can build a page that updates from a spreadsheet, a file or an online service. You keep editing the source, and the page displays the latest available data.
Tell your AI where the data lives and how often it should update. It can set up the page and a refresh schedule, and explain any access or approval it needs from you.
Build me [a dashboard / a menu board / a page of records] as one self-contained HTML page in my Showspace, from [WHERE THE NUMBERS LIVE — a Google Sheet, an Excel workbook, a CSV, a service you can read]. Keep it current as the source changes, following the lite.computer guide for pages that stay current. Tell me which route you are using and what you need from me, and ask before you install anything on my Mac. Show a small line on the page saying when it last read its numbers.
An AI connected to the app already has that guide. See Connect your AI to the app.
Your spreadsheet stays the place you work. Your AI builds the page once. Then a small script copies the sheet's numbers to a file beside the page, and the page re-reads that file every few seconds and redraws what changed. Change a price in the sheet and the dashboard follows, with nobody touching the page.
Approve the schedule. The script runs once each time it is called, so your AI sets up a small job on your Mac that calls it every minute. It asks first and tells you what it installs. The job runs whenever your Mac is on, with lite.computer open or closed, and it uses none of your AI plan. To stop it, tell your AI to remove the refresh job for that page.
Accounting, sales, bookings, analytics: the numbers live in a service, not a file.
From the source to the page. The spreadsheet, the service or the file stays the one place a number is changed. Sliders and inputs on a page are for trying a figure on screen: what happens to breakeven at a higher price. To change the real number, change it at the source, or tell your AI and it changes it there. You can do that from the page itself: leave a comment on it, and your AI reads it next session. See Comments.
A page built this way says when it last read its numbers: Sheet read · 13:04:21. Each time the source moves on, that time moves with it. If the line says it is showing a saved copy, the connection has stopped: ask your AI to check the script and its schedule. Showspace health checks for broken links, missing files and folders the app could not read. It does not check whether your numbers are current.
The guide tells your AI what the script has to do. This is a tested copy. It takes a Google Sheets link, an Excel workbook or a CSV, and writes the data file the page loads. From Excel it reads a plain table on the first sheet: text, numbers and TRUE/FALSE. For a workbook with dates or several sheets, your AI reads it with a workbook library and keeps the rest of the script.
#!/bin/sh # lite-refresh.sh — copy a spreadsheet's numbers into a data file beside a lite.computer page. # # lite-refresh.sh SOURCE DEST # # DEST ends in .js — for pages shown inside lite.computer (the page loads it as a script). # DEST ends in .csv — a plain CSV, for pages that will only ever run on a website. # # SOURCE is one of: # - a Google Sheets CSV link (the sheet must be shared "anyone with the link can view", # or published to the web): # https://docs.google.com/spreadsheets/d/<SHEET ID>/export?format=csv # - the path to an Excel file (.xlsx). Its first sheet is used. Needs python3. It reads a plain # table: text, numbers and TRUE/FALSE, with formulas as Excel last saved them. Dates arrive as # Excel's day numbers. For a workbook that needs more, read it with a workbook library instead. # - the path to a CSV file a person edits by hand (.csv). # # DEST is the data file beside the page, inside the Showspace. # # Two safety rules: # 1. The new file is written beside the old one and swapped in, so the page never reads half a file. # 2. If anything goes wrong, the last good copy stays where it is and this script exits non-zero. set -u if [ "$#" -ne 2 ]; then echo "usage: lite-refresh.sh SOURCE DEST" >&2 exit 2 fi SOURCE=$1 DEST=$2 DIR=$(dirname "$DEST") TMP= WRAP= fail() { echo "lite-refresh: $1 — kept the last good copy of $(basename "$DEST")" >&2 exit 1 } [ -d "$DIR" ] || fail "the folder $DIR does not exist" # Each run gets its own hidden temp files, so two runs for the same page can never swap each # other's half-written work. They are removed however the script ends. TMP=$(mktemp "$DIR/.$(basename "$DEST").XXXXXX") || fail "could not write in $DIR" cleanup() { rm -f "${TMP:-}" "${WRAP:-}"; } trap cleanup EXIT trap 'cleanup; exit 129' HUP trap 'cleanup; exit 130' INT trap 'cleanup; exit 143' TERM case "$SOURCE" in http://*|https://*) # A sheet that is not shared answers with a sign-in web page, not an error. # So check what came back, not only whether the download worked. TYPE=$(curl -s -L --max-time 30 -o "$TMP" -w "%{http_code} %{content_type}" "$SOURCE") \ || fail "could not reach the sheet" case "$TYPE" in "200 text/csv"*|"200 text/plain"*|"200 application/octet-stream"*) ;; *) fail "the link did not return a CSV (got: $TYPE). Is the sheet shared by link?" ;; esac # Some servers label a web page as plain text. Look at the first bytes as well. if head -c 512 "$TMP" | LC_ALL=C grep -Eqi '^[[:space:]]*(<!doctype[[:space:]]+html|<html([[:space:]>]|$))'; then fail "the link returned a web page, not a CSV. Is the sheet shared by link?" fi ;; *.xlsx) [ -f "$SOURCE" ] || fail "cannot find $SOURCE" command -v python3 >/dev/null 2>&1 || fail "python3 is needed to read Excel files" python3 - "$SOURCE" "$TMP" <<'PY' || fail "could not read the Excel file" import csv, re, sys, zipfile import xml.etree.ElementTree as ET src, out = sys.argv[1], sys.argv[2] M = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}" R = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}" P = "{http://schemas.openxmlformats.org/package/2006/relationships}" with zipfile.ZipFile(src) as z: names = set(z.namelist()) shared = [] if "xl/sharedStrings.xml" in names: for si in ET.fromstring(z.read("xl/sharedStrings.xml")).iter(M + "si"): shared.append("".join(t.text or "" for t in si.iter(M + "t"))) # The first sheet in the workbook's own order, whatever its file is called. wb = ET.fromstring(z.read("xl/workbook.xml")) first = wb.find(M + "sheets").find(M + "sheet") rels = ET.fromstring(z.read("xl/_rels/workbook.xml.rels")) target = next(r.get("Target") for r in rels.iter(P + "Relationship") if r.get("Id") == first.get(R + "id")) path = target.lstrip("/") if target.startswith("/") else "xl/" + target sheet = ET.fromstring(z.read(path)) def col_index(ref): n = 0 for ch in re.match(r"[A-Z]+", ref).group(0): n = n * 26 + ord(ch) - 64 return n - 1 rows = [] for row in sheet.iter(M + "row"): cells = {} for c in row.iter(M + "c"): kind, v = c.get("t"), c.find(M + "v") if kind == "s" and v is not None: text = shared[int(v.text)] elif kind == "inlineStr": text = "".join(t.text or "" for t in c.iter(M + "t")) elif kind == "b": text = "TRUE" if v is not None and v.text == "1" else "FALSE" else: text = v.text if v is not None and v.text is not None else "" # Excel stores 9.8 as 9.800000000000001. Write the number a person typed. if text and kind in (None, "n"): try: text = format(float(text), ".12g") except ValueError: pass cells[col_index(c.get("r"))] = text if cells: rows.append([cells.get(i, "") for i in range(max(cells) + 1)]) rows = [r for r in rows if any(x.strip() for x in r)] if not rows: sys.exit("the first sheet is empty") with open(out, "w", newline="", encoding="utf-8") as f: csv.writer(f).writerows(rows) PY ;; *.csv) # A CSV a person edits by hand (a menu, a price list). Nothing to convert: only to hand over. [ -f "$SOURCE" ] || fail "cannot find $SOURCE" cp "$SOURCE" "$TMP" || fail "could not read $SOURCE" ;; *) fail "SOURCE must be a web link, an .xlsx file or a .csv file" ;; esac [ -s "$TMP" ] || fail "the result was empty" # A page inside lite.computer is walled off and may not READ a file beside it, but it may LOAD a # script from one. So when DEST ends in .js, hand the page its numbers as a one-line script: # liteData(`...the CSV text...`) # Backslashes, backticks and ${ are escaped, so nothing typed into a sheet can run as page code. case "$DEST" in *.js) WRAP=$(mktemp "$DIR/.$(basename "$DEST").wrap.XXXXXX") || fail "could not prepare the script file" { printf 'window.liteData&&window.liteData(`' sed -e 's/\\/\\\\/g' -e 's/`/\\`/g' -e 's/\${/\\${/g' "$TMP" printf '`);\n' } > "$WRAP" || fail "could not prepare the script file" mv -f "$WRAP" "$TMP" || fail "could not prepare the script file" ;; esac # Only swap the file in when something changed, so the page and any sync service stay quiet. if [ -f "$DEST" ] && cmp -s "$TMP" "$DEST"; then exit 0 fi if [ -f "$DEST" ]; then chmod "$(stat -f %Lp "$DEST")" "$TMP" || fail "could not set permissions on $DEST" else chmod 644 "$TMP" || fail "could not set permissions on $DEST" fi mv -f "$TMP" "$DEST" || fail "could not write $DEST" echo "lite-refresh: updated $(basename "$DEST")"
Still stuck? Write to support@lite.computer.