How to Run Python Script in Collabora Online deployed by default in Nextcloud AIO setup and fetch CSV data from API and save in excel sheet running inside the Nextcloud

Installation & Configuration
1

Hi Everyone. Feeling Amazed to see active community ! Hope I find my Mentor for below Issues.

I have docker + traefik + nextcloud AIO (nextcloud + nextcloud office i.e. collabora online). I have read all the documents but I am bit lost and not able to configure the collabora , where I want to run my python scripts from excel sheet. I have few doubts and questions as below :-

  1. Python Scripts options in not visible in excel below the macros options, which we enabled through coolwsd file. Is that a normal thing ?
  2. How to run python scripts after placing them in /opt/collaboraoffice/share/Scripts/python folder ? Do we need to install python in docker container as well ?
  3. If we use =WEBSERVICE function is shows erro540, even when web api domain is added in lok_allow in coolwsd file. How to resolve this error ?
  4. How to edit macros in collabora online in integrated in nextcloud environment, since no edit option is available even if allow macros in coolwsd file ?
  5. In case python file runs in collabora online after getting solution to point 2 above, can we use and run any python packages that can be triggered from excel placed in nextcloud ?
  6. Shall we delete the default collabora online docker container created by NExtcloud AIO and create new collabora online container ? Doubt because many file example file are missing in /opt/collaboraoffice/share/Scripts/python.

I am trying to fetch data from api which provide csv file in response and save in excel file in nextcloud + collabora online. Any guide to complete this task will be very help full.

I find it very easy when using onlyoffice in nexctloud , but I decided to go with collabora after research and finding it more trustworthy, reliable and stable… Thanks in Advance to all masters.

2

Hiii @ANKUSHJAIN91 Welcome to Collabora onine forums.

I am going through your questions and trying to get a solution. :slight_smile:

Can you please check this out once…

Thanks,
Darshan

3

Thanks A Lot Dear @darshan for your response and I appreciate your efforts as how you to try to resolve each query as I observed in other post as well. !!

I have read almost all docs many times including Using Python scripting in Collabora Online — SDK https://sdk.collaboraonline.com/ documentation , but I am not finding the right way to install the collaboraofficebasis-python-script-provider & collaboraofficebasis-pyuno… for which I raised the query point 2 !!

Waiting for your expert guidance…

My original problem :- I am trying to fetch data from api which provide csv file in response and import in excel file in nextcloud + collabora online. Any guide to complete this task will be very help full.

Thanks & Regards
Ankush

4

Thanks @darshan
problem 1 to 6 resolved except 5… Python and its scripts are working now in collabora…
Only issue left is that urllib is not working…

import uno
import urllib.request
document = XSCRIPTCONTEXT.getDocument()
url = ‘https://somedomain?Div=Div1&month=April’

def write_something():
month = “APRIL”
div = “Div1”
sheets = document.getSheets()
firstSheet = sheets.getByIndex(0)
firstSheet.getCellRangeByName(“A1”).setString(‘hhhhhh’)
firstSheet.getCellRangeByName(“A4”).setString(‘rrrrr’)
try:
with urllib.request.urlopen(url) as f:
# print(f.read(300))
print(f.read())
firstSheet.getCellRangeByName(“A2”).setString(f.read())
firstSheet.getCellRangeByName(“A3”).setString(‘aaaa’)
firstSheet.getCellRangeByName(“A4”).setString(‘bbbb’)
except urllib.error.URLError as e:
firstSheet.getCellRangeByName(“A4”).setString(e)
return None

I am running the above python script which is working till try code and non is working below try line … and also no error is being generated in container logs. CANNT WE INSTALL OUR OWN PYTHON PACKAGES ?

My original problem :- I am trying to fetch data from api which provide csv file in response and import in excel file in nextcloud + collabora online. Any guide to complete this task will be very help full.

5

There are a few things to check here:

  1. String literals: Replace the curly single quotes (‘, ’) with straight single quotes (').
  2. Indentation: Ensure proper indentation for the code inside the try block and after the except block.
  3. Reading data from urllib response: The f.read() method returns bytes, which you need to decode to a string before setting it in a cell.
  4. Setting error messages: Convert the exception to a string before setting it in a cell.

Here is the corrected version of your script:

import uno
import urllib.request
document = XSCRIPTCONTEXT.getDocument()
url = 'https://somedomain?Div=Div1&month=April'

def write_something():
    month = "APRIL"
    div = "Div1"
    sheets = document.getSheets()
    firstSheet = sheets.getByIndex(0)
    firstSheet.getCellRangeByName("A1").setString('hhhhhh')
    firstSheet.getCellRangeByName("A4").setString('rrrrr')
    try:
        with urllib.request.urlopen(url) as f:
            data = f.read().decode('utf-8')  # Decode bytes to string
            print(data)  # Print the data for debugging
            firstSheet.getCellRangeByName("A2").setString(data)
        firstSheet.getCellRangeByName("A3").setString('aaaa')
        firstSheet.getCellRangeByName("A4").setString('bbbb')
    except urllib.error.URLError as e:
        firstSheet.getCellRangeByName("A4").setString(str(e))
    return None

Ensure the indentation is correct and matches the structure above. If you still do not see any logs or error messages, you might want to add more print statements to trace the execution flow and verify where the script might be failing. Additionally, check the container logs for any clues about potential issues not directly related to this script.

Thanks,
Darshan

6

Kudos @darshan !!! Hats off…
Problem got solved… Found few more error but got it resolved now…
Thanks for your great efforts and for your contributions to this open source technologies…
I am now eager to learn more and dive deeper now and want to contribute as well to this awesome tech.

7

I am glad that you have solved the issue. Happy to help :slight_smile:

Thanks, @ANKUSHJAIN91.

Sure! Here’s the revised version:

“That’s fantastic to hear! You’re always welcome to contribute. Feel free to dive into the Collabora Online repository at https://github.com/CollaboraOnline/online. Our community is here to support you every step of the way. For feedback or additional support, you can also visit https://www.collaboraonline.com/.”

Happy hacking

Thanks,
Darshan