URL to Attachment

@Stephen_Suen @Kasra, this is all working great for me except for one component: My image in the Attachment field doesn’t display as it should. See screenshot above. The link I am trying to load goes to a Google Drive image file. The permissions are set so anyone with the link can view.

How can I modify the code or the link so it correctly imports the Google Drive link as an attachment?

The link follows this pattern:

https://drive.google.com/file/d/file_id/view?usp=sharing

Here is the code:

let submissionsTable = base.getTable('Table 10');
let urlField = submissionsTable.getField('Attachment Source URL');
let attachmentField = submissionsTable.getField('Attachment');

let submissionsQuery = await submissionsTable.selectRecordsAsync();
let updates = [];
for (let record of submissionsQuery.records) {
    let url = record.getCellValue(urlField);
    let attachments = record.getCellValue(attachmentField);

    // If this record already has an attachment, skip it.
    if (attachments !== null) {
        continue;
    }

    // Otherwise, attach the image at the URL.
    updates.push({
        id: record.id,
        fields: {
            [attachmentField.id]: [{url: url}]
        }
    });
}

// Update records in batches of 50.
while (updates.length > 0) {
    await submissionsTable.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}
1 Like

@Marty_McCoy It looks like the URL you’re trying to attach points to a web page, rather than a direct URL for the raw image. This means when you’re updating the record, you’re uploading HTML rather than the PNG/JPG/etc itself.

Have you considered using the URL preview app instead? This will allow you to preview Google Drive share URLs by clicking cells in grid view:

2 Likes

Thanks @Stephen_Suen, I need to get them out of Google Drive because of limited storage. Does URL Preview work with Box? I have unlimited storage there.

Alternatively, do you know a way for me to access the URL for the raw image in Google Drive? It’d be great to use this URL to Attachment scripting solution for scalability.

Update

I got the script to import by using a link as follows: https://drive.google.com/uc?export=view&id=file_id

Update 2

After further testing, the script is not working consistently, even though all of the Google Drive links now point to the raw image, not the HTML. It works for a couple of records, then stops.

@Stephen_Suen any ideas to troubleshoot are appreciated.

Possibly helpful intel:

  • When I use Python’s requests library to download files from Google Drive, I use a base downloading URL https://docs.google.com/uc?export=download
  • Then, I pass in the the file ID and the destination where I want to save the file.

