Use Libreoffice calc to make graph from some json data #49

New Issue

juminet · 2023-10-17T15:36:59Z

juminet commented

2023-10-17 15:36:59 +00:00

The general goal of the issue #44 is to better exploit the data that is collected by users on biommap. These data are some observations (aka "sites") of natural elements: trees, hedges, ponds, etc.

The biommap app is based on a backend with Python-Flask + frontend with angular-typescript. So the backend serves as an API and provides the data as json file to the frontend. We benefit from this to use the API to do stuff. For instance, we can open these json data in QGIS. This is explained here: https://gitea.champs-libres.be/champs-libres/biommap/wiki/Utilisation-API

An idea would be to use Libreoffice Calc to somehow fetch the json data from the API and make some graphs. For example, from https://biomap.champs-libres.be/api/sites/programs/1, we could make a pie chart of the trees species distribution. A perfect solution would be to store the json address in a Libreoffice calc file and to have always the latest data when opening the file.

eg:

I suggest to make some search on the web, use chatgpt. Maybe there is a simple solution, maybe there is no good solutions that would take less than 2 days of work, in the latter case, I would not spend more than 2-3 days on it.

The general goal of the issue #44 is to better exploit the data that is collected by users on biommap. These data are some observations (aka "sites") of natural elements: trees, hedges, ponds, etc. The biommap app is based on a backend with Python-Flask + frontend with angular-typescript. So the backend serves as an API and provides the data as json file to the frontend. We benefit from this to use the API to do stuff. For instance, we can open these json data in QGIS. This is explained here: <https://gitea.champs-libres.be/champs-libres/biommap/wiki/Utilisation-API> An idea would be to use Libreoffice Calc to somehow fetch the json data from the API and make some graphs. For example, from https://biomap.champs-libres.be/api/sites/programs/1, we could make a pie chart of the trees species distribution. A perfect solution would be to store the json address in a Libreoffice calc file and to have always the latest data when opening the file. eg: - https://www.reddit.com/r/libreoffice/comments/s7xeem/how_to_read_data_in_json_format_from_web_site/ - https://superuser.com/questions/1049616/can-i-retrieve-and-access-a-simple-json-object-via-http-in-libreoffice-calc I suggest to make some search on the web, use chatgpt. Maybe there is a simple solution, maybe there is no good solutions that would take less than 2 days of work, in the latter case, I would not spend more than 2-3 days on it.

juminet referenced this issue

2023-10-17 16:03:42 +00:00

Add some new API point that produce some CSV #50

juminet referenced this issue

2023-10-19 07:40:55 +00:00

Pouvoir mieux exploiter les données des observations #44

LenaertsJ commented

2023-10-30 14:28:42 +00:00

It's true that chatGPT is kind of awesome sometimes. So he told me to do use the function WEBSERVICE("URL") and this works.
The json data is retrieved.

However I imagine that we want to parse it now to have a bit of oversight, because he puts all the data in one cell.

So to parse the JSON Data you can use LibreOffice Calc's JSON functions to extract the data you need with
=FILTERJSON(A1, "name")

This formula will create a list of names from the JSON data. You can adjust the "name" key to match the key in your JSON data.

If you click on the cell containing all the data and press 'enter' it will refetch the latest data.

Updating data automatically can also be done with a 'macro':

If you want to automatically update the data at regular intervals, you can create a macro to refresh the data.

"Tools" > "Macros" > "Organize Macros" > "basic"
Select your calc document from the left pane.
Click the "New" button to create a new module and give it a name "updateDate"

Sub UpdateData
    Dim oSheet As Object
    Dim oCell As Object
    
    oSheet = ThisComponent.Sheets.getByName("Sheet1") ' Replace with your sheet name
    oCell = oSheet.getCellByPosition(0, 0) ' Replace with the cell where you have the WEBSERVICE formula
    
    oCell.Formula = "=WEBSERVICE(""https://biomap.champs-libres.be/api/programs/1"")"
End Sub

Replace "Sheet1" with the name of your sheet.
Replace the coordinates (0, 0) with the cell coordinates where you have the WEBSERVICE formula.
Save the Macro. It will be saved in the module created earlier.
To run the Macro: Tools" > "Macros" > "Run Macro" and navigate to macro you created and click "Run."

