How to export Dashboard/Widgets to CSV automatically?

[prajwal]

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? 

[HamzaJ]

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-...

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

[bluemetrics]

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