Applescript to read spreadsheet and fill out web form

README.md

This Applescript will help you quickly import a spreadsheet of emails and names into any web form. This one is specifically designed to work with a custom King Sumo giveaway, and a spreadsheet of leads downloaded from Facebook lead generator. We are only importing name (column L) and email (column K).

Notes for Future Development / Calls for Help:

• I would LOVE to replace the key code navigation with javascript to fill out the fields in the web form.
• I think this would also run faster if I could store the names & emails in an array and therefore eliminate the need to switch back to Excel once they are grabbed. (the new algo would be: grab names & emails from spreadsheet, store as an array; REPEAT until end of array: open URL in incognito window; fill out form with first stored set of values; hit enter; close window; END REPEAT)

Some of the techniques contained in this script include:

1. Repeating a script for each row of an Excel sheet that contains data (stop when you reach the end of the data)
2. Use Key Codes to navigate a spreadsheet and a web page
3. Use key codes to copy and paste data
4. Switch between applications
5. Open a URL using Incognito Mode in Google Chrome
6. Close all Google Chrome windows

Check out the in-line comments for options to customize.

To Run:

• Open the Applescript
• Open your spreadsheet containing the names & emails you want to import.
• Make sure that the first NAME is selected from the first row of data (row 2, column L in this example, usually).
• Replace the URL in line 23 with that of your form entry page.
• CLOSE ALL GOOGLE CHROME WINDOWS!! This is important.

Contest Entry Script v2.scpt

-- This Applescript will help you quickly import a spreadsheet of emails and names into your KingSumo contest entry. Follow the instructions below to run. Check out the in-line comments for options to customize.
-- Open your spreadsheet containing the names & emails you want to import. Make sure that the first NAME is selected from the first row of data (row 2, usually).
-- CLOSE ALL GOOGLE CHROME WINDOWS!! This is important.

-- Repeat & if statements tell the script to keep running until the end of your spreadsheet.
tell application "Microsoft Excel"
	repeat with x from 2 to (count rows of active sheet)
		if value of cell 1 of row x = "" then exit repeat
		
		tell application "Microsoft Excel"
			activate
			--Get the first name	
			tell application "System Events"
				key code 8 using {command down} -- command-c, copy active cell
				delay 0.05
			end tell
		end tell
		
		--Open new Chrome Incognito tab
		tell application "/Applications/Google Chrome.app"
			make new window with properties {mode:"incognito"}
			activate
			set URL of active tab of first window to "http://example.test/form/123" -- REPLACE with URL of the active contest that you are going to import your entries into.
			delay 4
			tell application "System Events"
				repeat 4 times
					key code 48 -- tab key cycles through the links on your contest entry page until you get to the entry form.
					delay 0.05
				end repeat
				delay 1
				repeat 4 times
					key code 125 -- down arrow cycles through responses to "are you human?" question
					delay 0.05
				end repeat
				key code 36 -- enter key to select
				delay 1
				key code 48 -- tab key to move to "name" field
				delay 0.1
				key code 9 using {command down} -- command-v to past the name
				delay 0.05
			end tell
		end tell
		
		
		tell application "Microsoft Excel"
			activate
			tell application "System Events"
				key code 123 -- switch back to MS Excel and move the active cell left to get email address
				delay 0.05
				key code 8 using {command down} -- copy the email address
				delay 0.05
			end tell
		end tell
		
		tell application "Google Chrome"
			activate
			tell application "System Events"
				key code 48 -- switch to Chrome and hit tab to move to "email" field
				delay 0.5
				key code 9 using {command down} -- paste email
				delay 0.5
				key code 48 -- tab to "enter" button
				delay 0.1
				key code 36 -- enter
				delay 3
			end tell
			
			-- Close all Chrome windows
			set windowList to every tab of every window
			repeat with tabList in windowList
				set tabList to tabList as any
				repeat with tabItr in tabList
					set tabItr to tabItr as any
					delete tabItr
				end repeat
			end repeat
		end tell
		
		tell application "Microsoft Excel"
			activate
			tell application "System Events"
				key code 124 -- activate Excel and move right one cell and down one cell
				delay 0.05
				key code 125
				delay 0.05
			end tell
		end tell
	end repeat
end tell