Maybe something similar needs to happen in this script? Use this base URL and the file ID, rather than using the other format (https://drive.google.com/uc?export=view&id=file_id).

Anybody else have a solve for this?

This sounds precisely like the known bug documented here and here.

I see this failure often across many clients despite many attempts hone the URLs into Google Drive using different patterns such as:

  • https://drive.google.com/uc?export=download&id=" + thisID
  • https://drive.google.com/uc?export=view&id=" + thisD
1 Like

@Bill.French I tried using this URL pattern and it’s working right now. I am now not confident that it’ll work consistently, but glad it’s working at least sometimes. Thank you!

Can’t edit my comment above. Last week, I used this script to process 322 google drive photo links in one click! But today it’s not working.

1 Like

The URL Preview app doesn’t currently work with Box, but that’s a fantastic feature request! Many of my clients use Box, and I’m sure that this would be a very handy feature for them! Please submit this as a feature request to either the #show-and-tell:product-suggestions category, or by emailing support@airtable.com.

Last week, I used this script to process 322 google drive photo links in one click! But today it’s not working.

I actually don’t have the answer to solving this script problem for you, but I’ve setup my clients with Integromat in order to convert their Google Drive files into Airtable attachments.

1 Like

Thanks Scott!

The script just worked for me to process 105 photos. Thankfully, I am in a position where if the script doesn’t work, I can wait an hour or two and try again and it seems there’s a good chance it’ll work then.

Marty, that does sound more like a rate limit (number of photos processed per x amount of time) than an issue with your script – so that’s good news!

Google Drive and Dropbox are tricky. They intentionally don’t make it easy to discover their URL patterns to “hotlink” an image using their server. Their services are meant for personal file sharing, not web hosting, which would cost them more bandwidth to provide their services. I deal with this limitation a lot.

1 Like

I also believed this might be the suspected cause of such failures, but I was wrong.

The Test Climates

With more than three clients I have engaged in extreme measures to isolate issues concerning attachment inconsistencies and failures to process images as well as PDF documents. All of my tests and debugging activities are specific to the use of the Airtable API and Google Apps Script to perform mass extractions and attachment uploads into Airtable.

In 100% of the cases, we ultimately proved that document sources were not the cause. If the sources are properly configured for accessibility and the file URLs are constructed in a manner to reveal the actual artifacts (as opposed to rendering veneers), the Drive platform provided the climate for a successful extraction.

Rate Limiting

We also ruled out rate limiting and other quota breaches by performing mass high-velocity extractions into other systems such as local drives, network drives, and even Amazon S3 object stores. One test included the processing of 10,000 PDF documents from Google Drive into S3 over a five hour period without a single issue; roughly one TB of content.

It’s Airtable

As such, the failures that these three clients experience (even today) with their Airtable solutions are almost certainly in Airtable itself, not the document source platforms. To be clear, this causal assertion is with the Airtable as it pertains to the API only. There may or may not be other attachment issues with manual uploads or with other script processes in Blocks or automations but I can only speak to my experiences with the API.

The Pattern

If we’re talking about the same problem y’all are discussing here, the error condition is unmistakable - an API call into Airtable creates an attachment and for a few seconds it looks like the upload sticks because the UI indicates a document has arrived in the cell. The icon of the document is greyed out during this brief indication period which typically lasts a few seconds. Then the icon vanishes as if to suggest that Airtable tried to store the file but gave up.

Oddly, this behaviour is roughly the identical experience if you use the API to make an attachment using a URL that cannot be accessed by Airtable, a condition that should fail.

The Frequency of the Pattern

These observed failures are intermittent and from time-to-time there are periods where all attachment attempts via the API fail. And there are periods where all attachment attempts succeed. Largely, though, it seems they fail intermittently and randomly.

We also invested heavily in script processes that perform up to ten retries and capture upload success analytics when attachment attempts fail. The analytics from attachment attempts that are retired show equally random outcomes - sometimes a second retry succeeds; sometimes ten retries fail. And in almost every failure situation, a manual upload works.

I believe this issue is still creating problems from time-to-time and in very random cases and it has forced many of my clients to skirt the issue by avoiding attachments and referencing documents by URL only.

3 Likes

This may be a silly question Bill, but are you using the same batch of documents (i.e. PDFs you indicated) in all cases?

The only times I’ve seen this mystery photo popup for a moment and go away is when in fact the URL to the image no longer works, for example in the case of trying to pull an Instagram photo that has some kind of url timestamp embedded in it. When I then manually go to the URL it always produces an error. Also, certain file types seem to fail on me, like video URLs from Instagram and probably for good reason lol.

I have used Automations as well as Zapier zaps. I believe when you say API this would include a zap that relies on the Airtable API to work.

I am very happy to hear that there’s no technical rate or bandwidth limit from your experiments – and how cool that you were able to do some analytics logging too – but I am definitely curious about whether these same URLs that fail can be accessed via the regular web at the same time as they are failing in Airtable.

That’s good to hear. When did you start to examine these issues closely? My attention to this issue began in Nov 2019 and by Dec 19, 2019 we noticed a steep regression (i.e., many more failures). This issue was pervasive for about two months and on or about 20-Jan-2020 the error volume subsided to a much more intermittent experience. I believe the issue continues to plague API-based solutions but only from time-to-time; I have not performed any tests recently, although just checking on one client’s activity, the a small number of failures have occurred in the past 12 hours.

No. These are/were processes that generate new PDFs all day long.

This is correct. Zapier certainly uses “an” API to process its recipes.

To be clear, if there are bandwidth quotas, we’re not creating a condition where we have experienced them. :wink:

It’s a vital question in the quest to understand this issue and this was a key test as we researched it. In 100% of the cases, the URLs that failed were tested with a browser that was certified without a security context. This is a crucial aspect of our testing protocol but it wasn’t developed to isolate the cause of this problem. We test security protocols for video analytics and AI apps for transit surveillance systems and we have to keep clean machines with browsers that are void of any cookies or historical login knowledge. This was the environment we used to be certain that the URLs to these failing documents (a) existed, (b) contained content, and © were accessible without any security context.

It’s also important to note that our tests also developed metrics that showed in subsequent automation processes involving the same failed URLs, attachment success was likely on 27 of 100 retries of previously failed attachment attempts.

What does this data suggest?

  1. The issue is intermittent.
  2. Failures are not likely related to specific URLs.
  3. Failures are not likely related to the way the API is being used (relatively good success rate).
  4. The code executed using Airtable’s manual feature is not likely built on the API offered to the public.

If these observations are true, we can probably conclude there’s a high probability that the issue is in the API itself or the environment with which the API is being used.

We don’t use Zapier so I really can’t comment if that environment manages to avoid this issue. But there are two data points concerning this that should be considered:

  1. Zapier and others like Integromat supported Airtable long before the public API was released. This suggests these vendors probably don’t use the same gateway to access or update Airtable that the rest of us are required to use.
  2. There’s no evidence (that I’m aware of) where Zapier recipes have exhibited this attachment failure, although I don’t even know if Zapier and Integromat can perform attachments. I have a hunch they can and they are designed with different APIs separate and apart from public API.

I like a good mystery.

Hey,

Those who are looking for a native solution to do this, please see my topic here :

Florian

4 Likes

Exactly what I needed! Thank you!

If the field with url have multiple urls, how do I add them as multiple attachments?

You must create an array of objects

[
 { 'url': downloadUrl, 'filename': fileName },
 { 'url': downloadUrl, 'filename': fileName },
]

This can be done the no-code way as well with the Convert URL to Attachments function in On2Air Actions.

With Actions, you also get an additional 60+ features that integrate with Airtable.

Features like creating Google Docs automatically with Airtable data, bulk create, edit, or delete multiple records, set default field values, copy fields, perform financial calculations, sync Google Sheets, and more.

I am trying to run this but when I click run, nothing is happening in airtable. The script window is simply flashing blank. I also noticed if I pass the url into an attachment manually, it says unable to obtain metadata. Could it be an error with this site alone?

https://images1.apartments.com/i2/FoQUKOvUaZ9kFFrVZJYHKZPtIKt0_VRSpGprD4GGx9Q/117/deco-apartments-denver-co-poolside-firepit.jpg

Here is the code that ended up working for me. Based on @Stephen_Suen and @Marty_McCoy’s version, adapted for a URL column which may have empty values:

let myTable = base.getTable('Table 1');
let urlField = myTable.getField('URL field name');
let attachmentField = myTable.getField('Attachment field name');

let submissionsQuery = await myTable.selectRecordsAsync();
let updates = [];

for (let record of submissionsQuery.records) {
    let url = record.getCellValue(urlField);
  
    // If the URL field is empty, skip this record.
    if (url === null) continue;
    
    // If this record already has an attachment, skip it.
    let attachments = record.getCellValue(attachmentField);
    if (attachments !== null) continue;
    

    // Otherwise, attach the image at the URL.
    updates.push({
        id: record.id,
        fields: {
            [attachmentField.id]: [{url: url}]
        }
    });
}

// Update records in batches of 50.
while (updates.length > 0) {
    await myTable.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}