It's true that chatGPT is kind of awesome sometimes. So he told me to do use the function WEBSERVICE("URL") and this works. The json data is retrieved. However I imagine that we want to parse it now to have a bit of oversight, because he puts all the data in one cell. So to parse the JSON Data you can use LibreOffice Calc's JSON functions to extract the data you need with =FILTERJSON(A1, "name") This formula will create a list of names from the JSON data. You can adjust the "name" key to match the key in your JSON data. If you click on the cell containing all the data and press 'enter' it will refetch the latest data. Updating data automatically can also be done with a 'macro': If you want to automatically update the data at regular intervals, you can create a macro to refresh the data. 1. "Tools" > "Macros" > "Organize Macros" > "basic" 2. Select your calc document from the left pane. Click the "New" button to create a new module and give it a name "updateDate" 3. ``` Sub UpdateData Dim oSheet As Object Dim oCell As Object oSheet = ThisComponent.Sheets.getByName("Sheet1") ' Replace with your sheet name oCell = oSheet.getCellByPosition(0, 0) ' Replace with the cell where you have the WEBSERVICE formula oCell.Formula = "=WEBSERVICE(""https://biomap.champs-libres.be/api/programs/1"")" End Sub ``` 4. Replace "Sheet1" with the name of your sheet. 5. Replace the coordinates (0, 0) with the cell coordinates where you have the WEBSERVICE formula. 6. Save the Macro. It will be saved in the module created earlier. 7. To run the Macro: Tools" > "Macros" > "Run Macro" and navigate to macro you created and click "Run."

LenaertsJ commented

2023-10-30 14:31:16 +00:00

To automatize the updating of data on a regular basis we have to create a cronjob on safran I imagine and a python script that will be executed every so often.

Create a Python Script:

Open a text editor and create a Python script (e.g., run_macro.py) to run LibreOffice Calc with your macro. Here's an example script:

python script should be something like this:

import subprocess

# Path to the LibreOffice Calc executable
libreoffice_path = '/usr/bin/libreoffice6.4'

# Path to your spreadsheet and macro
spreadsheet_path = '/path/to/your/spreadsheet.ods'
module = 'YourModule'
macro = 'YourMacroName'

# Run LibreOffice Calc with the macro
cmd = [libreoffice_path, '--headless', '--invisible', '--nologo', '--run', f'{module}.{macro}', spreadsheet_path]
subprocess.run(cmd)

Modify the libreoffice_path, spreadsheet_path, module, and macro variables to match your specific setup.

Make the Python Script Executable:

Open your terminal and navigate to the directory where the Python script is located. Make the script executable by running:

bash

chmod +x run_macro.py

Edit Your User's Cron Table:

Open your user's crontab by running:

bash

crontab -e

Schedule the Python Script:

Add a line to your crontab to schedule the Python script. For example, to run it every day at 2:00 AM, add the following line:

bash

0 2 * * * /path/to/your/run_macro.py

    Replace /path/to/your/run_macro.py with the full path to your Python script.

Save and Exit the Crontab Editor:

Save the crontab file, and the Cron scheduler will execute your Python script, which in turn will run your LibreOffice Calc macro at the scheduled time.

To automatize the updating of data on a regular basis we have to create a cronjob on safran I imagine and a python script that will be executed every so often. Create a Python Script: Open a text editor and create a Python script (e.g., run_macro.py) to run LibreOffice Calc with your macro. Here's an example script: python script should be something like this: ``` import subprocess # Path to the LibreOffice Calc executable libreoffice_path = '/usr/bin/libreoffice6.4' # Path to your spreadsheet and macro spreadsheet_path = '/path/to/your/spreadsheet.ods' module = 'YourModule' macro = 'YourMacroName' # Run LibreOffice Calc with the macro cmd = [libreoffice_path, '--headless', '--invisible', '--nologo', '--run', f'{module}.{macro}', spreadsheet_path] subprocess.run(cmd) ``` Modify the libreoffice_path, spreadsheet_path, module, and macro variables to match your specific setup. Make the Python Script Executable: Open your terminal and navigate to the directory where the Python script is located. Make the script executable by running: bash chmod +x run_macro.py Edit Your User's Cron Table: Open your user's crontab by running: bash crontab -e Schedule the Python Script: Add a line to your crontab to schedule the Python script. For example, to run it every day at 2:00 AM, add the following line: bash 0 2 * * * /path/to/your/run_macro.py Replace /path/to/your/run_macro.py with the full path to your Python script. Save and Exit the Crontab Editor: Save the crontab file, and the Cron scheduler will execute your Python script, which in turn will run your LibreOffice Calc macro at the scheduled time.

juminet commented

2023-11-22 08:55:24 +00:00

NB: webservice don't work with my version of libreoffice (6.4). Version of Julie's libreoffice is 7.3.7

