Query Table Export

Feedback Feature Requests
1

Add an option to output the query table to some text format: markdown, csv, text.

2

Here’s a kit script that does that. Click a button to copy the content of a table to your clipboard.

It converts the table to either rich text (default), csv, or json format. You can choose the format by including the option as a macro argument.

By default it looks for a table in the parent block. Then it looks in the same block as the button. Specify which block by including it as an argument.

Setup and instructions are in the header comment.

Usage and examples:

Invoke the macro in any block: {{copyTable button-label, arguments[format, target]}}

formats: text (default), csv, json
target: parent (default), block, grandparent

The script tries to figure out the best block to copy from if you don’t specify.
It copies the found table as text if you don’t specify a format.
The order you provide the format & target arguments in doesn’t matter.

example uses:

export as csv from table in parent (default) block
{{copyTable this is a label,csv,parent}}
{{copyTable Copy Table,csv}}

copy as json from current block
{{copyTable copycopycopy,json,block}}

copy table as text (default) from table in current block
{{copyTable COPY,text,block}}
{{copyTable copy,block}}

copy table as text (default) from parent block (default)
{{copyTable LABEL)}}

Code

convertTableToFormat javascript:

/**
 * @description
 * A Logseq kit-based script providing functionality to export a table from a block to the system clipboard in various formats based on user arguments.
 * 
 * @requires Logseq kits library
 * @see {@link https://discuss.logseq.com/t/edit-and-run-javascript-code-inside-logseq-itself/20763|Logseq kits library}
 * 
 * @setup
 * 1. Add to Logseq config.edn under :macros key:
 *    :copyTable "[:button.kit.eval {:data-kit convertTableToFormat :data-arguments \"$2 $3 $4 $5 $6\"} \"$1\"]"
 * 2. Create a Logseq page named "convertTableToFormat"
 * 3. Add this entire script as a JavaScript code block on that page

 * @usage
 * Invoke the macro in any block: {{copyTable <button-label>,<arguments>}}
 * 
 * @param {string} button-label - Text to display on the rendered button
 * @param {string} [arguments] - Space-separated options:
 *   - Output format: "text" (default) | "json" | "csv" | "html"
 *   - Target block: "parent" (default) | "block" | "grandparent"
 * 
 * @example
 * {{copyTable Export as CSV,csv parent}}
 * 
 * @function tableElementToObject - Converts HTML table to JavaScript object
 * @function objectToCsv - Converts object to CSV string
 * @function logseq.kits.convertTableToFormat - Main conversion function
 * 
 * @note The script automatically selects the best target block if not specified,
 *       prioritizing parent > current > grandparent blocks containing tables.
 * 
 * @author DeadBranch
 * @license AGPL-3.0
 * 
 * This script includes code from or adapted from code by other authors. 
 */

// Input processors
/**
 * Converts table elements to a structured javascript object
 *
 * This function includes code adapted from a GitHub Gist.
 *
 * @see {@link https://gist.github.com/johannesjo/6b11ef072a0cb467cc93a885b5a1c19f?permalink_comment_id=5130476#gistcomment-5130476}
 *
 * Original code by divinity76
 * Adapted by DeadBranch
 * Licensed under the same terms as the original Gist
 *
 * @param {HTMLElement} inputTable - The table element to convert
 * @returns {Object[]} - An array of objects representing the rows of the table
 */
function tableElementToObject(inputTable) {
  let tableRows = inputTable.querySelectorAll("tr");
  let columnHeaders = Array.from(tableRows[0].querySelectorAll("th")).map(
    (headerCell) => headerCell.textContent.trim()
  );

  let resultArray = [];
  for (let rowIndex = 1; rowIndex < tableRows.length; rowIndex++) {
    let rowObject = {};
    let tableCells = tableRows[rowIndex].querySelectorAll("td");
    for (let columnIndex = 0; columnIndex < tableCells.length; columnIndex++) {
      rowObject[columnHeaders[columnIndex]] =
        tableCells[columnIndex].textContent.trim();
    }
    resultArray.push(rowObject);
  }
  return resultArray;
}

/**
 * Converts a structured object representing tabular data to csv format.
 *
 * This function includes code adapted from a Stack Overflow answer.
 * @see {@link https://stackoverflow.com/a/62418083}
 *
 * Original code by ctholho
 * Adapted by DeadBranch
 * Licensed under CC BY-SA 4.0
 *
 * @param {Object[]} jsonObject - The array of objects representing the tabular data
 * @returns {string} - The CSV string representation of the tabular data
 */
const objectToCsv = (jsonObject) => {
  if (jsonObject.length === 0) return;
  const header = jsonObject
    .map((x) => Object.keys(x))
    .reduce((acc, cur) => (acc.length > cur.length ? acc : cur), []);

  const replacer = (key, value) => (value === undefined || value === null ? "" : value);

  let csv = jsonObject.map((row) =>
    header.map((fieldName) => JSON.stringify(row[fieldName], replacer)).join(",")
  );
  csv = [header.join(","), ...csv];
  return csv.join("\r\n");
};

/**
 * Main function
 *
 * This function includes code from a Logseq discussion forum post.
 * @see {@link https://discuss.logseq.com/t/how-to-copy-export-results-from-an-advanced-query-in-logseq/28037/4?u=deadbranch|How to Copy/Export Results from an Advanced Query in Logseq}
 *
 * Original code by mentaloid
 * Licensed under the same terms as the original post
 */
