(BULK IMAGE UPLOADER) KB+Mouse Macro to reupload your ImageURLS / LocalFilePath Images to Airtable


#1

There wasn’t a bulk image uploader with airtable so I just made my own autohotkey macro to do this. There’s a substantial amount of users on this forum requesting a feature such as this, myself included, but for now this macro will do

Basically, I have a bunch of imgur.com URL’s already and wanted to reupload it into airtable to get the image thumbnail preview

This is what it looks like in action: (I am not pressing any buttons here asides the “Play Button”, this is macro doing all the work)

This is how i initialize the macro:

This is what the actual macro looks like

This is what my actual macro file is

This is the program I use (Easy-to-use Applications that runs off AutoHotkey)

http://www.mouserecorder.com/

My macro only works for my computer + network settings (Windows 10), so you might not get the same results I will.

But this is how you would recreate the macro SPECIFIC to your settings:

First, understand how the macro works in a nutshell

PRE-SETUP

(A) Have two columns setup. RIGHT SIDE your image host URL, LEFT SIDE is resulting airtable upload
(B) Start on an image host URL

MACRO IN ACTION:
(1) CTRL+C
(2) Left key
(3) Enter (opens up file upload area)
(4) Enter (opens up windows explorer to browse for file)
(5) CTRL+V (to paste the image URL directly in)
(6) Enter (to tell Airtable to grab the image from your imagehost)
(7) Enter (this physically uploads the file to airtable)
(8)
(9) (this brings you back to the next set of images)
(10) Go back to step 1

Also, after step 5

(5.5.) ENTER On some windows PCs, windows will display a security message asking you to login to your image host. I make my macro press enter again just in case this comes up

Inbetween each step, there’s a pause delay. After step 7 will have the longest delay, because the file is being physically uploaded to airtable. If your files big, it will take longer. I personally find on my network settings, 30 mpbs download 10 mbps upload, that 8000 ms or 8 seconds is good up to a 4 mB image size

Also, with some MouseRecording functions, you can run PixelChecks (e.g. your macro checks if a pixel on your application / window changes to determine what steps to do next). This is good if your file uploads are inconsistent in nature (e.g. you have some 4mB files, some 300 kB files, in no particular order)

Now to setup macro in MouseRecorder

First thing you want to do is download the program if you haven’t already

You can download it here:

http://www.mouserecorder.com/

There’s many other mouse recording macro solutions (Pulover’s macro) but I personally like mouserecorder a lot, because its not horribly complicated to learn

Initial setup:

The first thing you want to do when setting up your macro is setting up binding hotkeys

These are what mine look like:

I personally only use CTRL+ALT+R and CTRL+ALT+E in this example

Its important to set a hotkey for these because any mouse + keyboard movement gets added into your macro while its recording, giving you lots of bad data to work with

How to create your own macro

(1)

First, think about the shortest number of potential keys to run the macro. Ideally, the macro should be entirely run with keyboard strokes as a best practice, and minimal mouse movement for robust / reliability / reproduceability on different PCs. If you looked at my 10 steps above on what my macro looks like, its highly optimized to take an image host URL and reupload it into airtable. Basically, figure out the least number of steps you would do to normally do the repetitive task . Do this a few times to forsee any problems your macro might have

(2)

Second. When you feel comfortable you optimized your macro by doing it repetitively over and over, now you want to record it. Put your mouse where it needs to be to start the macro (over image URL). Press your start/stop recording (CTRL+ALT+R) for the hotkey initialization I discussed above. Don’t try to do it as quickly as possible, do the task at a consistent pace

(3)

Third. When you feel comfortable your macro is done, now its time to go test it out if it works. Now its time to do some dry runs / optimizations

Start changing the SPEED down to 50 (%), this increases the delay between each task by double. It helps so you can analyze exactly how long your macro is actually taking

You also want to set the macro to repeat, personally I would set this to a value of 3, so you can really see if your macro is working

You’ll know when your macro fails because it will show what step it gets stuck on, usually the issue is because you have to adjust the “Wait” delay between each steps to be a little bit higher.

(4)

Save the macro somewhere so you can just use it whenever you want.

Other applications of running macro

If you want to have a denormalized column in airtable (Multiple image attachments to one cell), learn to use your filter+view settings to control what images are going to be uploaded to airtable. E.G. you can upload multiple images to one cell, by running the macro twice for 2 seperate columns of image URLS to put into it

E.G.

when I ran this macro, I applied the following settings to ensure my macro would succeed:

[Filter: Where ImageLink is NOT EMPTY]

This way I could just run the macro from top to bottom. Since some of my cells inside the column are NULL, this entirely negates this problem using filters

You should be aware of your GB limit uploads as well depending on what service you are running

I keep my macro to take about ~20 seconds to do one image upload. I set lots of delays on it to ensure robustness. I’d rather it work 100% of the time and be slower than be twice as fast with a 95% success rate. Because preferably I just want to go on lunch, come back, and all my work to be done

This is a temporary solution to use before bulk file uploading is implement if it is going to be at all, or =Image(URL) thumbnail previewing functions similar to that of google spreadsheets

Also, if you work with lots of product spreadsheets this is a very good tool for parsing CSV-list data http://txtformat.com/