LenaertsJ commented

2023-11-27 14:02:59 +00:00

Ok, so in the end this is quite a bit more complex than just running two functions in Calc.
In fact I have the WEBSERVICE funtion, but not the FILTERJSON function. So it is not possible to simply do everything within Calc directly.

Consequently I found that you could write a macro, but for me the only language my Calc is willing to work with is 'Basic', which I don't really know and I feel like it was better to use python.

The result is that we'd have to write a python script that fetches the data and then parses it.
I made an attempt at that, but it's not super easy. Then we could write a small script in Basic that will run the Python script if we want so it could be a macro within the excel...

Like I said, it's all a bit more complicated and I don't have much knowledge of Basic to write directly in that language. I could try. Probably chatgpt could help...

Ok, so in the end this is quite a bit more complex than just running two functions in Calc. In fact I have the WEBSERVICE funtion, but not the FILTERJSON function. So it is not possible to simply do everything within Calc directly. Consequently I found that you could write a macro, but for me the only language my Calc is willing to work with is 'Basic', which I don't really know and I feel like it was better to use python. The result is that we'd have to write a python script that fetches the data and then parses it. I made an attempt at that, but it's not super easy. Then we could write a small script in Basic that will run the Python script if we want so it could be a macro within the excel... Like I said, it's all a bit more complicated and I don't have much knowledge of Basic to write directly in that language. I could try. Probably chatgpt could help...

LenaertsJ commented

2023-11-27 17:19:21 +00:00

Alright, I cannot get it right. Sorry...

Tried with python, tried with Basic. I can give you what I have so far in terms of Macro and python script, but Calc keeps on giving errors. The python script is external to the Calc spreadsheet, but BASIC should be able to execute it. Anyways, I keep getting errors.

Another idea was to use the WEBSERVICE() function to get the data, which is then put in one cell and then in BASIC to parse the data of that one cell. However my Calc keeps crashing now when I insert the api url https://biomap.champs-libres.be/api/sites/programs/1

I tried something else to get the data for each site separately, but it's difficult because the site_id's are not at all consecutive. It jumps from 1 to 100000 or something and then there's other ones that are like 275 or something... So I would have to get all the site_id's and then dynamically form each api url for each site.... However even getting the site_id's from this url https://biomap.champs-libres.be/api/sites/programs/1 makes my Calc crash.

Bref, it's been a bit of a mess and unfortunately I don't have anything working.

I still feel like getting BASIC to work with the python script would be the best option, but I've run out of ideas on how to do this....
Here are the two scripts though if you want to try

Python script:

import requests
import json

def fetch_data():
    api_url = "https://biomap.champs-libres.be/api/sites/programs/1"
    response = requests.get(api_url)

    if response.status_code == 200:
        return response.json()
    else:
        print(f"Failed to fetch data. Status code: {response.status_code}")
        return None

if __name__ == "__main__":
    data = fetch_data()
    if data:
        print(json.dumps(data, indent=2))

BASIC script used in the macro

