Passing values from Excel to the web via AppleScript

Jun 23, '04 09:01:00AM

Contributed by: roncross@cox.net

It took me some time to figure out how to pass a variable from an Excel table to a form on the web using a do Javascript in Safari, but I finally did figure it out sometime ago and would like to share it with the group. I will not post the entire script that I use, but just provide clues on how to do it. Here are the clues.

Read the rest of the hint for some tips on making this work...

  1. set the variable this_script2 to part of the string for the do Javascript command that relates to the form or document that you want to populate. The command usually takes the form of:
    do Javascript "document.forms[0].elements[2].value='johndoe'"
    set this_script2 to "document.forms[0].elements[2].value="
    
  2. j is a variable such as a column in the Excel table; the row is defined elsewhere in the script. In this case, j is column 1 of the Excel table. The variables myfield and my_script are set to j and this_script2, respectively, to be passed through the subroutine onetab. mycell is also passed through the subroutine from the Excel value that is pulled from the column and row of the Excel table -- in this case, johndoe.
    
    if j is equal to 1 then
      set myfield to j
      set my_script to this_script2
      my onetab(myfield, my_cell, my_script)
    end if
  3. onetab subroutine. Please note that my_cell passes as id_cell and my_script passes as the_script in the onetab subroutine. myfield passes as thefield in onetab subroutine. The important thing to note is the code after the else statement. From this point on, it is just concatenation. id_cell is set to thecell; notice the SINGLE QUOTES. The variable the_script and thecell is set to thevalue through concatenation with the & symbol. The variable thevalue is then passed through the do Javascript command to populate the text field on the web page. The do JavaScript command actually looks like the following in long form:
    
    do JavaScript "document.forms[0].elements[2].value='johndoe'"
    
    Johndoe comes from my_cell in the Excel table listed above. It is passed into id_cell in the subroutine.
    on onetab(thefield, ld_cell, the_script)
      if thefield is equal to 1 then
        if page_loaded(60) is false then
          display dialog "the window did not load completely"
        else
          set thecell to "'" & (ld_cell as string) & "'"
          set thevalue to the_script & (thecell as text)
          tell application "Safari"
            do JavaScript thevalue in document 1
          end tell
        end if
      end if
    end onetab
That's it. It is pretty simple once you figure it all out. I am not a programmer, but I like the construction of AppleScript in this case. This script is very fast and efficient. You can use it with GUI scripting, but it is a lot slower and prone to mistakes. For example, using the do Javascript command takes roughly one to two seconds to fill out 10 text fields on a form, while the GUI command will take roughly 20 seconds. I am sure that there are other ways to do the same thing, but this just points out the power of Applescript capabilities.

Comments (2)


Mac OS X Hints
http://hints.macworld.com/article.php?story=20040325235627468