Skip to main content

SUBSTITUTE() not removing escaped quotes added to response

  • June 14, 2020
  • 3 replies
  • 44 views
D

I read through this thread and this one trying to find a way to remove escaped quotes /" from the API response on a couple of fields. It looks like, if the field is calculated (I could be misspeaking, I’m just a developer working with the API so I’m unfamiliar with how using the Airtable interface works.) and includes a comma, it’ll wrap the string in extra quotes in the response. Ex: Agency Name for Website: "\\"Hearing, Speech and Deaf Center (HSDC)\\""

I’ve tried all of these variations in filterByFormula:

`SUBSTITUTE('\\"', '')`
`SUBSTITUTE("\\"", "")`
`SUBSTITUTE({Agency Name for Website}, '\\"', '')`
`SUBSTITUTE({Agency Name for Website}, "\\"", "")`

and all versions without escaping the double quote, and encoded each here before appending them to the URL. None of them have any effect.

Am I missing something? Something else in my parameters messing with it? A bug? Is there another way to remove the quotes? Thanks for any help.

This is the full list of encoded parameters in my request:

filterByFormula=SUBSTITUTE(%7BAgency+Name+for+Website%7D%2C+'%5C%22'%2C+'')&sort%5B0%5D%5Bfield%5D=Agency+Name+for+Website&sort%5B0%5D%5Bdirection%5D=asc&view=Website+view

3 replies

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • 4647 replies
  • June 14, 2020

Admittedly I haven’t used Airtable’s API before, but I’ve done a lot of quote escaping, and I think I know the problem. If the quote is escaped as you specified above, that means that it’s not just an escaped quote that you need to remove, but the backslash in front of it as well, which will require its own escape.

Here’s the code as I’d write it inside Airtable:

SUBSTITUTE({Agency Name for Website}, '\\\\"', '')

The first backslash escapes the second one, and the third escapes the quote, so that it looks for a literal backslash followed by a quote.

Convert as needed for your filterByFormula usage, and let me know if that works.

D
  • Derek_Sheppard
  • Author
  • New Participant
  • 1 reply
  • June 14, 2020

Admittedly I haven’t used Airtable’s API before, but I’ve done a lot of quote escaping, and I think I know the problem. If the quote is escaped as you specified above, that means that it’s not just an escaped quote that you need to remove, but the backslash in front of it as well, which will require its own escape.

Here’s the code as I’d write it inside Airtable:

SUBSTITUTE({Agency Name for Website}, '\\\\"', '')

The first backslash escapes the second one, and the third escapes the quote, so that it looks for a literal backslash followed by a quote.

Convert as needed for your filterByFormula usage, and let me know if that works.


Thank you!

That does make sense, but it still isn’t working. As it turns out, I can’t get any SUBSTITUTE formula to work in the API call, so I’m wondering if there’s something else going on. I’ll see if the Airtable user can apply the formula directly to that column to see if we can resolve the issue that way.

B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3263 replies
  • June 14, 2020

This is the sign of crappy data.

This often occurs in Airtable database fields where someone has created values with double quotes as part of the value itself. This typically occurs when importing data that’s not ideally encoded or when an over-zealous base designer tried to concatenate strings whose formulas are imperfect. But there are many ways to create poorly-formatted data and in Airtable, where formula-fields are used often, almost anything goes.

There are two approaches to resolving this:

  1. Locate the source and clean up the data. No data should have embedded quotes like this unless it’s a rich-text field.
  2. Transform the values in any API processes by replacing the quotes much the way substitute() is used in the examples.

Option #1 is the preferred approach because even if you never use the API, this is a mess for any process, export, or reporting feature that must also worry about this crappy data. Better to nip it in the bud rather than create a lifetime of bandaid repairs.

Almost every API app I’ve built has encountered this type of mess and because of it, I’ve had to bake into my own API wrappers the ability to (a) see this pattern, and (b) remedy it.

And BTW - it’s not really an escaping issue - it’s a JSON serialization process; you’re essentially stringifying the content to get a clean value that can exist in a JSON payload. Content like this would be unable to traverse the Interwebs as JSON without doing this. The serializer is simply accommodating the need to account for double quotes inside string fields. This is a little different than escaping HTML entities but the stringifier uses a similar approach (i.e., a backslash) to indicate the next character is to be ignored as far as the JSON parser is concerned.

To test these nuances, use the Script Block to make a string with embedded quotes and run JSON.stringify(str) on it to see the output.

And to be clear, I’m not sure the comma actually triggers the addition of the quotes in every case, but it’s possible. If so, then this is an internal issue with Airtable, but the upshot is the same - it’s bad. I have also noticed a bias in the problem related to ID fields in Airtable - perhaps related to the instances that these occur.


Terms & ConditionsAccessibility statement