Formatting text output properly from a text source

[noviceplayer]

Hi,
I have a column with a text output that was generated by AI.

Output:
[
“Question 1: Hi, there are several people who are laying out mats in the park. They seem to be enjoying conversations. Some of them are playing. Some of them are talking to each other. They are discussing and there are some people buying items from cars.”,
“Question 2: There is a time when my family took me to a garden park and we picnicked there. I enjoyed the weather and directions.”,
“Question 3: Community events can help strengthen relationships for the diversity. We can discuss the same topics. I think it’s important because it helps in understanding one another and preventing conflicts.”,
“Question 4: I think strengthening the bonds between people in the community is important because it helps in understanding each other and preventing conflicts.”
]

I want to format the output properly that results in the following.

Desired Formatting:

Question 1: Hi, there are several people who are laying out mats in the park. They seem to be enjoying conversations. Some of them are playing. Some of them are talking to each other. They are discussing and there are some people buying items from cars.

Question 2: There is a time when my family took me to a garden park and we picnicked there. I enjoyed the weather and directions.

Question 3: Community events can help strengthen relationships for the diversity. We can discuss the same topics. I think it’s important because it helps in understanding one another and preventing conflicts.

Question 4: I think strengthening the bonds between people in the community is important because it helps in understanding each other and preventing conflicts.

Is there an easy way to do this?

[Darren_Murphy]

What you have there is some JSON.

To extract each item on its own row:

• Number the rows in your table, starting at zero
• Make sure the JSON is applied to all rows in the table. You can use something like a Single Value or Lookup column for this, depending on where you have it.
• Add a JavaScript column using the following:

return JSON.parse(p1)[p2];

• In the above, p1 is your JSON, and p2 is the Row Index.

Here is what that looks like:

[noviceplayer]

Thank you! I will try it.

[noviceplayer]

I am not quite familiar with JS. Supposed if I want to extract all the content of the JSON to text without splitting into rows, what should by my Javascript code be like?

[Darren_Murphy]

Try the following:

return JSON.parse(p1).join('\n');

The \n causes each string to be output on a separate line. If you don’t want that, you can just remove it.

[noviceplayer]

@Darren_Murphy

return JSON.parse(p1).join('\n');

Thanks! I tried and gave an error.

Function Error

SyntaxError: “undefined” is not valid JSON

[Darren_Murphy]

Can you show me a screen shot?

[ThinhDinh]

I tried with manual p1 input and it works fine.

[noviceplayer]

Ah, I think I misunderstood what you mentioned. I will try it again. Thank you!

[noviceplayer]

@Darren_Murphy

return JSON.parse(p1).join('\n');

I understood what you are saying now and I got my first use case resolved. Now I now have another separate formatting problem that is related. I am trying to split each URL into different data row for further processing. Currently it is obtain as source below. I don’t know an easier way to do this (if there is a more straight forward way) but one way is to use what we just discussed, to convert to a JSON array and extract this through the same example as my very first use case. I attempted a javascript and was not successful.

input.split(‘,’).map(item => item.trim());

Original source:
https://storage/MI4E8iLr1AXkKl.jpg, https://storage/dyqN9ZS6HAm.jpg, https://storage/m0v9k3M5LkY3SHo.jpg

Target:
[“https://storage/MI4E8iLr1AXkKl.jpg”, “https://storage/dyqN9ZS6HAm.jpg”, “https://storage/m0v9k3M5LkY3SHo.jpg”]

Can you kindly advise please?

I even tried RegExReplace using excel formula, and I did get it working in excel but glide does not seems to recognize the formula.

[noviceplayer]

Managed to get it working using this javascript. Would be curious if there is a easier way without using coding.

const urls = p1.split(‘,’).map(url => url.trim());

const result = “[” + urls.map(url => "${url}").join(", ") + “]”;

return result;

[Darren_Murphy]

@noviceplayer

Managed to get it working using this javascript. Would be curious if there is a easier way without using coding.

In this case, yes there is. You can use a combination of Split Text and JSON Template columns:

The Split Text converts the joined list to an array, and then you simply pass that to the JSON template column like so:

[noviceplayer]

okay I will try it. Thanks again!