Return value from Macro

I’ve written a Python script which geocodes a location.

I would like to execute this on a cell (e.g. K2) and write back the result to K1 (latitude) and K2 (longitude).

Sub test

    oSheet=ThisComponent.CurrentController.ActiveSheet
    Cell = oSheet.GetCellRangeByName("K183")
    v = Cell.getString

    x = Shell ("python /work/mapping/calc-lati.py" & v)

    MsgBox x

End Sub

I’m always getting back “0”, so that’s probably the exit code for success. But how can I really get the cli output of my script?

Is there a reason you didn’t write the whole macro in Python-UNO? The only reason I can think of is if calc-lati.py requires libraries that are not included in the python core.

Even if that is the case, I recommend you write the macro in Python, because that will make it easier to obtain a result through interprocess communication. Basic doesn’t do this well.

Here on Pastebin is my script; it only imports requests, json & sys.

But as I am a total noob I would like to ask you for a minimal working example, on which I could build up my script. May you please do this?

Calling shell will not return the outcome of an executable. To call a python script “involved” with LO you must either include it as LO script or as user script or as document script.

My older tries to use requests (for its benefits) where a nightmare since requests is not included in standard LO python.

Anyway your /work/mapping/calc-lati.py can write the answer in a predefined file (say: /work/mapping/calc-lati.outcome), you have to wait for shell to return (using bsync) and then read that file and set the Value or String of any cells you want.

You may have to use the bSync parameter to have Shell wait until the shell command is finished to return. See Other Functions (Apache OpenOffice Runtime Library) - Apache OpenOffice Wiki.

Here is a working example. Use APSO to run this code.

import json
import os
import sys
import urllib.request

import uno

def test():
    oDoc = XSCRIPTCONTEXT.getDocument()
    oSheet = oDoc.getCurrentController().getActiveSheet()
    oCell = oSheet.getCellRangeByName("K183")
    longitude = "123"
    latitude = "456"
    oCell.setString(longitude + ", " + latitude)
    
g_exportedScripts = test,

The requests library is not included with LO python on my Windows system. Perhaps urllib.request does what you need instead.