Export New Table As Displayed

[tristdrum]

Hi all!

I'm revisiting a topic from this closed thread, which discussed the need for a feature that allows exporting table data as displayed to the user, with all mappers, filters, etc., applied. The closure of the thread might be due to version discrepancies in the table component (@victoria?), but the need for this feature persists.

In a nutshell, we're looking for a straightforward method to download table data exactly as it appears to the user. I assumed the following code would suffice, as Displayed implies what the user sees, not the underlying data:

const data = await newTable.getDisplayedData();
utils.exportData(data, 'filename', 'csv');

However, this doesn't seem to work as expected. I'm unclear about its function, especially in contrast to this existing method:

await newTable.exportData({fileName: 'test', fileType: 'csv' })

The latter method seems to apply mappings to the headers, which is counterintuitive.

For those who can access an old, legacy table, there's a workaround using a function by @Van:

function formatTablesAsSeen(table) {
  var inputData = table.displayedData; // source of data

  var inputDataLength = inputData.length;

  var headerValuesNew = table.columnHeaderNames; // new header values as objects
  var headerKeys = Object.keys(table.columnHeaderNames);

  var headerValues = Object.values(table.columns); // current header values as array

  var newHeaders = headerValues.map((val) =>
    headerKeys.includes(val) ? headerValuesNew[val] : val
  );
  var headerKeysLength = newHeaders.length;

  var outputData = [];

  var mappedColumnKeys =
    table.columnMappers == undefined ? [] : Object.keys(table.columnMappers);

  for (
    var inputDataIndex = 0;
    inputDataIndex < inputDataLength;
    inputDataIndex++
  ) {
    var outputObj = {};
    for (
      var headerKeysIndex = 0;
      headerKeysIndex < headerKeysLength;
      headerKeysIndex++
    ) {
      if (table.columnVisibility[headerValues[headerKeysIndex]]) {
        if (mappedColumnKeys.includes(headerValues[headerKeysIndex])) {
          //outputObj[newHeaders[headerKeysIndex]] = table.columnMappers[headerValues[headerKeysIndex]][inputDataIndex]
          var displayedIndex = table.displayedDataIndices[inputDataIndex];
          outputObj[newHeaders[headerKeysIndex]] =
            table.columnMappers[headerValues[headerKeysIndex]][displayedIndex];
        } else {
          outputObj[newHeaders[headerKeysIndex]] =
            inputData[inputDataIndex][headerValues[headerKeysIndex]];
        }
      }
    }
    outputData.push(outputObj);
  }

  return outputData;
}

@Kabirdas, @victoria, I'm eager to move away from the legacy table, but this feature gap is a significant hurdle. Is there any update on this being addressed in the roadmap? Do you or anyone internally see the value in this feature? Will the new table eventually match the old in this capability?

@alex-w This was solved previously, and I appreciate your backwards compatibility disposition (but we still, effectively, have the same problem... 4. years. later. )

Here's to hoping for innovative table features in 2024!

