https://bugs.documentfoundation.org/show_bug.cgi?id=167142
Bug ID: 167142
Summary: LibreOffice Calc (current version) will not refresh
pivot tables programmatically from a Python script
Product: LibreOffice
Version: 25.2.4.3 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
The python script executes without errors. The data cache is discarded in order
to force Calc to re-read the data source. The Pivot table does not see the new
data and the data row is not added. But, right-click on a cell and select
refresh and the new data appears.
Steps to Reproduce:
1.Run the listed code on a spreadsheet that has the following:
Sheet=Current Prices
Column A Column B Column C Column D
Symbol Price Quantity Total Value
XRP $2.13 22000.00 [Computed]
SOL $140.04 10.00 [Computed]
...
etc
Sheet=Historical Prices
Column A Column B Column C Column D
Date Time Symbol Price
2025-04-21 19:11:53 XRP $2.0950
2025-04-21 19:11:53 SOL $138.81
...
etc
Sheet=Pivot
Source is Sheet=historical Prices
Pivot Properties
Column Fields=Symbol, Data
Row Fields = Date
Data Fields=Average - Price
Options = Ignore empty rows, Total Columns, Total rows
Source and Destination
$'Historical Prices'.$A$1:$D$49910
Selection
$Pivot.$A$1
2. This is the Python macro that runs without error and updates everything
except the Pivot table for an unknown reason:
# FINAL SCRIPT - With robust, cache-busting pivot table refresh
import uno
import json
import urllib.request
from datetime import datetime
import traceback
import time
def refresh_pivots_final(doc, sheet_name, update_source_range=False):
"""
Robustly refreshes all pivot tables on a sheet by disabling the cache
first.
Optionally updates the source range for pivots on the specified sheet.
"""
python_log_file = "z:/BTC/python_macro_log.txt"
try:
sheet = doc.Sheets.getByName(sheet_name)
pivots = sheet.getDataPilotTables()
new_source_address = None
if update_source_range:
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
last_row = cursor.getRangeAddress().EndRow + 1
range_str = f"$A$1:$D${last_row}"
new_source_address =
sheet.getCellRangeByName(range_str).getRangeAddress()
for pivot in pivots:
# --- THE CRITICAL CACHE-BUSTING LOGIC ---
# 1. Tell the pivot table NOT to use its internal cache
pivot.UseCache = False
# 2. Update the source range if requested
if update_source_range and new_source_address:
pivot.setSourceRange(new_source_address)
# 3. Now, refresh the pivot. It is forced to re-read from the
sheet.
pivot.refresh()
# 4. (Good practice) Re-enable the cache for future interactive
use.
pivot.UseCache = True
msg = f"INFO: Successfully refreshed pivot '{pivot.getName()}' on
sheet '{sheet_name}'.\n"
print(msg.strip())
with open(python_log_file, "a") as f: f.write(f"[{datetime.now()}]
{msg}")
except Exception as e:
error_msg = f"ERROR: Failed during pivot refresh on sheet
'{sheet_name}'.\n{e}\n{traceback.format_exc()}\n"
print(error_msg)
with open(python_log_file, "a") as f: f.write(f"[{datetime.now()}]
{error_msg}")
def update_crypto_prices(*args):
python_log_file = "z:/BTC/python_macro_log.txt"
doc = None
try:
ctx = uno.getComponentContext()
smgr = ctx.getServiceManager()
desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop",
ctx)
doc_path = "Z:/BTC/Coinbase Tracking.ods" # Using your original path
doc_url = uno.systemPathToFileUrl(doc_path)
# Patiently wait for the document to load
for i in range(15):
for component in desktop.getComponents():
if hasattr(component, "getURL") and component.getURL() ==
doc_url:
doc = component
break
if doc is not None:
break
time.sleep(1)
if doc is None:
# Log fatal error and exit
return
# --- Main data update logic (remains the same) ---
sheet_current = doc.Sheets.getByName("Current Prices")
sheet_hist = doc.Sheets.getByName("Historical Prices")
symbols = []
row = 1
while True:
sym = sheet_current.getCellByPosition(0,
row).String.strip().upper()
if not sym: break
symbols.append(sym)
row += 1
if not symbols: return
url = ("https://min-api.cryptocompare.com/data/pricemulti";
"?fsyms=" + ",".join(symbols) + "&tsyms=USD")
with urllib.request.urlopen(url, timeout=15) as resp:
data = json.loads(resp.read().decode())
for idx, sym in enumerate(symbols, start=1):
cell = sheet_current.getCellByPosition(1, idx)
price = data.get(sym, {}).get("USD")
cell.Value = float(price) if price is not None else "N/A"
now = datetime.now()
ds, ts = now.strftime("%Y-%m-%d"), now.strftime("%H:%M:%S")
cursor = sheet_hist.createCursor()
cursor.gotoEndOfUsedArea(False)
hist_row = cursor.getRangeAddress().EndRow + 1
for sym in symbols:
price = data.get(sym, {}).get("USD")
sheet_hist.getCellByPosition(0, hist_row).String = ds
sheet_hist.getCellByPosition(1, hist_row).String = ts
sheet_hist.getCellByPosition(2, hist_row).String = sym
c = sheet_hist.getCellByPosition(3, hist_row)
c.Value = float(price) if price is not None else "N/A"
hist_row += 1
# ====================================================================
# FINAL PIVOT TABLE REFRESH LOGIC
# ====================================================================
# 1. Force the entire document to recalculate, committing new data.
doc.calculateAll()
# 2. Call the new robust refresh function for each sheet.
# For this sheet, we need to update the source range AND refresh.
refresh_pivots_final(doc, "Historical Prices",
update_source_range=True)
# For this sheet, we only need to refresh it.
# Note: Correct the sheet name if "Pivot sheer" was a typo
refresh_pivots_final(doc, "Pivot", update_source_range=False)
# ====================================================================
doc.store()
with open(python_log_file, "a") as f:
f.write(f"[{datetime.now()}] SUCCESS: Macro finished.\n")
desktop.terminate()
except Exception as e:
# Handle exceptions...
pass
finally:
if doc is not None and hasattr(doc, "close"):
doc.close(True)
return None
g_exportedScripts = (update_crypto_prices,)
3.
Actual Results:
Observed Behavior:
The new data is written correctly to the source sheet and the file is saved,
but the Pivot Table's displayed data does not change. The .refresh() command
executes without throwing an error but has no effect.
Troubleshooting Steps Attempted (which all failed to solve the issue):
Calling doc.calculateAll() before the refresh to commit data model changes.
Programmatically updating the pivot table's source range (.setSourceRange())
before refreshing.
Explicitly disabling the pivot table's cache (pivot.UseCache = False) before
calling .refresh(). The failure of this step is strong evidence of a bug.
Attempting the refresh both in a headless mode via the command line and
manually from within the visible Calc application. The result is the same.
Expected Results:
With the data cache discarded and a forced re-reading of the source data, the
pivot table should have refreshed with a new row.
Reproducible: Always
User Profile Reset: Yes
Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no
--
You are receiving this mail because:
You are the assignee for the bug.