ALSO you could run this macro, if you had the local file location for each image. E.G C:\Users\Vincent\Google Drive\foo.png. You could take it even further, and use a file-location regex tools/ excel VBA / phraseexpress/ bulk image downloading / file path maker / bulk rename utility for more macroing based options adding onto the above workflows I outlined.

This could be used for both personal and or work use. For instance, if you download a list of every movie on IMDB, you can run a webscraping application to grab the image URLs, use a bulk image downloader + proxy, paste the resulting csv file into airtable, and macro reupload it into airtable using the above method. Then you can use that data, make notes about which movies you watched, tag items, etc

Also, if you want to have even more functions with everything + all these other tools I named, you can also use airtables API too after the fact. To integrate into a website, create printable spreadsheets using something like excel’s image downloader options, etc. Or use something like flashcards with anki to help you learn a new subject faster, etc

It just depends how consistent your system is, formatting, what tools you know, what your trying to do , etc

The 10 steps I laid out works exactly the same for this as well

TL;DR

This is software download: EDIT: I neglected to mention, this is a windows only program. see comments below for Mac OS program equivalents

http://www.mouserecorder.com/

Here’s my actual macro file

Basically, this is a bulk image macro uploader. you have two columns, one side has your local file location / image host url. The other side is where the macro reuploads to airtable to get image thumbnail previews


BULK IMAGE DOWNLOADER and RENAMER using excel vba
New Data Type: Image URL
#2

How about Mac… ?

I’m also looking to upload images contained in the data.


#3

Apple (or Linux) doesn’t have as much support for macro-automation unfortunately. Since autohotkey (the technology powering the windows app I recommended) is much better than Mac OS’s applescript equivalent

I did a little bit of digging and these are all the automation programs I can find related to macs.

I think of all of these, I would suggest looking into program called automator or keyboard maestro. It looks like automator and keyboard maestro can do similar things to the windows equivalent i recommended

Automator in action mouse / keyboard recording:

Download links ":

http://automator.us/downloads.html

http://www.keyboardmaestro.com/main/


#4

Thanks, @Vincent_Tang
Automator ships with OS X, which I was aware of.

How does your macro know when to stop? ie. Can it see the last entry?
I’m contemplating how to do this for a 4,500-record base.

Too bad there’s no built-in option.

Thanks.


#5

Usually on the macro editor you can specify how many iterations you want to run the program

So each iteration is basically taking an image link / local file location, and then reuploading it into airtable

I think if you use a local file path structure when uploading it might be a little more challenging since I used all imgur hosted links, so I didn’t have to worry about any of my data not being recognized / uploaded from the get go

You could always just specify the endpoint. If you knew you had 4500 records, I would do a 100 record run, and see if it has any problems. Then do the last 4400

Your macro wouldn’t do much if it went past 4501 records, because there wouldn’t be any local file path data / image urls to use and it’d just keep pushing random buttons that don’t do anything

Also, I think it would be best to follow this guideline when making your macro do things: (this was the most important step to understand below)

PRE-SETUP
(A) Have two columns setup. RIGHT SIDE your image host URL, LEFT SIDE is resulting airtable upload
(B) Start on an image host URL

MACRO IN ACTION:
(1) CTRL+C
(2) ← Left key
(3) Enter (opens up file upload area)
(4) Enter (opens up windows explorer to browse for file)
(5) CTRL+V (to paste the image URL directly in)
(6) Enter (to tell Airtable to grab the image from your imagehost)
(7) Enter (this physically uploads the file to airtable)
(8) →
(9) ↓ (this brings you back to the next set of images)
(10) Go back to step 1


Prefilter data

Also, if you have blank data where your local image path / image URLs are, I strongly suggest you do the following:

[Filter: Where ImageLink is NOT EMPTY]

Then you can run the macro

Afterwards you could just do this too:

[Filter: Where ImageLink is EMPTY]

Then you can see which images you need to upload, etc.


multiple images into one cell

If your going to put, multiple images into one cell, I would suggest the following approach

Say you have the column of data where you will put all the images at. Some cells will have1 image, others will have 2, others might have 3. All within the same column

I would use the following column approach:

  • Primary Key (Column 1)
  • Image URL-A (Column 2)
  • Image URL-B (Column 3)
  • Image URL-C (Column 4)
  • Uploaded airtable images (Column 5)

I would put the following columns next to each other and run the macro, 3 times (each time being 4500 records) . You can just drag and reorder the columns so there next to each other or hide the ones not being used

  • Image URL-A (Column 2) + Uploaded airtable images (Column 5)
  • Image URL-B (Column 3) + Uploaded airtable images (Column 5)
  • Image URL-C (Column 4) + Uploaded airtable images (Column 5)

I hope this makes sense. Unfortunately I don’t own a mac but I also heard good things about both automator and keyboard maestro


#6

Thanks for all your info.
I’m going to give something a go.

The one difference I see is, on my end, the attachment selector doesn’t allow keyboard focus - I don’t see a way to cursor over to the URL selector. So I would have to use mouse input via macro.

Will give it a go.


#7

You could also use bootcamp for mac and run windows programs too

Its not like your going to be doing this more than a handful of times anyways