PS: I understand there might be complexities in implementing this feature without impacting performance. A shoutout to the devs – maybe a Red Bull will give you wings to tackle this challenge! (no disrespect, I'm a dev, and this would scare me too)

I'm available for any clarifications or further discussion.

[tristdrum]

Hey @Kabirdas & @victoria,

Hope you're both doing well! Wanted to touch base to see if there's been any progress on our discussion?

[bobthebear]

heres code that worked for me with the new table. I tested opening it with Excel. it handles arrays properly. it also adheres to the accepted def for CSV RFC 4180
I also seperate the header and body incase you or someone wants to add it back in

function formatTablesAsSeen(table) {
  /////////////////
  //VARIABLES
  /////////////////
  let headerCSV = '';
  let bodyCSV = '';
  
  //CSV CONSTS
  const ENDLINE = '\r\n';
  
  //REGEX CONSTS
  const csv_tokens_rx = /("(?:[^"]|"")*"|[^,"\n\r]*)(,|\r?\n|\r)/y;
  const csv_unescape_quote_rx = /""/g;
  
  
  /////////////////
  //CODE START
  /////////////////
  try{
    //get a blob for csv file
    let csvBlob = buildCSVFile(await table.getDisplayedData());
  
    //try downloading the blob file
    if(!downloadCSVFile(csvBlob))
      console.log("ERROR DOWNLOADING CSV FILE")
  }
  catch(err){
    throw new Error("ERROR BUILDING/DOWNLOADING CSV FILE", err);
  }  
  
  
  /////////////////
  //DEBUG-VIEW CSV PARTS
  /////////////////
  //these clog up the console, uncomment to view the parts you want to see
  //console.log("HEADER CSV", getHeaderCSV);
  //console.log("TABLE CSV", getBodyCSV);
  //console.log("HEADER + TABLE", getHeaderAndBodyCSV);
  
  
  /////////////////
  //FUNCTIONS
  /////////////////
  function getHeaderAndBody() {
    return headerCSV + bodyCSV; 
  }

  function getHeaderCSV() {
    return headerCSV;
  }

  function getBodyCSV() {
    return bodyCSV;
  }

  //we parse arrays seperately so we can format them properly.
  function csv_parse_array(arrayObj){
    if(arrayObj.length === 0)
      return arrayObj;
  
    //csv for excel defined:  [""item"",""item2""]
    arrayObj.forEach(function(item) {
      item = item.contains('"') ? item.replaceAll(/\"/g, '""') : item;
    });
  
    return '\"[\"\"' + arrayObj.join('\"\",\"\"') + '\"\"]\"';
  }
  
  function csv_parse(s) {
      console.log(`${s} is of type ${typeof s}`);
  
      //handle array    
      if(typeof s === 'object' && s.length > 0){
        return csv_parse_array(s);
      }
    
      //handle number and boolean
      if(typeof s !== 'string'){
        return s;
      }
  
      //handle string
      if (s && s.slice(-1) != '\n')
          s += '\n';
      var ok;
      var rows = [];
      var row = [];
      csv_tokens_rx.lastIndex = 0;
      while (true) {
          ok = csv_tokens_rx.lastIndex == s.length;
          var m = s.match(csv_tokens_rx);
          if (!m)
              break;
          var v = m[1], d = m[2];
          if (v[0] == '"') {
              v = v.slice(1, -1);
              v = v.replace(csv_unescape_quote_rx, '"');
          }
          if (d == ',' || v)
              row.push(v);
          if (d != ',') {
              rows.push(row)
              row = [];
          }
      }
      return ok ? rows : null;
  }
  
  //CREATE CSV STRING FROM ARRAY
  function arrayToCSV(itemlist, isHeader) {
    //handle csv body
    if(!isHeader){
      //get array of obj items.  parse each item for proper format then turn array to string .join()
      let itemCSVString = Object.values(itemlist).map((item) => csv_parse(item)).join();
  
      if(typeof itemCSVString === 'string'){
        //ensure no trailing comma
        if(itemCSVString.endsWith(',')){
          itemCSVString = (itemCSVString.slice(0, -1) + ENDLINE);
        }
        //check if string already ends in newline and add one if missing then append to bodyCSV
        bodyCSV += itemCSVString.endsWith('\r') || itemCSVString.endsWith('\n') ? itemCSVString : (itemCSVString + ENDLINE);
      }
  
    }
    //handle header seperately
    else{
      headerCSV = Object.keys(itemlist).map((item) => csv_parse(item)).join().concat(ENDLINE);
    }
  }
  
  //CREATE BLOB FROM CSV STRING
  function csvToBlob(data){
  // Creating a Blob for having a csv file format  
    // and passing the data with type 
    const blob = new Blob([data], { type: 'text/csv' }); 
  
    // Creating an object for downloading url 
    return window.URL.createObjectURL(blob);
  }
  
  //BUILD CSV FILE
  function buildCSVFile(data){
    data.forEach((row, idx) => {
      //console.log("I WIN: ", window.$.csv.fromObjects(row))
      arrayToCSV(row, idx > 0 ? false : true);
    });
  
    //turn CSV String into Blob
    return csvToBlob(bodyCSV);
  }
  
  //DOWNLOAD CSV FILE
  function downloadCSVFile(url){
    try{
      // Creating an anchor(a) tag of HTML 
      const aElement = document.createElement('a') 
    
      // Passing the blob downloading url  
      aElement.setAttribute('href', url) 
    
      // Setting the anchor tag attribute for downloading 
      // and passing the download file name 
      aElement.setAttribute('download', 'tableAsCSV.csv'); 
    
      // Performing a download with click 
      aElement.click()
      return true;
    }
    catch(err){
      return false;
    }
  }
}

[tristdrum]

Hey @bobthebear,

First off, I genuinely appreciate you taking the time to provide a solution and engage in this conversation. It's great to have such supportive community members willing to help out.

I ran into a couple of hurdles trying to implement your code:

1. When I attempt to use formatTablesAsSeen(newTable) in a JS Query, I encounter this error: missing ) after argument list. It seems like there might be a syntax issue somewhere.
2. I also wanted to bring up a potential misunderstanding regarding the issue at hand. It appears that table.getDisplayedData() does not accurately reflect what the user sees on their end. Specifically, it:

