AppleScript and Excel: Getting row/column index, and skipping blank cells

Here is the script that allows duplicate name.

Testing is for hypochondriac programmers. Please save all files or at least files on the desktop. Really should open a names workbook, but ...

(*
  Column A: URLs to images - some may be blank
  Column B: First name
  Column C: Last name


It is easier to diagnose problems with debug information. I suggest adding
log statements to your script to see what is going on.  Here is an example.


    Author: rccharles
   
    For testing, run in the Script Editor.
      1) Click on the Event Log tab to see the output from the log
          statement
      2) Click on Run
     
  For running shell commands see:
  http://developer.apple.com/mac/library/technotes/tn2002/tn2065.html
   


*)


tell application "Microsoft Excel"
   
    tell active sheet
        tell used range
            set RowCount to count of rows
        end tell
        log "RowCount is " & RowCount
    end tell -- active sheet   
end tell -- application   

repeat with theIncrementValue from 1 to RowCount
    tell application "Microsoft Excel"
       
        tell active sheet
            set theCell to "A" & theIncrementValue & ":A" & theIncrementValue
            log "theCell is " & theCell
            set theURL to get value of range theCell
            log "theURL  is " & theURL
           
            set theCell to "B" & theIncrementValue & ":B" & theIncrementValue
            log "theCell is " & theCell
            set theFirstName to get value of range theCell
            log "theFirstName is " & theFirstName
           
            set theCell to "C" & theIncrementValue & ":C" & theIncrementValue
            log "theCell is " & theCell
            set theLastName to get value of range theCell
            log "theLastName is " & theLastName
        end tell -- active sheet
    end tell -- application   
   
    if theURL is not "" then
        -- allow multiple versions of the file
        set fileFolder to path to desktop as text
        log "fileFolder is " & fileFolder
        set fileName to theFirstName & theLastName & ".jpg"
        log "fileName is " & fileName
        set fileCount to 0
        set doDownload to true -- what a hack because of no iterate
        repeat while fileExists(fileFolder & fileName)
            set fileCount to fileCount + 1
            set fileName to theFirstName & theLastName & "#" & fileCount & ".jpg"
            if fileCount > 50 then
                display dialog "we have over fifty copies of " & fileFolder & fileName & " skipping. " giving up after 20
                set doDownload to false
                exit repeat
               
            end if
        end repeat
       
        if doDownload is true then
            try
                set toUnix to "curl " & theURL & " -o $HOME/Desktop/" & fileName
                log "toUnix is " & toUnix
                set fromUnix to do shell script toUnix
                log "fromUnix# is " & fromUnix
            on error errMsg
                log "ERROR: " & errMsg
            end try
        end if
    end if -- have URL
   
end repeat -- draggin through excel then processing



-- ---------------------------------------------------------------
-- Philop Regan  https://stackoverflow.com/questions/3469389/applescript-testing-for-file-existence
--
--  fileExists(theFile)
--     theFile as a text which is a string
-- returns true or false
--
-- example
--    if fileExists(fileName) then
--       ...
--    end if
on fileExists(theFile)
    log "    theFile is " & theFile
    tell application "System Events"
        if exists file theFile then
            log "    file exists"
            return true
        else
            log "    not here"
            return false
        end if
    end tell
end fileExists

Oh, applescript has the unix quoting built in 😊. Oh, seemed like a good idea to put a space between the first and last names 😎.

(*
  Column A: URLs to images - some may be blank
  Column B: First name
  Column C: Last name


It is easier to diagnose problems with debug information. I suggest adding
log statements to your script to see what is going on.  Here is an example.


    Author: rccharles
   
    For testing, run in the Script Editor.
      1) Click on the Event Log tab to see the output from the log
          statement
      2) Click on Run
     
  For running shell commands see:
  http://developer.apple.com/mac/library/technotes/tn2002/tn2065.html
   


*)


tell application "Microsoft Excel"
   
    tell active sheet
        tell used range
            set RowCount to count of rows
        end tell
        log "RowCount is " & RowCount
    end tell -- active sheet   
end tell -- application   

