LibreOffice Calc 6 - "batch" hyperlink to files in folder?

[LOcalcQ]

Hei!

Question regarding LibreOffice Calc (Version: 6.1.5.2).

I have one folder with the spreadsheet and a bunch of image and pdf files.

In the sheet every line starts with a hyperlink, which I usually put in using Ctrl+K, then target the file, and then rename it to not show the full link to file but just the file name.

Since I have 36 file to put in, I’ve been wondering if there is a faster way to do this, instead of manually putting them in one by one?

(I’m on Fedora 29 if that makes any difference).

Thanks!

[karolus]

Called from a Calcfile stored in target_folder the following code puts HYPERLINK(…)-formulas into Column A of active Sheet.

from os import path
from pathlib import Path
import uno



def link_my_folder(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentSelection.Spreadsheet

    p = Path(path.dirname(uno.fileUrlToSystemPath(doc.URL)))
    filenames = [(x.name, x.as_uri()) for x in p.iterdir()
                if x.is_file() and not x.as_uri()==doc.URL]
    filenames.sort()
    formulas = tuple(('=HYPERLINK("{}";"{}"'.format(b, a),) for a, b in filenames)

    targetrange = sheet.getCellRangeByPosition(0,0,0,len(formulas)-1)
    targetrange.setFormulaArray(formulas)

[LOcalcQ]

Thank you for the answer! I don’t know how to use this script. Where should I put it? How can I call it?

[karolus]

open your favourite Texteditor (no it isnt writer in this case) c&p the souce exactly into a new file and store it into path:

/home/$( you )/.config/libreoffice/4/user/Scripts/python/$(some_sensefull_name).py

run link_my_folder via >Tools>Makros>execute Makros>…

if the Script and the Funktion is not visible in the GUI you have probably to install libreoffice-scriptprovider-python with your Packet-Manager or apt-get or yum or whatever is used in Fedora.

[karolus]

Ps.:
look also in this thread about APSO a very usefull Extionsion for managing python-Scripts in Libreoffice

[LOcalcQ]

Thank you, this answered my question! Worked like a charm.

[karolus]

If so please tick the ✓-mark left near my answer.