• Includes hidden columns,
• Does not format the column headings as they appear visually,
• Does not correctly format Datetime columns—at least not in my case.

These discrepancies mean that the data ready for download isn't quite what we're aiming for, as it doesn't match the user-displayed data in its entirety. This gap is precisely what we're looking to bridge.

I'm hopeful we can find a workaround or maybe refine the approach further. Your input has been invaluable, and any further guidance or suggestions you might have would be greatly appreciated.

Thank you again for your time and effort—it really makes a difference.

_

[bobthebear]

what do you mean by

@tristdrum

Does not format the column headings as they appear visually,

If you can give me an example of what what it should look like or something I can fix that for you. the headings are just strings though so if you're looking for an icon or something just let me know whats missing

I fixed hidden columns. you can now specify if you want them or not.
you can also specify if you want the headers included in the csv file or not.
Datetime columns are now formatted for Excel: yyy-mm-dd hh:mm. Note: you can tell it came in to Excel as a valid date/time as it will right justify... if it comes in as a string it will left justify

formatTableAsSeen(table2, false, false);

async function formatTableAsSeen(tableComponent, includeHeader, includeHidden) {
  /////////////////
  //VARIABLES
  /////////////////
  const tableData = await tableComponent.getDisplayedData({
    includeHiddenColumns: includeHidden == null? false : includeHidden
  });
  
  let headerCSV = '';
  let bodyCSV = '';
  
  //CSV CONSTS
  const ENDLINE = '\r\n';
  
  //REGEX CONSTS
  const csv_tokens_rx = /("(?:[^"]|"")*"|[^,"\n\r]*)(,|\r?\n|\r)/y;
  const csv_unescape_quote_rx = /""/g;
  
  
  /////////////////
  //CODE START
  /////////////////
  try{
    //get a blob for csv file
    let tableCSV = tableToCSV(tableData);
  
    //try downloading the blob file
    if(!downloadCSVFile(tableCSV))
      console.log("ERROR DOWNLOADING CSV FILE")
  }
  catch(err){
    throw new Error("ERROR BUILDING/DOWNLOADING CSV FILE", err);
  }  
  
  
  /////////////////
  //DEBUG-VIEW CSV PARTS
  /////////////////
  //console.log("HEADER CSV", headerCSV);
  //console.log("TABLE CSV", bodyCSV);
  
  
  /////////////////
  //FUNCTIONS
  /////////////////
  //we parse arrays seperately so we can format them properly.
  function csv_parse_array(arrayObj){
    if(arrayObj.length === 0)
      return arrayObj;
    
    return '\"[\"\"' + arrayObj.join('\"\",\"\"') + '\"\"]\"';
  }

  function csv_parse_number(num){
    return num;
  }

  function csv_parse_boolean(val){
    return val;
  }

  function csv_parse_datetime(s){
    //console.log("DATE: ", s);
    //replace 'T' with ' ', remove trailing Z, remove milliseconds and seconds for excel format
    //s.replace('T', ' ').slice(0, -1).slice(0, s.lastIndexOf('.'));  
    return s.replace('T', ' ').slice(0, -1).slice(0, s.lastIndexOf(':'));
  }
  
  function csv_parse(s) {
      console.log(`${s} is of type ${typeof s}`);
  
      //handle array    
      if(typeof s === 'object' && s.length > 0){
        return csv_parse_array(s);
      }
      //handle number
      else if(typeof s === 'number'){
        return csv_parse_number(s);
      }
      //handle boolean
      else if(typeof s === 'boolean'){
        return csv_parse_boolean(s);
      }
      //handle datetime
      else if(s.endsWith('Z')){         
        return csv_parse_datetime(s);
      }
      
  
      //handle string    
      if (s && s.slice(-1) != '\n')
          s += '\n';
      var ok;
      var rows = [];
      var row = [];
      csv_tokens_rx.lastIndex = 0;
      while (true) {
          ok = csv_tokens_rx.lastIndex == s.length;
          var m = s.match(csv_tokens_rx);
          if (!m)
              break;
          var v = m[1], d = m[2];
          if (v[0] == '"') {
              v = v.slice(1, -1);
              v = v.replace(csv_unescape_quote_rx, '"');
          }
          if (d == ',' || v)
              row.push(v);
          if (d != ',') {
              rows.push(row)
              row = [];
          }
      }
      return ok ? rows : null;
  }

  //BUILD CSV FILE
  function tableToCSV(data){
    data.forEach((row, idx) => {
      tableRowToCSV(row, idx > 0 ? false : true);
    });
  
    //turn CSV String into Blob
    //console.log("CSV: ", includeHeader ? headerCSV + bodyCSV : bodyCSV);
    return csvToBlob(includeHeader ? headerCSV + bodyCSV : bodyCSV);
  }
  
  //CREATE CSV STRING FROM ARRAY
  function tableRowToCSV(itemlist, isHeader) {
    //handle csv body
    if(!isHeader){
      //get array of obj items.  parse each item for proper format then turn array to string .join()
      let itemCSVString = Object.values(itemlist).map((item) => csv_parse(item)).join();
  
      if(typeof itemCSVString === 'string'){
        //ensure no trailing comma
        if(itemCSVString.endsWith(',')){
          itemCSVString = (itemCSVString.slice(0, -1) + ENDLINE);
        }
        //check if string already ends in newline and add one if missing then append to bodyCSV
        bodyCSV += itemCSVString.endsWith('\r') || itemCSVString.endsWith('\n') ? itemCSVString : (itemCSVString + ENDLINE);
      }
  
    }
    //handle header seperately
    else{
      headerCSV = Object.keys(itemlist).map((item) => csv_parse(item)).join().concat(ENDLINE);
    }
  }   
  

  //CREATE BLOB FROM CSV STRING
  function csvToBlob(data){
  // Creating a Blob for having a csv file format  
    // and passing the data with type 
    const blob = new Blob([data], { type: 'text/csv' }); 
  
    // Creating an object for downloading url 
    return window.URL.createObjectURL(blob);
  }
  
  //DOWNLOAD CSV FILE
  function downloadCSVFile(url){
    try{
      // Creating an anchor(a) tag of HTML 
      const aElement = document.createElement('a') 
    
      // Passing the blob downloading url  
      aElement.setAttribute('href', url) 
    
      // Setting the anchor tag attribute for downloading 
      // and passing the download file name 
      aElement.setAttribute('download', 'tableAsCSVfile.csv'); 

      // Performing a download with click 
      aElement.click()
      return true;
    }
    catch(err){
      return false;
    }
  }
}

