Skip to main content
Solved

Formula to get image attachment url


I have an attachment field containing images. I see that the stored attachment object has a url property. In another formula field I would like to show just the attachement url. Any ideas?


Thanks.

Best answer by AlliAlosa

The following formula will give you the URL for an attachment field 🙂


RIGHT(LEFT({Attachment Field}, LEN({Attachment Field}) - 1), LEN(LEFT({Attachment Field}, LEN({Attachment Field}) - 1)) - SEARCH("https://", {Attachment Field}) + 1)
View original
Did this topic help you find an answer to your question?

17 replies


Where do you see this URL property? Are you looking at the underlying data via an API? I created a test table with an attachment field, and added an image, but I don’t see a URL property anywhere.


  • Inspiring
  • 382 replies
  • Answer
  • February 15, 2019

The following formula will give you the URL for an attachment field 🙂


RIGHT(LEFT({Attachment Field}, LEN({Attachment Field}) - 1), LEN(LEFT({Attachment Field}, LEN({Attachment Field}) - 1)) - SEARCH("https://", {Attachment Field}) + 1)

  • New Participant
  • 1 reply
  • December 8, 2019
AlliAlosa wrote:

The following formula will give you the URL for an attachment field 🙂


RIGHT(LEFT({Attachment Field}, LEN({Attachment Field}) - 1), LEN(LEFT({Attachment Field}, LEN({Attachment Field}) - 1)) - SEARCH("https://", {Attachment Field}) + 1)

Hey, is there a way to get only the urls separated by coma, without the file names?


David_Penev wrote:

Hey, is there a way to get only the urls separated by coma, without the file names?


The formula that @AlliAlosa posted will work for a single attachment. With multiple attachments, the process would be much more complex because the filenames and URLs are interspersed. You can’t write a single formula that would extract the URLs from an arbitrary number of attachments. For example, if you write a formula (or series of formulas) that can extract up to five URLs, you’ll have to revisit the code the moment you have more than five attachments.


What I suggest doing is using the sample that @AlliAlosa provided to learn how the first URL was extracted. To get the second URL, search for it using the index of the first one as a reference. Same for the third, and so on.


I made a tool for this. It takes attachments, extracts the URLs from them, and then saves all of the URLs (comma separated) in a new field.


  • New Participant
  • 1 reply
  • January 27, 2020
Abdulrahman_Alz wrote:

I made a tool for this. It takes attachments, extracts the URLs from them, and then saves all of the URLs (comma separated) in a new field.


Hello!.. I saw your video and is amazing!!!, great work. I’ll boy some of your extensions.

Nick


  • Participating Frequently
  • 7 replies
  • June 29, 2020
AlliAlosa wrote:

The following formula will give you the URL for an attachment field 🙂


RIGHT(LEFT({Attachment Field}, LEN({Attachment Field}) - 1), LEN(LEFT({Attachment Field}, LEN({Attachment Field}) - 1)) - SEARCH("https://", {Attachment Field}) + 1)

Hello,

Thanks for that!


Would you be able to make a formula that gets up to 3 URLs if there’s up to 3 attachments in the attachment field?


I’m trying but I get confused with all the left, len, right…


@AlliAlosa @Justin_Barrett


Greg_B wrote:

Hello,

Thanks for that!


Would you be able to make a formula that gets up to 3 URLs if there’s up to 3 attachments in the attachment field?


I’m trying but I get confused with all the left, len, right…


@AlliAlosa @Justin_Barrett


While this could technically be done in a single formula, it would be a massive formula. In cases like this, I prefer to divide and conquer, splitting the effort across more than one formula field.


To begin, I’ll take the default output from the attachment field and spread things out a bit by replacing the parentheses around the URLS with large blocks of spaces. I’ll also prepend a number that tells me how many attachments exist, which I’ll use later. This is a formula field that I named {Expanded}:


IF(

    Attachment,

    ((LEN(Attachment) - LEN(SUBSTITUTE(Attachment, ",", ""))) + 1)

    & " - "

    & SUBSTITUTE(SUBSTITUTE(Attachment, "(", REPT(" ", 100)), ")", REPT(" ", 100))

)


That creates the following output (shown in the field’s expanded view, and based on a three-attachment field):



The URL extraction then becomes much easier. Each URL is grabbed in a 200-character block using the MID() function, and the surrounding spaces are trimmed off with the TRIM() function. In my test, I found that I could grab the first URL block from my expanded string starting at the 100th character, the second at the 400th, and the third at the 700th. If you have really long filenames, these starting positions may need slight adjustments. For separation between each URL in the final output, I just used commas, but you can change that as desired. Anyway, here’s the final formula, which I put into a field named {URLs}.


IF(

    Expanded,

    TRIM(MID(Expanded, 100, 200))

    & IF(LEFT(Expanded, 1) > 1, ", " & TRIM(MID(Expanded, 400, 200)))

    & IF(LEFT(Expanded, 1) > 2, ", " & TRIM(MID(Expanded, 700, 200)))

)



Justin_Barrett wrote:

While this could technically be done in a single formula, it would be a massive formula. In cases like this, I prefer to divide and conquer, splitting the effort across more than one formula field.


To begin, I’ll take the default output from the attachment field and spread things out a bit by replacing the parentheses around the URLS with large blocks of spaces. I’ll also prepend a number that tells me how many attachments exist, which I’ll use later. This is a formula field that I named {Expanded}:


