Functions, Formulas, & Scripts

This apple script will take two column inputs from Excel to bulk-download images to Desktop for processing. Images uploaded to the web server are named using the convention styleID.jpg

Column K (filename) is the result of Excel formula =styleIDColumn & ".jpg"

Column G (URLs) lists URLs to image files. If the extension of the file is not .jpg, the script will often work anyway and make the conversion. Sometimes it won't. If data is missing from either column (a cell is empty), the script will exit and return an error. At the end of the process, check the number of images downloaded. If that does not amount to the number of rows, check errors and adjust.


tell application "Microsoft Excel"

    set filenames to value of every cell of range "O2:O9000" of sheet 1 of document 1

    set URLs to value of every cell of range "P2:P9000" of sheet 1 of document 1

end tell

repeat with i from 1 to count URLs

    if (item i of filenames is not missing value) and (item i of URLs is not missing value) then

    	set thisFname to quoted form of (POSIX path of ((path to desktop) as text) & item i of filenames)

    	set thisUrl to quoted form of item i of URLs

    	set status to (do shell script "curl -s -o " & thisFname & space & thisUrl)



    end if

end repeat

Excel

Assume column A contains target/start data.

Add a comma after every 4 digits

Format the row of digits to text. In a new column, use formula:

=MID(A2,1,4)&","&MID(A2,5,4)&","&MID(A2,9,4)&","&MID(A2,13,4)

Extend the formula based on depth of taxonomy entry (will be no longer than four levels).

Turn Department data into keywords

Step 1

=PROPER(SUBSTITUTE(A2,"/",", "))

Step 2

=RIGHT(B2, LEN(B2) - 3)

Remove one character from left

=RIGHT(A2, LEN(A2) - 1)

Replace spaces with dashes (Shopify handles)

=SUBSTITUTE(A1," ","-")

See if there's a blank

=COUNTIF(R2, "<>")

Feel free to get in touch with any questions.