[tristdrum]

Unfiltered (scroll down for the more polite version)

Hey @bobthebear ,

I really do appreciate you trying but we are talking past each other.

I need somebody from Retool respond.

The new table does not export the data as it Displayed to the user (full stop).

This should happen when you execute table.getDisplayedData() and formatTablesAsSeen(). Neither of these work properly.

This needs to happen. It worked with the old table, it needs to work with the new. I need to know when this will happen.

It needs to take into account all mappers, all sorts, all filters, hidden columns, column ordering, all formatting based on the data type. If it doesn't take into account something, I would like to know about it.

This is a basic requirement, if you have an API endpoint exposed, it needs to work in the obvious way. This is clearly broken.

I am not going to run this through ChatGPT because I feel like it obfuscated the true meaning with too much fluff.

.
.
.
.
.
.
.
.

Filtered through ChatGPT

Hey @bobthebear,

I truly appreciate your efforts to help, but it seems we're not fully aligned on the issue at hand.

I urgently need a response from the Retool team on this.

The core of the problem is that the new table component does not export data as it is displayed to the user. This is a significant step back from the functionality provided by the old table component, where table.getDisplayedData() and formatTablesAsSeen() accurately reflected the user's view, including all mappers, sorts, filters, hidden columns, column ordering, and data type-specific formatting.

This functionality isn't just a nice-to-have; it's essential for our operations. The ability to export data as displayed to the user should be inherent to the API endpoint's functionality. The current shortcomings represent a clear issue that needs resolution.

I'm not seeking a workaround but a permanent fix that restores or improves upon the old table's capabilities. Could the Retool team please provide insight into when we can expect a solution? Additionally, if certain aspects won't be covered by the intended fix, it would be helpful to know.

I believe in the value of clear and direct communication and hope this message conveys the importance of the issue without unnecessary complexity. I'm open to further discussion to clarify any points and provide additional feedback if needed.

Thank you for your attention to this matter.

[Tess]

Hi @tristdrum,

following up from office hours! As discussed, the export data functionality has an option for including/excluding hidden columns, but there are some issues downloading with the proper mappers and column formatting. We'll post here with any updates we get internally Thanks for flagging!

[Jessica_D]

Can the flag for includeHiddenColumns be included on this screen?

[Tess]

Hi @Jessica_D Good callout! I'll request that

[tristdrum]

Hey @Tess!

Any update on this?

[Tess]

It's still in the works! Unfortunately, I don't have a timeline