Support for markdown table calculations

I just learned about the Atom markdown-table-calc package (or the corresponding vscode package) and thought, that this feature would definitely be a nice addition for Joplin, too. And now that I investigated into it, I saw that Evernote users want this, too. So I guess it'd also be a good 'selling point' for Joplin. :wink:

The implementation allowed to create a comment on the end of a markdown table and calculate things in the last row.
For instance:

| Column A | Column B | Column C |
|:---------|:---------|:---------|
| 123      | 456      | 789      |
| 0        | 0        | 0        |
| <!--f A1 * B1 -->56088 | <!--f B1 -->456 | <!--f SUM(C1:C2) -->789 |

It used markdown-it and the mighty formula-parser (although nowadays there's the package hyperformula from the same folks, which claims to have "a high-performance").

Usage

To define a set of table formulas, you can add a special HTML comment in a table cell.

| Column A | Column B | Column C |
|:---------|:---------|:---------|
| 123      | 456      | 789      |
| 0        | 0        | 0        |
| <!--f A1 * B1 -->56088 | <!--f B1 -->456 | <!--f SUM(C1:C2) -->789 |

Formulas are calculated in the order they are written. Table cells are described using spreadsheet notation, columns designated with letters like A,B,C .. Z (and even more AA, AB etc.) and rows with integers 1,2,3 ... You can also use range notation like A1:E23. Moreover, hot-formula-parser contains also a big set of predefined functions which can be used in formulas.

You can find a corresponding GitHub issue 4072 and oswida's implementation at GitHub - oswida/joplin-markdown-calc: Joplin plugin for markdown table calculations

3 Likes

Personally I do not like inline formulas in cells.

I think it's much more readable and less work to just write your formulas in a dedicated area (first line after the table). Otherwise the formatting of the table will look messy, unless you don't care how they look in markdown. (I usually format them properly.)

| Column A | Column B | Column C |
|:---------|:---------|:---------|
| 123      | 456      | 789      |
| 0        | 0        | 0        |
<!--f A3=A1+A2; B3=B1; C3=SUM(C1:C2) -->

I don't know, maybe it's even possible to support both.

My plugin proposal implements both notations.

3 Likes

That's awesome. You can also create a topic in #plugins and be our first plugin contributor.

Thanks. I will do it, however I need to resolve one problem and for that I suppose I need some advice.
After the table processing, I'm preparing a new note body with calculated values in all tables. As I've seen from the plugin API, I could replace the note body with this "calculated version", but so far I was not able to get expected result.
I've tried two approaches:

  1. Update note body through Data API
await joplin.data.put(["notes", note.id], null, {
        body: body_lines.join("\n"),
      });

This works properly, but the editor view is not refreshed, so I need to switch to another note, then switch again to a modified one to see the result.

  1. Use Command API:
await joplin.commands.execute("textSelectAll");
await joplin.commands.execute("replaceSelection", body_lines.join("\n"));

In this case , it seems the text is not selected, so new text is just added at the cursor position.

It is possible that I'm making some trivial mistake using this API, so I would appreciate any hints.

Those are good questions. I think it came up at one point in another topic that the API call doesn't refresh the editor view.

The API has nothing to do with the UI, therefore I'm not sure how to make that work. Maybe there is a way.

However it should be possible to use the API for the update and the command API to refresh the current note.
@laurent would know, since he wrote it. :wink:

1 Like

Your second approach would be preferable when you simply want to edit the currently opened note. But I will add a "setText" command to directly set the content of the complete note, and will also check why textSelectAll is not working.

So far, I was able to find the place where "textSelectAll" fails:

After to call to joplin.commands.execute, I get

CodeMirror: unsupported Joplin command:  
{name: "textSelectAll", value: undefined}
name: "textSelectAll"
value: undefined
__proto__: Object

From the next version you can use editor.setText

1 Like

Looks like CodeMirror object is simply missing textSelectAll command. I would propose to add following lines to app-desktop/gui/NoteEditor/NoteBody/CodeMirror/CodeMirror.tsx:

textSelectAll: () => {
	editorRef.current.execCommand('selectAll');
	editorRef.current.focus();
}

I was unsure if I should start the issue processing for such a change.

So next version