repeat with theIncrementValue from 1 to RowCount
    tell application "Microsoft Excel"
       
        tell active sheet
            set theCell to "A" & theIncrementValue & ":A" & theIncrementValue
            log "theCell is " & theCell
            set theURL to get value of range theCell
            log "theURL  is " & theURL
           
            set theCell to "B" & theIncrementValue & ":B" & theIncrementValue
            log "theCell is " & theCell
            set theFirstName to get value of range theCell
            log "theFirstName is " & theFirstName
           
            set theCell to "C" & theIncrementValue & ":C" & theIncrementValue
            log "theCell is " & theCell
            set theLastName to get value of range theCell
            log "theLastName is " & theLastName
        end tell -- active sheet
    end tell -- application   
   
    if theURL is not "" then
        -- allow multiple versions of the file
        set fileFolder to path to desktop as text
        log "fileFolder is " & fileFolder
        set fileName to theFirstName & " " & theLastName & ".jpg"
        log "fileName is " & fileName
        set fileCount to 0
        set doDownload to true -- what a hack because of no iterate
        repeat while fileExists(fileFolder & fileName)
            set fileCount to fileCount + 1
            set fileName to theFirstName & " " & theLastName & "#" & fileCount & ".jpg"
            if fileCount > 50 then
                display dialog "we have over fifty copies of " & fileFolder & fileName & " skipping. " giving up after 20
                set doDownload to false
                exit repeat
               
            end if
        end repeat
       
        if doDownload is true then
            try
                -- Convert to Unix form
                set unixPath to POSIX path of fileFolder & fileName
                log "unixPath = " & unixPath
                set quotedUnixPath to quoted form of unixPath
                log "quoted form is " & quotedUnixPath
               
                set toUnix to "curl " & theURL & " -o " & quotedUnixPath
                log "toUnix is " & toUnix
               
                set fromUnix to do shell script toUnix
                log "fromUnix# is " & fromUnix
            on error errMsg
                log "ERROR: " & errMsg
            end try
        end if
    end if -- have URL
   
end repeat -- draggin through excel then processing



-- ---------------------------------------------------------------
-- Philop Regan  https://stackoverflow.com/questions/3469389/applescript-testing-for-file-existence
--
--  fileExists(theFile)
--     theFile as a text which is a string
-- returns true or false
--
-- example
--    if fileExists(fileName) then
--       ...
--    end if
on fileExists(theFile)
    log "    theFile is " & theFile
    tell application "System Events"
        if exists file theFile then
            log "    file exists"
            return true
        else
            log "    not here"
            return false
        end if
    end tell
end fileExists

Codes a little convoluted. Should only do things once, but this code doesn't always.

R

There is always the hacker method. Use the try statement to capture the error and ignore.

    try
        set fromUnix to do shell script toUnix
        log "fromUnix# is " & fromUnix
    on error errMsg
        log "ERROR: " & errMsg
    end try

(*
  Column A: URLs to images - some may be blank
  Column B: First name
  Column C: Last name
*)

tell application "Microsoft Excel"
  
    tell active sheet
        tell used range
            set RowCount to count of rows
        end tell
        log "RowCount is " & RowCount
      
        repeat with theIncrementValue from 1 to RowCount
            set theCell to "A" & theIncrementValue & ":A" & theIncrementValue
            log "theCell is " & theCell
            set theURl to get value of range theCell
            log "theURl  is " & theURl
          
            set theCell to "B" & theIncrementValue & ":B" & theIncrementValue
            log "theCell is " & theCell
            set theFirstName to get value of range theCell
            log "theFirstName is " & theFirstName
          
            set theCell to "C" & theIncrementValue & ":C" & theIncrementValue
            log "theCell is " & theCell
            set theLastName to get value of range theCell
            log "theLastName is " & theLastName
            if theURl is not "" then
                try
                    set toUnix to "curl " & theURl & " -o $HOME/Desktop/" & theFirstName & theLastName & ".jpg"
                    log "toUnix is " & toUnix
                    set fromUnix to do shell script toUnix
                    log "fromUnix# is " & fromUnix
                on error errMsg
                    log "ERROR: " & errMsg
                end try
            end if
        end repeat -- draggin through excel then processing
    end tell -- active sheet
end tell

I have not done much testing. Backup your data. I what happens if the file already exists on the desktop is a mystery to me. I assume it is replaced. I have not considered point #2. I suggest you create a new folder and place output in new folder.

R

- In your code block to check if a URL is present, the condition to exist that loop is if the fileCount >50. Is that to check for an excessive number of duplicate files?

Yes. I think is a good idea to avoid the potential of infinite loops. Also, it avoids a snafu of a bug in checking for file existence. When I wrote the code for checking for file existence, I remember it was a hassle [ I wrote it earlier in the years. ]

- I assume "get value" copies the entire contents of a given cell. In my testing, first names with a space (i.e. Jean Luc) get truncated (i.e. Jean), even though the logs show that the filename is set to "Jean LucPicard.jpg". Is this the expected behavior?

I'm surprised about this. Oh, it's because of Unix 👿. And the bug originates in the code I copied from you 😉😉. Fix:

set toUnix to "curl " & theURL & " -o $HOME/Desktop/" & "'" & fileName & "'"