IF(

    Attachment,

    ((LEN(Attachment) - LEN(SUBSTITUTE(Attachment, ",", ""))) + 1)

    & " - "

    & SUBSTITUTE(SUBSTITUTE(Attachment, "(", REPT(" ", 100)), ")", REPT(" ", 100))

)


That creates the following output (shown in the field’s expanded view, and based on a three-attachment field):



The URL extraction then becomes much easier. Each URL is grabbed in a 200-character block using the MID() function, and the surrounding spaces are trimmed off with the TRIM() function. In my test, I found that I could grab the first URL block from my expanded string starting at the 100th character, the second at the 400th, and the third at the 700th. If you have really long filenames, these starting positions may need slight adjustments. For separation between each URL in the final output, I just used commas, but you can change that as desired. Anyway, here’s the final formula, which I put into a field named {URLs}.


IF(

    Expanded,

    TRIM(MID(Expanded, 100, 200))

    & IF(LEFT(Expanded, 1) > 1, ", " & TRIM(MID(Expanded, 400, 200)))

    & IF(LEFT(Expanded, 1) > 2, ", " & TRIM(MID(Expanded, 700, 200)))

)



Thanks for your Formula !


What would it be with more than 3 attachments ? For example 10 attachments ?


Bigland_Bigland wrote:

Thanks for your Formula !


What would it be with more than 3 attachments ? For example 10 attachments ?


For more than three attachments, keep expanding the second formula. Notice the pattern in the MID() functions:



It starts by grabbing characters beginning at position 100…



…then position 400, then position 700. In short, we’re adding 300 each time to grab the next URL. Just keep that pattern going. The next one would start at position 1000, then 1300, then 1600, etc.


Also notice the pattern in the comparisons made before grabbing each subsequent piece. Each line increases the number we’re comparing against from the start of {Expanded}. The first one is just grabbed outright because we know we have at least one URL. If we have more than one—IF(LEFT(Expanded, 1) > 1—we grab the next piece. If more than 2, grab the next one. Keep that pattern going as well.


Also, for more than 9 attachments, you would need to change all instances LEFT(Expanded, 1) to LEFT(Expanded, 2).


A lot of programming is about finding patterns. Learn to see the patterns in the data you’re playing with, and you can write code to find things based on those patterns.


Justin_Barrett wrote:

For more than three attachments, keep expanding the second formula. Notice the pattern in the MID() functions:



It starts by grabbing characters beginning at position 100…



…then position 400, then position 700. In short, we’re adding 300 each time to grab the next URL. Just keep that pattern going. The next one would start at position 1000, then 1300, then 1600, etc.


Also notice the pattern in the comparisons made before grabbing each subsequent piece. Each line increases the number we’re comparing against from the start of {Expanded}. The first one is just grabbed outright because we know we have at least one URL. If we have more than one—IF(LEFT(Expanded, 1) > 1—we grab the next piece. If more than 2, grab the next one. Keep that pattern going as well.


Also, for more than 9 attachments, you would need to change all instances LEFT(Expanded, 1) to LEFT(Expanded, 2).


A lot of programming is about finding patterns. Learn to see the patterns in the data you’re playing with, and you can write code to find things based on those patterns.


Thank you so much. You save my day ! Good Work and clear explanations.


AlliAlosa wrote:

The following formula will give you the URL for an attachment field 🙂


RIGHT(LEFT({Attachment Field}, LEN({Attachment Field}) - 1), LEN(LEFT({Attachment Field}, LEN({Attachment Field}) - 1)) - SEARCH("https://", {Attachment Field}) + 1)

Thanks so much, you have saved me hours of work!


Suddenly, today, all those formulas above have stopped working. By some reason Í do not understand. Maybe Airtable did un update to block this way of getting a permanent URL from attachments. Anyone knows why this is happening? Thank's in advance. 🙂


gaston
Forum|alt.badge.img+3
  • Known Participant
  • 16 replies
  • May 4, 2023

Same issue as @Alvaro_Hernande here!! 😓

@Justin_Barrett @ScottWorld any idea on what might be happening?


ScottWorld
Forum|alt.badge.img+20
  • Brainy
  • 8718 replies
  • May 4, 2023

Formulas can no longer be used to get URLs of attachments, because they will result in non-working URLs.

To get a temporary working URL for an attachment that will expire after 2 hours, you can temporarily access the URL for 2 hours via Airtable's automations or any other app that accesses Airtable's API, such as Make's advanced automations for Airtable

To get a permanent working URL for an attachment that won't expire, you would need to move your attachments to a cloud drive (such as Google Drive), and you can use a tool like Make's advanced automations & integrations to manage those files and access the URLs of those files. You could even use Airtable's Google Drive sync to keep a list of all the permanent URLs within Airtable.

If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is how you would instantly trigger a Make automation from Airtable.

I also give live demonstrations of how to use Make in many of my Airtable podcast appearances here. For example, in this video, I show how to work with Airtable attachments in Make .

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld


gaston wrote:

Same issue as @Alvaro_Hernande here!! 😓

@Justin_Barrett @ScottWorld any idea on what might be happening?


This has broken for me as well which is very frustrating.


  • New Participant
  • 4 replies
  • April 30, 2024
I built a service called Air CDN (https://aircdn.io) that lets you keep using Airtable image attachments with static URLs that never expire.

It works by creating a formula column that points to Air CDN's servers, which is updated via a script action whenever an attachment is updated.

Please try it out and let me know if you have any questions or suggestions!

 


Reply