Exporting CSV using REST API

[TomerA]

Hi,
Is there a way to export a table widget data using REST API?
I haven't seen this option on the API documentation.

[edselv · Accepted solution]

Hi, here (https://support.sisense.com/kb/en/article/python-script-to-export-dashboards-to-csv-file) is a python script that leverages Sisense's REST API to export a dashboard as a CSV. The script actually exports each individual widget as a csv file. This should get you in the right direction!

[pbechet]

Hi ! 
Is it possible to have the source code of the python .exe?

Also, I found this (https://documentation.sisense.com/docs/exporting-pivot-tables-to-excel ) in the documentation that talks about the API request POST /engine/excelExport but I didn't find it in the doc. Do you know where I can find information on it ?

Thanks

[TomerA]

Hi,
We don't have Admin privileges as required.
Is it possible to share the Python script source code in order to hard code the Dashboard ID?

[harikm007]

Hi @TomerA , @pbechet 

Please try this alternate python script.

import json
import requests
import urllib.parse

localpath = 'C:\Sisense\CSV_Files\\'
server_name = 'https://xxxxxx.xxxxxxxxxx.com'

username = 'enter sisense login name (email)'
password = 'enter your password here'

dashboardid = 'dashboard id'
widgetid = 'widget id'



login_url = server_name + '/api/v1/authentication/login'
dash_export_base_url = server_name + '/api/v1/dashboards/export?dashboardIds='

login_data = {  'username' : username,
                'password' : password
                }

# Generating token
login_res = requests.post(url=login_url, data=login_data).json()

access_token = login_res.get('access_token')
if len(access_token) > 0:
    print('login successful')
    
api_header = {'Authorization': 'Bearer ' + access_token,
             'content-type': "multipart/form-data; boundary={}"}

# Get widget details
get_widget_url = server_name + '/api/v1/dashboards/' + dashboardid + '/widgets/' + widgetid
get_widget_res = requests.get(url=get_widget_url, headers=api_header).json()

widget_json = {}
widget_metadata = []

widget_json["datasource"] = get_widget_res.get("datasource")
panels = get_widget_res.get("metadata").get("panels")

#Add metadata from widget
for panel in panels:
    items = panel.get("items")
    for item in items:
        if(not item.get("disabled") == True):
            if (panel.get("name") == "filters"):
                widget_metadata.append({"jaql" : item.get("jaql"), "panel":"scope"})
            else:
                widget_metadata.append({"jaql" : item.get("jaql")})

dash_filter_ignore = get_widget_res.get("metadata").get("ignore")
ignore_list = dash_filter_ignore.get("dimensions")

#Add dashboard filters
if(dash_filter_ignore.get("all") == False):
    dash_file = requests.get(url=dash_export_base_url + dashboardid, headers=api_header, allow_redirects=True).json()
    dash_filters = dash_file[0].get("filters")

    for dash_filter in dash_filters:
        if(not dash_filter.get("disabled") == True and not dash_filter.get("jaql").get("dim") in ignore_list):
            widget_metadata.append({"jaql" : dash_filter.get("jaql"), "panel":"scope"})

widget_json["metadata"] = widget_metadata
widget_json["format"] = "csv"
widget_json["widgetType"] = "pivot2"
widget_json["by"] = "export"
widget_json["isMaskedResponse"] = "true"
widget_json["filename"] = "widget.csv"
widget_json["download"] = "true"
widget_json["widget"] = widgetid
widget_json["dashboard"] = dashboardid
widget_json["culture"] = "en-US"

# Calling API to download CSV
datasource = urllib.parse.quote(get_widget_res.get('datasource').get('title'))
download_csv_url = server_name + '/api/datasources/' + datasource + '/jaql/csv?data=' + urllib.parse.quote(json.dumps(widget_json))
donwloaded_csv = requests.post(url=download_csv_url, headers=api_header, allow_redirects=True)

open(localpath + widgetid + '.csv', 'wb').write(donwloaded_csv.content)

print('CSV downloaded')
requests.get(server_name + '/api/v1/authentication/logout')

[pbechet]

Thanks a lot ! I'll try this 🙂 

[Nazeer]

Spoiler

Hi @harikm007 ,

I tried using this, i could able to download the file but i see error stream 500 message with object reference to an object.

Can you help me where I went wrong.

Spoiler

Nazeer

[vrice]

Has anyone gotten this script to work for non-admins? I have tried it and am getting an error message about dash_filters = dash_file[0].get("filters"). 

[Milan]

Does this script still work? I'm getting a <Response [401]> error message

[edselv]

Hey All! Apologies for the delay in response here (holidays..). I suggest reaching out to your dedicated CSM to see they can get approval to share the Python source code with you!

[jlizarraga]

Hi all, one question, Is the script must be set in the "Edit Script" option from the Dashboard? Thanks in advance.