Unix command line parses on spaces and a bunch of special characters. I changed the code to quote the file name. Names like O'Brian are not allowed. I'll have to fix up this issues sometime in the future. I'll see what I can do. Any excuse I can fine to get away from working on my taxes.

R

I cleaned up the code. Hope it still works, but you never know.

(* 
  Column A: URLs to images - some may be blank
  Column B: First name
  Column C: Last name
  
How get range works:
https://discussions.apple.com/thread/3053250

applescript conditional operators:
https://developer.apple.com/library/content/documentation/AppleScript/Conceptual/AppleScriptLangGuide/reference/ASLR_operators.html


It is easier to diagnose problems with debug information. I suggest adding 
log statements to your script to see what is going on.  Here is an example.

    Author: rccharles
    
    For testing, run in the Script Editor.
      1) Click on the Event Log tab to see the output from the log 
          statement
      2) Click on Run
      
 *)

-- This controls the maximum number of times we renamed a file to make it have a unique name.
set theFence to 50
-- extension
set theExtension to ".jpg"

tell application "Microsoft Excel"
    
    tell active sheet
        tell used range
            set RowCount to count of rows
        end tell
        log "RowCount is " & RowCount
    end tell -- active sheet    
end tell -- application    

repeat with theIncrementValue from 1 to RowCount
    tell application "Microsoft Excel"
        tell active sheet
            -- Read a row
            set theCells to "A" & theIncrementValue & ":C" & theIncrementValue
            log "theCells are " & theCells
            set theRow to get value of range theCells -- Note: get always return a list within a list
            set actualRow to item 1 of theRow
            log actualRow
            log class of actualRow
            set theURL to item 1 of actualRow
            log "theURL  is " & theURL
            set theFirstName to item 2 of actualRow
            log "theFirstName is " & theFirstName
            set theLastName to item 3 of actualRow
            log "theLastName is " & theLastName
        end tell -- active sheet
    end tell -- application    
    
    -- Empty rows exist in the input sheet.  Well, at leaset, the URL is empty. 
    if theURL is not "" then
        -- allow multiple versions of the file
        set fileFolder to path to desktop as text
        log "fileFolder is " & fileFolder
        set filePartName to theFirstName & " " & theLastName
        log "filePartName is " & filePartName
        set fileName to filePartName & theExtension
        log "fileName is " & fileName
        
        -- In case the file already exists, create new name via a count
        set fileCount to 0
        
        -- Spin until we find a name or hit the fence.
        repeat while fileExists(fileFolder & fileName)
            set fileCount to fileCount + 1
            set fileName to theFirstName & " " & theLastName & "#" & fileCount & theExtension
            if fileCount > theFence then
                -- Hit the fence
                display dialog "we have over " & theFence & " copies of " & fileFolder & fileName & " skipping. " giving up after 20
                exit repeat
            end if
        end repeat
        
        -- Retrieve the file from the internet and create with the name gotten from the excel sheet.
        -- We are within the bounds of the fence
        if fileCount ≤ theFence then
            try
                -- Convert to Unix form
                set unixPath to POSIX path of fileFolder & fileName
                log "unixPath = " & unixPath
                set quotedUnixPath to quoted form of unixPath
                log "quoted form is " & quotedUnixPath
                set toUnix to "curl " & theURL & " -o " & quotedUnixPath
                log "toUnix is " & toUnix
                -- Read and create the new file
                set fromUnix to do shell script toUnix
                log "fromUnix# is " & fromUnix
            on error errMsg
                log "ERROR: " & errMsg
                display dialog "Unable to read " & toUnix & " from the internet or create the new file. Because " & errMsg giving up after 20
            end try
            
            -- Just for debug.
            try
                set fromUnix to do shell script "ls -l  " & quotedUnixPath
                log "ls -l of " & quotedUnixPath & return & "  " & fromUnix
            on error errMsg
                log "ls -l error..." & errMsg
            end try
            
        end if -- we try to create a different file name
    end if -- have URL
    
end repeat -- draggin through excel then processing 



-- ---------------------------------------------------------------
-- Philop Regan  https://stackoverflow.com/questions/3469389/applescript-testing-for-file-existence
--
--  fileExists(theFile) 
--     theFile as a text which is a string
-- returns true or false
--
-- example
--    if fileExists(fileName) then
--       ...
--    end if
on fileExists(theFile)
    log "    theFile is " & theFile
    tell application "System Events"
        if exists file theFile then
            log "    file exists"
            return true
        else
            log "    not here"
            return false
        end if
    end tell
end fileExists

I'm almost down with add the code for #2. Should be a today.

R

Glad you find it useful. Post any questions you have and I'll provided info.

R

Can you send an example of how I would write this script to extract the content of Row two in separate excel files to combine them in to a multi rowed single excel file?