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