Sub FetchAndParseDataFromAPI
    Dim pythonScript As String
    Dim cmd As String
    Dim result As String
    Dim json As Object
    Dim features As Object
    Dim feature As Object
    Dim properties As Object
    Dim mergedVisits As Object
    Dim rowIndex As Integer
    

    ' Set the path to your Python script
    pythonScript = "/home/julielenaerts/Documents/CHAMPSLIBRES/fetch_data_only.py"

    ' Build the command to run the Python script
    cmd = "python3 """ & pythonScript & """"
    
    ' Display the command for debugging
    MsgBox "Command to run: " & cmd

    ' Run the Python script
    result = Shell(cmd, 0)
    
    ' Display the result for debugging
	MsgBox "Shell command result: " & result
    
    ' Check if the result is empty
    If Len(result) = 0 Then
        MsgBox "Error: No data received from API"
        Exit Sub
    End If
    
    ' Display the content of the result for debugging
    MsgBox "Received data: " & result

    ' Check if the result contains valid JSON data
    Dim jsonStart As Integer
    jsonStart = InStr(result, "{")
    If jsonStart = 0 Then
        MsgBox "Error: Invalid JSON data"
        Exit Sub
    End If

    ' Extract the JSON data from the result
    Dim jsonData As String
    jsonData = Mid(result, jsonStart)

    ' Parse the JSON data
    json = JSON.Parse(jsonData)

    ' Check if JSON parsing is successful
    If IsNull(json) Then
        MsgBox "Error parsing JSON data"
        Exit Sub
    End If

    ' Get the 'features' array
    features = json.get("features")

    ' Check if 'features' is an array
    If IsNull(features) Then
        MsgBox "No 'features' array found in JSON data"
        Exit Sub
    End If

    ' Set the starting row index in Calc
    rowIndex = 1

    ' Loop through each feature
    For Each feature In features
        ' Get the 'properties' object
        properties = feature.get("properties")

        ' Check if 'properties' is an object
        If IsNull(properties) Then
            MsgBox "No 'properties' object found in feature"
            Exit Sub
        End If

        ' Get the 'name' property
        Dim name As Variant
        name = properties.get("name")

        ' Check if 'name' is null
        If IsNull(name) Then
            MsgBox "No 'name' property found in 'properties' object"
            Exit Sub
        End If

        ' Get the 'merged_visits' object
        mergedVisits = properties.get("merged_visits")

        ' Check if 'merged_visits' is an object
        If IsNull(mergedVisits) Then
            MsgBox "No 'merged_visits' object found in 'properties' object"
            Exit Sub
        End If

        ' Get the values from 'merged_visits'
        Dim hauteur As Variant
        hauteur = mergedVisits.get("hauteur")
        
        Dim environnement As Variant
        environnement = mergedVisits.get("environnement")
        
        Dim circonference As Variant
        circonference = mergedVisits.get("circonference")
        
        Dim espece As Variant
        espece = mergedVisits.get("espece")

        ' Check if variables are null
        If IsNull(hauteur) Then
            MsgBox "No 'hauteur' property found in 'merged_visits' object"
            Exit Sub
        End If

        ' Insert data into Calc
        ThisComponent.Sheets(0).getCellByPosition(0, rowIndex).String = name
        ThisComponent.Sheets(0).getCellByPosition(1, rowIndex).String = espece
        ThisComponent.Sheets(0).getCellByPosition(2, rowIndex).String = environnement
        ThisComponent.Sheets(0).getCellByPosition(3, rowIndex).Value = hauteur
        ThisComponent.Sheets(0).getCellByPosition(4, rowIndex).Value = circonference

        ' Increment the row index
        rowIndex = rowIndex + 1
    Next feature
End Sub

Alright, I cannot get it right. Sorry... Tried with python, tried with Basic. I can give you what I have so far in terms of Macro and python script, but Calc keeps on giving errors. The python script is external to the Calc spreadsheet, but BASIC should be able to execute it. Anyways, I keep getting errors. Another idea was to use the WEBSERVICE() function to get the data, which is then put in one cell and then in BASIC to parse the data of that one cell. However my Calc keeps crashing now when I insert the api url `https://biomap.champs-libres.be/api/sites/programs/1` I tried something else to get the data for each site separately, but it's difficult because the site_id's are not at all consecutive. It jumps from 1 to 100000 or something and then there's other ones that are like 275 or something... So I would have to get all the site_id's and then dynamically form each api url for each site.... However even getting the site_id's from this url https://biomap.champs-libres.be/api/sites/programs/1 makes my Calc crash. Bref, it's been a bit of a mess and unfortunately I don't have anything working. I still feel like getting BASIC to work with the python script would be the best option, but I've run out of ideas on how to do this.... Here are the two scripts though if you want to try Python script: ``` import requests import json def fetch_data(): api_url = "https://biomap.champs-libres.be/api/sites/programs/1" response = requests.get(api_url) if response.status_code == 200: return response.json() else: print(f"Failed to fetch data. Status code: {response.status_code}") return None if __name__ == "__main__": data = fetch_data() if data: print(json.dumps(data, indent=2)) ``` BASIC script used in the macro ``` Sub FetchAndParseDataFromAPI Dim pythonScript As String Dim cmd As String Dim result As String Dim json As Object Dim features As Object Dim feature As Object Dim properties As Object Dim mergedVisits As Object Dim rowIndex As Integer ' Set the path to your Python script pythonScript = "/home/julielenaerts/Documents/CHAMPSLIBRES/fetch_data_only.py" ' Build the command to run the Python script cmd = "python3 """ & pythonScript & """" ' Display the command for debugging MsgBox "Command to run: " & cmd ' Run the Python script result = Shell(cmd, 0) ' Display the result for debugging MsgBox "Shell command result: " & result ' Check if the result is empty If Len(result) = 0 Then MsgBox "Error: No data received from API" Exit Sub End If ' Display the content of the result for debugging MsgBox "Received data: " & result ' Check if the result contains valid JSON data Dim jsonStart As Integer jsonStart = InStr(result, "{") If jsonStart = 0 Then MsgBox "Error: Invalid JSON data" Exit Sub End If ' Extract the JSON data from the result Dim jsonData As String jsonData = Mid(result, jsonStart) ' Parse the JSON data json = JSON.Parse(jsonData) ' Check if JSON parsing is successful If IsNull(json) Then MsgBox "Error parsing JSON data" Exit Sub End If ' Get the 'features' array features = json.get("features") ' Check if 'features' is an array If IsNull(features) Then MsgBox "No 'features' array found in JSON data" Exit Sub End If ' Set the starting row index in Calc rowIndex = 1 ' Loop through each feature For Each feature In features ' Get the 'properties' object properties = feature.get("properties") ' Check if 'properties' is an object If IsNull(properties) Then MsgBox "No 'properties' object found in feature" Exit Sub End If ' Get the 'name' property Dim name As Variant name = properties.get("name") ' Check if 'name' is null If IsNull(name) Then MsgBox "No 'name' property found in 'properties' object" Exit Sub End If ' Get the 'merged_visits' object mergedVisits = properties.get("merged_visits") ' Check if 'merged_visits' is an object If IsNull(mergedVisits) Then MsgBox "No 'merged_visits' object found in 'properties' object" Exit Sub End If ' Get the values from 'merged_visits' Dim hauteur As Variant hauteur = mergedVisits.get("hauteur") Dim environnement As Variant environnement = mergedVisits.get("environnement") Dim circonference As Variant circonference = mergedVisits.get("circonference") Dim espece As Variant espece = mergedVisits.get("espece") ' Check if variables are null If IsNull(hauteur) Then MsgBox "No 'hauteur' property found in 'merged_visits' object" Exit Sub End If ' Insert data into Calc ThisComponent.Sheets(0).getCellByPosition(0, rowIndex).String = name ThisComponent.Sheets(0).getCellByPosition(1, rowIndex).String = espece ThisComponent.Sheets(0).getCellByPosition(2, rowIndex).String = environnement ThisComponent.Sheets(0).getCellByPosition(3, rowIndex).Value = hauteur ThisComponent.Sheets(0).getCellByPosition(4, rowIndex).Value = circonference ' Increment the row index rowIndex = rowIndex + 1 Next feature End Sub ```

juminet commented

2023-11-27 17:54:36 +00:00

@LenaertsJ

Hey if it is too much complicated and not very used by libreoffice users, it is probably better to not do it. We still have the export as csv/Excel option which should not be too complicated.

I'm afraid having the need of Python would prevent the use of the macro by any user, since the user would need to install Python to use it, right? So it is definetely better to make a custom API point that export data as CSV or Excel.

@LenaertsJ Hey if it is too much complicated and not very used by libreoffice users, it is probably better to not do it. We still have the export as csv/Excel option which should not be too complicated. I'm afraid having the need of Python would prevent the use of the macro by any user, since the user would need to install Python to use it, right? So it is definetely better to make a custom API point that export data as CSV or Excel.

LenaertsJ commented

2023-11-28 13:53:06 +00:00

Hey Julien, ok, I think we can say it'll be too complicated. Allé, I really liked the challenge of figuring it out, but it will require too much time so better not to.

It's also true that users would be required to have Python installed on their computer which wouldn't be a good option either then.

Ok, so custom Api point to export data as a CSV it will have to be. I don't have the application installed. Had some problems with that. I just checked again and I get this,

ERROR: Head "https://registry.gitlab.com/v2/champs-libres/geonature-citizen/taxhub/manifests/latest": denied: access forbidden

Not sure if that was the only problem, but it might be resolved if I could have access to that repository.
Well, let me know, if you'd like me to work on that or not. Always willing to try!

Hey Julien, ok, I think we can say it'll be too complicated. Allé, I really liked the challenge of figuring it out, but it will require too much time so better not to. It's also true that users would be required to have Python installed on their computer which wouldn't be a good option either then. Ok, so custom Api point to export data as a CSV it will have to be. I don't have the application installed. Had some problems with that. I just checked again and I get this, `ERROR: Head "https://registry.gitlab.com/v2/champs-libres/geonature-citizen/taxhub/manifests/latest": denied: access forbidden` Not sure if that was the only problem, but it might be resolved if I could have access to that repository. Well, let me know, if you'd like me to work on that or not. Always willing to try!

juminet closed this issue

2024-01-08 15:29:21 +00:00

Sign in to join this conversation.