const LS = logseq.api;
const Module = logseq.Module;
const Kits = Module.Kits;
const Msg = Module.Msg;
logseq.kits.convertTableToFormat = Kits.addClickEventToButton.bind(
  null,
  async function onConvertTableClicked(e) {
    const buttonElement = e.target.closest("button");
    const thisBlock = e.target.closest("div.ls-block");
    const parentBlock = thisBlock.parentElement?.closest("div.ls-block");
    const grandparentBlock = parentBlock?.parentElement?.closest("div.ls-block");

    /**
     * Argument processors
     *
     * Expect the user to include one of these options as a macro argument, and use
     * the first one found. If none are found, default to the first array item
     */
    const macroArguments = buttonElement.dataset.arguments;
    const arguments = macroArguments.toLowerCase();
    const outputFormat = ["text", "json", "csv"]
      .reverse()
      .find(
        (format, index, arr) => arguments.includes(format) || index === arr.length - 1
      );
    console.log("output format:", outputFormat);

    /**
     * Export target
     *
     * Try to determine the correct block to use if the user doesn't
     * specify a valid one.
     */
    const selectExportTarget = (
      arguments,
      thisBlock,
      parentBlock,
      grandparentBlock
    ) => {
      const candidates = [
        { name: "block", element: thisBlock },
        { name: "parent", element: parentBlock },
        { name: "grandparent", element: grandparentBlock },
      ];

      const userSpecifiedTarget = candidates.find(
        (candidate) =>
          arguments.includes(candidate.name) &&
          candidate.element &&
          candidate.element.querySelector("table")
      );

      if (userSpecifiedTarget) {
        console.log(`User specified target: ${userSpecifiedTarget.name}`);
        return userSpecifiedTarget.element;
      }

      const autoSelectedTarget = candidates.find(
        (candidate) => candidate.element && candidate.element.querySelector("table")
      );

      if (autoSelectedTarget) {
        console.log(`Auto-selected target: ${autoSelectedTarget.name}`);
        return autoSelectedTarget.element;
      }

      console.log("No valid target found");
      return null;
    };
    const targetBlock = selectExportTarget(
      arguments,
      thisBlock,
      parentBlock,
      grandparentBlock
    );

    /**
     * Input processors
     */
    const htmlContent = targetBlock.cloneNode(true);
    const tableElements = htmlContent.querySelectorAll("table");
    let clipboardData;

    // To JSON
    if (outputFormat === "json") {
      const elementsToObjectsArray = (elements) => {
        if (elements.length === 0) return null;
        if (elements.length === 1) return tableElementToObject(elements[0]);
        // return elements.map((table) => tableElementToObject(table));
        let outputArray = [];
        for (const table of tableElements) {
          const tableObject = tableElementToObject(table);
          outputArray.push(tableObject);
        }
        return outputArray;
      };

      const tablesToJson = JSON.stringify(elementsToObjectsArray(tableElements));
      clipboardData = {
        "text/plain": new Blob([tablesToJson], { type: "text/plain" }),
      };
    }

    // To CSV
    if (outputFormat === "csv") {
      const csvOutput = () => {
        if (tableElements.length === 0) return null;

        if (tableElements.length === 1) {
          const tableObject = tableElementToObject(tableElements[0]);
          const csv = objectToCsv(tableObject);
          return csv;
        }

        if (tableElements.length > 1) {
          let outputArray = [];
          for (const table of tableElements) {
            const tableObject = tableElementToObject(table);
            const csv = objectToCsv(tableObject);
            outputArray.push(csv);
          }

          return outputArray.join("\n\n");
        }
      };
      clipboardData = {
        "text/plain": new Blob([csvOutput()], { type: "text/plain" }),
      };
    }

    // To formatted text
    if (outputFormat === "text") {
      clipboardData = {
        "text/html": new Blob([htmlContent], { type: "text/html" }),
        "text/plain": new Blob([targetBlock.innerText], { type: "text/plain" }),
      };
    }

    await navigator.clipboard.write([new ClipboardItem(clipboardData)]);
  }
);

Attribution

This script uses some code from this comment by mentaloid.

3 Likes
3
  • Nice.
  • Some changes I would do:
    • Replace resultArray with a tableRows.map
    • Drop the arguments and the guessing to:
      • make instead a separate explicit macro for each combination
        • e.g. {{htmlFromCurrent Copy HTML}}, {{csvFromGrandpa Copy CSV}} etc.
      • differentiate them within the same kit through html attributes
        • e.g. :data-format csv :data-target parent
1 Like
4

I appreciate the excellent suggestions. Thank you for the helpful feedback.

5

It would be good if this was a standard option. Simply copying the inner HTML of the query results would be sufficient in most cases, and more versatile (e.g. most spreadsheet software knows how to import an HTML table, but it would also work for non-table :view formats).

Other related requrests:

Another work-around is:

  • View → Toggle Developer Tools
  • Click the element selector (arrow at the top left)
  • Click the block (make sure the highlight is at the top level)
  • Right-click the element that’s highlighted in the Elements pane → Copy → Element

This copies the entire element, including all the inner HTML. You can copy that into a text editor and save it as HTML and then edit it with a document editor.