Hello there,
We know the struggle with retool tables when we want to provide users a more excel feel with calculated columns updating upon values on other columns being updated, without necessarily having the query updating the backend database. This is arguably doable with having a variable as the table's data set, but it gets tricky when you want to undo, or remove a change, etc.
As such, for one user case I'm working on, I thought to set up the functionality for users to download a formulated excel file. This file would contain data from their data set, but also columns with formulas using that data. This allows users to download, play around with the data, see what fits their needs and upload back (in this particular instance, is a pricing spreadsheet).
I added the xlsx library to my app: https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.mini.min.js
And with the help of chat gpt and a lot of trial and error, I managed to create this js query:
// Ensure the xlsx library is loaded
const XLSX = window.XLSX;// Prepare data
const data = fetchProducts.data.map((x, index) => {
const rowNum = index + 2; // Excel rows are 1-indexed and we have headersreturn {
id: x.id,
product: x.title,
sku: x.sku,
net_cost: x.net_cost,
courier: x.courier_cost,
combined_net:=D${rowNum} + E${rowNum},
net_rrp:=(D${rowNum} + E${rowNum}) * 2,
markup:=(G${rowNum} - F${rowNum}) / G${rowNum}, // Reference net_rrp and combined_net cells
margin:=G${rowNum} - F${rowNum}, // Reference net_rrp and combined_net cells
rrp: x.taxable ?=G${rowNum} * 1.20:=G${rowNum}// Reference net_rrp cell
};
});// Convert data to a worksheet format with explicit types
const worksheetData = [
["id", "Name", "sku", "Net Cost", "courier_cost", "Combined Net", "Net RRP", "Markup", "Margin", "rrp"], // Note ID is the first column
...data.map(item => [
{ v: item.id, t: 's' },
{ v: item.product, t: 's' },
{ v: item.sku, t: 's' },
{ v: item.net_cost, t: 'n' },
{ v: item.courier, t: 'n' },
{ f: item.combined_net, t: 'n' },
{ f: item.net_rrp, t: 'n' },
{ f: item.markup, t: 'n' },
{ f: item.margin, t: 'n' },
{ f: item.rrp, t: 'n' }
])
];// Create a new worksheet from the data
const worksheet = XLSX.utils.aoa_to_sheet(worksheetData);// Hide the ID column (first column, index 0)
if (!worksheet['!cols']) worksheet['!cols'] = ;
worksheet['!cols'][0] = { hidden: true }; // Set the 1st column (ID) as hidden (0-indexed)// Create a new workbook and append the worksheet
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');// Ensure Excel recalculates the formulas when the workbook is opened
workbook.Workbook = {
CalcPr: { fullCalcOnLoad: 1 }
};// Create a binary string for the Excel file
const excelBuffer = XLSX.write(workbook, {
bookType: 'xlsx',
type: 'binary'
});// Function to convert string to array buffer
const s2ab = (s) => {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i < s.length; i++) {
view[i] = s.charCodeAt(i) & 0xFF;
}
return buf;
};// Create a Blob and use it to download the file
const blob = new Blob([s2ab(excelBuffer)], { type: 'application/octet-stream' });
const url = window.URL.createObjectURL(blob);// Construct the file name
const fileName =products-export ${new Date().toISOString().split('T')[0]} ${new Date().toLocaleString('en-GB', { timeZone: 'Europe/London', hour: '2-digit', minute: '2-digit', hour12: false })}.xlsx;const link = document.createElement('a');
link.href = url;
link.setAttribute('download', fileName);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
I was able even to hide the id column to make sure it is not changed or edited so that the bulk update works when user uploads the file back as a csv.
Interesting little functionality, hope this helps someone!
seriously though, been considering that for a future iteration of an application and not having to start from zero is always a major plus. Thanks so much for sharing the knowledge!