Skip to main content

Stripping quotes from a field (that's outputting json) or equivalent to Excel clean()

  • December 20, 2021
  • 3 replies
  • 53 views
O
Forum|alt.badge.img+1

I have an automation that takes a record and transforms all the data within it to a custom json data structure.

The final line of this script looks like

await table.updateRecordAsync(targetRecord, {
    'json': JSON.stringify(recordOutput, null, 2).replace(/\\"/g, '"')+"\n"
}

Where recordOutput contains my json structure and all mapped values for the record that was triggered. This works fine, the Airtable field json looks exactly how I’d expect:


(double clicking on any of these fields reveals the rest of the output).

Here’s the issue:

  • When I copy these fields, I get a string where each object and text string is wrapped in an extra double quote:
"{
  ""display"": true,
  ""featured"": false,
  ""sortNumbers"": [

  • If I double click the field, and then cmd + a to select all of it, and copy, it does copy the version I want (without the excessive quote wrapping)

    • However, I need to be able to copy many cells at once (the whole column), and when I try to do that, I always get the extra quoted version.

  • In Excel, I’d use a function called clean() in the formula, which would remove characters injected by the app

  • Ideally I wouldn’t just substitute/replace, because some quotes in my json are intentional (there are many strings)

Karlstens

3 replies

Justin_Barrett
Forum|alt.badge.img+21

Airtable is doing that because your field content is spread across multiple lines. With the content on a single line, there’s no confusion (generally) about where it begins and ends. However, when the content of a single cell is spread on multiple lines, the extra surrounding quotes are added to clearly indicate that all the lines together are to be treated as a single entity from a single cell, not multiple cells. (My gut says that the duplicated inner quotes are also part of that mechanism, and I’ve got some thoughts about how the Airtable copy/paste system is treating them, but I won’t go that deep here.)

The bottom line is that I’m not aware of a way to tell Airtable to not do what it’s doing. I’ve run into this same issue, and have had to use the same workaround. In my case I only needed to copy from a single field, but I can definitely see how it’s a pain when copying from the same field across multiple records.

On the plus side, Airtable does not return all of those extra quotes to scripts. While not ideal, you could write a short manually-run script that will mash the content of all cells from a given field into a single string (separated by newlines) and display it in the output. Then you’re back to a single select-all operation to collect the data. Running the script is an extra step, but if you don’t need to run it often, it might be a tolerable step.

O
Forum|alt.badge.img+1
  • Orun_Bhuiyan
  • Author
  • Participating Frequently
  • February 4, 2022

Airtable is doing that because your field content is spread across multiple lines. With the content on a single line, there’s no confusion (generally) about where it begins and ends. However, when the content of a single cell is spread on multiple lines, the extra surrounding quotes are added to clearly indicate that all the lines together are to be treated as a single entity from a single cell, not multiple cells. (My gut says that the duplicated inner quotes are also part of that mechanism, and I’ve got some thoughts about how the Airtable copy/paste system is treating them, but I won’t go that deep here.)

The bottom line is that I’m not aware of a way to tell Airtable to not do what it’s doing. I’ve run into this same issue, and have had to use the same workaround. In my case I only needed to copy from a single field, but I can definitely see how it’s a pain when copying from the same field across multiple records.

On the plus side, Airtable does not return all of those extra quotes to scripts. While not ideal, you could write a short manually-run script that will mash the content of all cells from a given field into a single string (separated by newlines) and display it in the output. Then you’re back to a single select-all operation to collect the data. Running the script is an extra step, but if you don’t need to run it often, it might be a tolerable step.


(whoops, missed the notification for this response last month)

In my case there are several hundred records parsed by the json constructor function, so manually copying it is more overhead than I’d like.

Yes, either this or minifying the json output (which would put it in a single line) should be a workaround. Will give it a shot. Not the ideal solution, but it would be better than what I have now (which is a collection of cases that replace quotes based on their position and preceding/following string in my destination as an awk/shell script).

IMO Airtable should have a clean() function that can be called in-formula like Excel to help mitigate these kinds of situations. If I paste the output into Excel as a string, and then apply clean() to those cells, the issue is corrected.

Not sure if I have to send this feedback somewhere else other than this forum post. Any idea @Justin_Barrett? Maybe here?

Justin_Barrett
Forum|alt.badge.img+21

(whoops, missed the notification for this response last month)

In my case there are several hundred records parsed by the json constructor function, so manually copying it is more overhead than I’d like.

Yes, either this or minifying the json output (which would put it in a single line) should be a workaround. Will give it a shot. Not the ideal solution, but it would be better than what I have now (which is a collection of cases that replace quotes based on their position and preceding/following string in my destination as an awk/shell script).

IMO Airtable should have a clean() function that can be called in-formula like Excel to help mitigate these kinds of situations. If I paste the output into Excel as a string, and then apply clean() to those cells, the issue is corrected.

Not sure if I have to send this feedback somewhere else other than this forum post. Any idea @Justin_Barrett? Maybe here?


@Orun_Bhuiyan That category is one place to share feedback, but you can also write directly to support@airtable.com and share your thoughts. It’s anyone’s guess if a function similar to Excel’s clean() function will ever be added, but it couldn’t hurt to ask!

Terms & ConditionsAccessibility statement