Represent json string in Text column in structured format

[Hugoman]

My Setup:

• SeaTable Self-hosted
• SeaTable Version (only necessary for self-hosted): 5.2.0
  You can find your SeaTable Server version at https://your-server-url/server-info

Describe the Problem/Error/Question:

Hello,

first of all I would like to congratulate to this great application. Even if I just have started it already saved me a lot of time to demonstrate my business cases. Thanks a lot.

From an event handler I retrieve a json response, which I am storing as string in seatable in a text column.

How could it made possible to show up the json in a nice formatted way (please see screenshots below?

Thanks a lot in advance.

Best regards, Michael

[bennhatton]

Hi @Hugoman, and welcome to the SeaTable forum!
If you use SeaTable self-hosted, you can pretty easily install the python pipeline if it’s not already done. Then, you can use this script:

import json
from seatable_api import Base, context

base = Base(context.api_token,context.server_url)
base.auth()

TABLE_NAME = 'Message List'
RAW_JSON_COL = 'Event Message'
FORMATTED_JSON_COL = 'Formatted Event Message'

curr_row = context.current_row
print(curr_row)

json_data = curr_row.get(RAW_JSON_COL).replace('\\','').replace('\n', ' ').replace('\r', '')
print(json_data)

obj = json.loads(json_data)
print(obj)

json_formatted_str = json.dumps(obj, indent=4)
print(json_formatted_str)
base.update_row(TABLE_NAME, curr_row['_id'], {FORMATTED_JSON_COL : json_formatted_str})

You’ll have to modify the content of the variables in uppercase at the beginning of the script and add a second longtext column (or try to modify your existing Event Message column which should work, but I didn’t test it.
The script can be launched using a button or an automation.

Bests,
Benjamin

---

I love spending time gathering information and explaining solutions to help you solve your problems. I spend… quite a bit of time on it .If you feel grateful for the help I’ve given you, please don’t hesitate to support me. I am also available for paid services (scripts, database architecture, custom development, etc.).

[Hugoman]

Hello @bennhatton ,

thanks a lot for your fast reply. This was guiding me to a solution I can work with. I was hoping, there is feature embedded in SeaTable, that is able to “beautify” json strings in tables, but that might be something more “nice to have” than really a necessity.

As I am more related to java I preferred to use javaScript and the solution is looking somehow nice. Please find below the script to transform content of JSON string column ‘Event Message (origin)’ to beautified, resp. formatted JSON String in column ‘Event Message (formatted)’ on the whole table.

const table = base.getTableByName('Message List');
const view  = base.getActiveView();
const rows = base.getRows(table, view);

for (var i=0; i<rows.length; i++) { 
  const row = rows[i];
  if (typeof row['Event Message (origin)'] != 'undefined') { 
    const jsonString = row['Event Message (origin)'];
    const jsonObject = JSON.parse(jsonString);    
    const formattedJson = JSON.stringify(jsonObject, null, 4);    
    base.updateRow(table, row, {'Event Message (formatted)':formattedJson});    
  }
}