Forum Discussion

prajwal's avatar
prajwal
Data Storage
09-08-2022

How to export Dashboard/Widgets to CSV automatically?

Hi,

I have a requirement to send a dashboard/ widget in CSV format to user on a daily basis. Sisense subscription only allows PDF & Embedded in email format. And API's also don't support CSV format.

Is there any way to achieve this other than Report Manager plugin? 

 

2 Replies

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration

    If you have access to the server, you could schedule a python script to run at set times. 

    More on how you can use python to export widgets/dashboards to CSV:

    https://community.sisense.com/t5/build-analytics/exporting-csv-using-rest-api/m-p/1698

    Otherwise you could use tools like Zapier do this for you. 

    Get the widget jaql: https://support.sisense.com/kb/en/article/extracting-retrieving-results-from-widgets-to-json-format-using-jaql-queries-104695

    Add the following to the root:

    "csvSeparator": ";", "format": "csv",

    Send this payload to <sisense_url>/api/v1/datasources/<elasticube>/jaql

    This will result in a downloadable CSV which you can then send via email

  • Hi Prajwal,

    I have a script that I believe fits your demand, see it below:

     

    from datetime import datetime
    from sisense import Sisense
    import pandas as pd
    import os
    import io
    
    
    folder_path = ''  # path where the file will be sent
    date_format = 'MM/dd/yyyy'  #  date format that will be printed in the exported file name
    filename_sufix = datetime.now().strftime("%Y-%m-%dT%H:%M")
    
    server_name = 'server_name'  # example: https://example.com
    access_token = 'access_token'  # can be achieved through your own sisense REST API page
    dashboard_id = 'dashboard_id'  # id of the dashboard you want to export, the id of the dashboard can be found in the URL on the dashboard page
    
    api = Sisense(server_name, access_token)
    dashboard = api.dashboard.get(oid=dashboard_id)
    
    for widget in dashboard.widgets:
        widget_metadata = []
        panels = widget.metadata['panels']
    
        for panel in panels:
            for item in panel['items']:
                if not item.get('disabled', False):
                    if item['jaql']['datatype'] == 'datetime':
                        item['format']['mask']['days'] = date_format
    
                    widget_metadata.append(item)
    
        # Add dashboard filters
        if not widget.metadata['ignore']['all']:
    
            for filter in dashboard.filters:
                if not filter.get('disabled', False) and filter['instanceid'] not in widget.metadata['ignore']['ids']:
                    filter.update({'panel': 'scope'})
                    widget_metadata.append(filter)
    
        # Calling API to download CSV
        response = api.datasource.to_csv(dashboard.datasource, widget_metadata)
        
        # Response is a CSV format as string
        # You can simply save the string in a file or convert it to a pandas.DataFrame for manipulation
        data = io.StringIO(response)
        dataframe = pd.read_csv(data, header=0, index_col=None, dtype=str)

     

    To use this script, you will need to install the sisense python library by running:

     
    $ pip install sisense

    Once you set up your script, you can schedule it using the cron (on Linux) or the Task Manager (on Windows). For a cron job:

    ┌───────────── min (0 - 59)
    
    │ ┌────────────── hour(0 - 23)
    
    │ │ ┌─────────────── Day of month (1 - 31)
    
    │ │ │ ┌──────────────── month (1 - 12)
    
    │ │ │ │ ┌───────────────── day of the week(0 - 6)
    
    │ │ │ │ │                                                                   
    
    0 12 * * * python to_csv.py

    The command example above runs the script every day at 12:00.

    Hope this helps. If you have any questions, please, feel free to contact us.

     

    Best regards,
    BlueMetrics