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

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:

json-output-in-airtable
(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)

1 Like

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.