Export buttons [Excel, CSV] for the Pivot widget

Disclaimer: Please note that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment prior to deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you "as-is" and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.

This script adds Export to Excel and Export to CSV buttons on the bottom of a pivot widget.

How it works:
Script appends 2 buttons below Pivot widget, each button generates a relevant payload to make API calls for export. Please note that Export to Excel V2 should be enabled in Admin => System Management => Configuration => System Configuration => Exporting

How to use:

Paste this script in your Pivot widget, no additional configuration is required.
To change styling, please refer to 'domready' handler part.

Result:

Code:

widget.on('domready', () => {
	if(!$(`#${widget.oid}_excelController`).length){
	let excelButton = document.createElement('button')
	excelButton.onclick = (lmnt) => { exportToExcel(widget) }
	excelButton.id = widget.oid + "_excelController"
	excelButton.innerHTML = 'Export to Excel'
	excelButton.style.margin = "0 0 5px 10px"
	excelButton.style.backgroundColor = "white"
	excelButton.style.border = "1px solid lightgrey"
	$(`widget[widgetid=${widget.oid}]`).parent().append(excelButton)
	}
	
	if(!$(`#${widget.oid}_csvController`).length){
	let csvButton = document.createElement('button')
	csvButton.onclick = (lmnt) => { exportToCSV(widget) }
	csvButton.id = widget.oid + "_csvController"
	csvButton.innerHTML = 'Export to CSV'
	csvButton.style.margin = "0 0 5px 10px"
	csvButton.style.backgroundColor = "white"
	csvButton.style.border = "1px solid lightgrey"
	$(`widget[widgetid=${widget.oid}]`).parent().append(csvButton)
	}
})




const exportToExcel = (widget) => {
	let payload = prepareExcelPayload(widget);
	payload = JSON.stringify(payload);
	const xhttp = new XMLHttpRequest();
	xhttp.withCredentials = true;
	xhttp.onreadystatechange = () => {
		if (xhttp.readyState === 4 && xhttp.status === 200) {
			const a = document.createElement('a');
			a.href = window.URL.createObjectURL(xhttp.response);
			a.download = widget.title ? `${widget.title}.xlsx` : 'Details.xlsx';
			a.style.display = 'none';
			document.body.appendChild(a);
			a.click();
		}
	};
	xhttp.open("POST", `${location.origin}/api/v1/export/jaql/xlsx`);
	xhttp.setRequestHeader("Content-Type", "application/json");
	xhttp.responseType = 'blob';
	xhttp.send(payload);
}

const exportToCSV = (widget) => {
	let payload = prepareCSVPayload(widget);

	const xhttp = new XMLHttpRequest();
	xhttp.withCredentials = true;
	xhttp.onreadystatechange = () => {
		if (xhttp.readyState === 4 && xhttp.status === 200) {
			const a = document.createElement('a');
			a.href = window.URL.createObjectURL(xhttp.response);
			a.download = widget.title ? `${widget.title}.csv` : 'Details.csv';
			a.style.display = 'none';
			document.body.appendChild(a);
			a.click();
		}
	};
	xhttp.open("POST", `${location.origin}/api/datasources/${encodeURIComponent(widget.datasource.title)}/jaql/csv`);
	xhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
	xhttp.responseType = 'blob';
	xhttp.send(payload);
}

const prepareExcelPayload = (widget) => {
	const query = {}
	
	query.jaql = JSON.parse(prism.debugging.GetJaql(widget))
	query.jaql.by = 'export'
	query.jaql.count = -1
	query.jaql.queryGuid = uuidv4()
	query.jaql.widget = widget.oid

	query.dashboardId = widget.dashboard.oid
	query.widgetId = widget.oid
	query.widgetType = "pivot2"
	query.language = "en-US"
	query.widgetStyles = {
		"coloringRules": {
			"alternatingRows": widget.style.colors.rows,
			"alternatingColumns": widget.style.colors.columns,
			"headersHighlighting": widget.style.colors.headers,
			"rowMembersHighlighting": widget.style.colors.members,
			"totalsHighlighting": widget.style.colors.totals
		}
	}

	return query
}

const prepareCSVPayload = (widget) => {
	const query = {}
	
	query.data = JSON.parse(prism.debugging.GetJaql(widget))
	query.data.by = 'export'
	query.data.count = 0
	query.data.download = true
	query.data.filename = widget.title ? `${widget.title}.csv` : 'Details.csv';
	query.data.format = 'csv'
	query.data.offset = 0 
	query.data.widget = widget.oid
	query.data.isMaskedResponse = true
	query.data.culture = "en-US"
	query.data = encodeURIComponent(JSON.stringify(query.data))
	query.localized = true
	
	return new URLSearchParams(query).toString()
}

const uuidv4 = () => {
	return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, (c) => {
		var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);
		return v.toString(16);
	});
}