Pasting Excel data to webform

Feanux · December 15, 2020, 8:41pm

I’ve got a large amount of data in Excel that I need to paste into a webform, hit the “send” button, wait for the page to reload, and continue where it left off.

My Excel data is 7 columns, roughly 500 rows.

I tried to follow a similar script by Craig Smith where another user us asking for similar help but I’m getting stuck. Here’s the post: https://macscripter.net/viewtopic.php?id=20318

I used Craigs script to put the data in to an array/list, but I’m not sure how to insert that data into the form.

set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
	repeat with r_row from 2 to 3
		repeat with c_col from 1 to 7
			copy value of cell c_col of row r_row of active sheet to the end of dummy
		end repeat
		set end of master_data to dummy
		set dummy to {}
	end repeat
end tell
master_data

The webform has the following html boxes, if we’re using javascript their ID is the same as the Name
-foodColor
-foodSender
-foodReceiver
-foodValue
-foodAmount
-foodName
-foodShape

The Excel sheet I have contains those 7 items listed as data in the columns, so something like:

[format]
Color Sender Receiver Value Amount Name Shape

Red Bill Jim 100 40 Apples Round

Blue James Mike 400 10 Berries Oval

Green Pat Sam 130 5 Grapes Round
[/format]

I just need to paste each Excel column to its corresponding webform field, hit a “Send” button, and give it a minute to send, then move on to the next row of data.

I don’t know if there is a better way to do it other than an array, any help is much appreciated.

KniazidisR · December 16, 2020, 12:37pm

The post you reffer is related to reading and parsing the webpage content, and not to sending your data to webpage. For you task you should use GUI scripting the dialog of webform.

To find what is text field’s names and how to enter the data, use Automator’s Record button. Fill manually the webform while recording is on. Then see at what wrote the Automator for your actions.

To find how to open new empty webform do the same: use Automator’s Record button. For example, 1) set Automator’s Record on 2) click “Login” link of this MacScripter webpage 3) stop Record of Automator 4) See at what wrote the Automator for your action (click).

See here what wrote the Automator for me, when i clicked the “Login” link of this page:

“click static text 1 of UI Element "Login" of group 7 of group 2 of UI Element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1 of window "MacScripter / Pasting Excel data to webform" of application process "Safari"”

Now, I know: I can do something like this (the username and password are pseudo):


tell application "Safari"
	open location "https://macscripter.net/viewtopic.php?id=48118"
	delay 5 -- wait the loading
	activate
end tell

-- open the webform
tell application "System Events" to tell process "Safari" to tell window 1
	click static text 1 of UI element "Login" of group 7 of group 2 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1
end tell

-- Now, filling the form. The last Return sends the filled webform
tell application "System Events" to keystroke "theUserName" & return & "thePassword" & return & return

NOTE: for each row, whole the code above should be putted in the repeat loop (with rows)

Feanux · December 16, 2020, 1:16pm

KniazidisR:

Feanux:

I tried to follow a similar script by Craig Smith where another user us asking for similar help but I’m getting stuck. Here’s the post: https://macscripter.net/viewtopic.php?id=20318

I used Craigs script to put the data in to an array/list, but I’m not sure how to insert that data into the form.

The post you reffer is related to reading and parsing the webpage content, and not to sending your data to webpage. For you task you should use GUI scripting the dialog of webform.

To find what is text field’s names and how to enter the data, use Automator’s Record button. Fill manually the webform while recording is on. Then see at what wrote the Automator for your actions.

To find how to open new empty webform do the same: use Automator’s Record button. For example, 1) set Automator’s Record on 2) click “Login” link of this MacScripter webpage 3) stop Record of Automator 4) See at what wrote the Automator for your action (click).

Thanks for the help! I figured out how to enter the data but I’m not sure how to loop it. Any ideas?

set {foodColor, foodSender, foodReceiver, foodValue, foodAmount, foodName, foodShape} to getData()

repeat with i from 1 to count foodColor
	tell application "Safari"
		activate
		do JavaScript "document.getElementById('foodColor').value= '" & foodColor& "';" in document 1
		do JavaScript "document.getElementById('foodSender').value= '" & foodSender& "';" in document 1
		do JavaScript "document.getElementById('foodReceiver').value= '" & foodReceiver& "';" in document 1
		do JavaScript "document.getElementById('AddValue').value='Yes';" in document 1
		do JavaScript "document.getElementById('foodValue').value= '" & foodValue& "';" in document 1
		do JavaScript "document.getElementById('foodAmount').value= '1';" in document 1
		do JavaScript "document.getElementsByName('foodName')[0].value= '" & foodName& "';" in document 1
		do JavaScript "document.getElementsByName('foodShape')[0].value= '" & foodShape& "';" in document 1

As for getting the data from Excel, it looks like I can do either method, not sure which is best.

Method 1

set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
   repeat with r_row from 2 to 3
       repeat with c_col from 1 to 7
           copy value of cell c_col of row r_row of active sheet to the end of dummy
       end repeat
       set end of master_data to dummy
       set dummy to {}
   end repeat
end tell
master_data

Method 2

on getData() -- this part grabs excel data, I could use the 
	set colA to {}
	set colB to {}
	set colC to {}
	set colD to {}
	set colE to {}
	set colF to {}
	set colG to {}
	tell application "Microsoft Excel"
		
		activate
		tell active sheet
			set lastRow to first row index of (get end (last cell of column 1) direction toward the top)
			
			repeat with i from 2 to lastRow
				set end of colA to (value of range ("A" & i))
				set end of colB to (value of range ("B" & i))
				set end of colC to (value of range ("C" & i))
				set end of colD to (value of range ("D" & i))
				set end of colE to (value of range ("E" & i))
				set end of colF to (value of range ("F" & i))
				set end of colG to (value of range ("G" & i))
			end repeat
		end tell
	end tell
	
	return {colA, colB, colC, colD, colE, colF, colG}
end getData

I’m not sure which method would be best and I’m not sure how to loop it.

KniazidisR · December 16, 2020, 1:58pm

I would build the text for each row as a whole, to keystroke it then with System Events. Here’s the pseudocode (as I don’t know your webpage):

set formDataList to {}

tell application "Microsoft Excel" to tell active workbook to tell active sheet
	repeat with rowIndex from 2 to 3
		set theText to (value of cell ("A" & i)) & return
		set theText to theText & (value of cell ("A" & i)) & return
		set theText to theText & (value of cell ("B" & i)) & return
		set theText to theText & (value of cell ("C" & i)) & return
		set theText to theText & (value of cell ("D" & i)) & return
		set theText to theText & (value of cell ("E" & i)) & return
		set theText to theText & (value of cell ("F" & i)) & return
		set theText to theText & (value of cell ("G" & i)) & return
		set end of formDataList to theText
	end repeat
end tell -- formDataList is now ready

-- now go to website
repeat with formData in formDataList
	tell application "Safari"
		open location "https://macscripter.net/viewtopic.php?id=48118"
		delay 5 -- wait the loading
		activate
	end tell
	
	-- open the webform
	tell application "System Events" to tell process "Safari" to tell window 1
		click static text 1 of UI element "Login" of group 7 of group 2 of UI element 1 of scroll area 1 of group 1 of group 1 of tab group 1 of splitter group 1
	end tell
	
	-- Now, filling the form. The last Return sends the filled webform
	tell application "System Events" to keystroke formData & return
end repeat

Feanux · December 16, 2020, 2:22pm

Thanks for the Help KniazidisR.

When running the script, it gives me the following error: [format]error “The variable i is not defined.” number -2753 from “i” [/format]

It’s referencing the first “set TheText to” call, specifically (“A” & i) but I assume it will fail with the other columns referenced as well.

Also, for the form fill, I’ve already got it working with the javascript calls:

tell application "Safari"
       activate
       do JavaScript "document.getElementById('foodColor').value= '" & foodColor& "';" in document 1
       do JavaScript "document.getElementById('foodSender').value= '" & foodSender& "';" in document 1
       do JavaScript "document.getElementById('foodReceiver').value= '" & foodReceiver& "';" in document 1
       do JavaScript "document.getElementById('AddValue').value='Yes';" in document 1
       do JavaScript "document.getElementById('foodValue').value= '" & foodValue& "';" in document 1
       do JavaScript "document.getElementById('foodAmount').value= '1';" in document 1
       do JavaScript "document.getElementsByName('foodName')[0].value= '" & foodName& "';" in document 1
       do JavaScript "document.getElementsByName('foodShape')[0].value= '" & foodShape& "';" in document 1

so I’m not sure if the UI scripting is necessary for putting the data in, but if it is I can definitely mess around with it to fit it in.

Feanux · December 16, 2020, 8:10pm

I’m definitely dense, I can’t seem to figure out why it isn’t working. I think I’m combining too many parts of different scripts. Here’s what I have so far:

---GET EXCEL DATA--
on getData()
	set colA to {}
	set colB to {}
	set colC to {}
	set colD to {}
	set colE to {}
	set colF to {}
	set colG to {}
	tell application "Microsoft Excel"
		
		activate
		tell active sheet
			set lastRow to first row index of (get end (last cell of column 1) direction toward the top)
			
			repeat with i from 2 to lastRow
				set end of colA to (value of range ("A" & i))
				set end of colB to (value of range ("B" & i))
				set end of colC to (value of range ("C" & i))
				set end of colD to (value of range ("D" & i))
				set end of colE to (value of range ("E" & i))
				set end of colF to (value of range ("F" & i))
				set end of colG to (value of range ("G" & i))
			end repeat
		end tell
	end tell
	
	return {colA, colB, colC, colD, colE, colF, colG}
end getData

--SET VARIABLE--

set theData to {foodColor, foodSender, foodReceiver, foodValue, foodAmount, foodName, foodShape}

---PASTES DATA TO THE WEBFORM---
repeat with i from 1 to count theData
	tell application "Safari"
		activate
		do JavaScript "document.getElementById('foodColor').value= '" & foodColor & "';" in document 1
do JavaScript "document.getElementById('foodSender').value= '" & foodSender & "';" in document 1
do JavaScript "document.getElementById('foodReceiver').value= '" & foodReceiver & "';" in document 1
do JavaScript "document.getElementById('AddValue').value='Yes';" in document 1
do JavaScript "document.getElementById('foodValue').value= '" & foodValue & "';" in document 1
do JavaScript "document.getElementById('foodAmount').value= '1';" in document 1
do JavaScript "document.getElementsByName('foodName')[0].value= '" & foodName & "';" in document 1
do JavaScript "document.getElementsByName('foodShape')[0].value= '" & foodShape & "';" in document 1
	end tell
end repeat

Running this script works, but it only enters the first row of data, I can’t seem to make it loop to the next set of data after waiting ~5 seconds for the page to reload.

KniazidisR · December 17, 2020, 8:41am

It is very difficult to help without knowing about a specific site. Your last script clarifies something. Now I understand that your site allows you to fill a form using Javascript.

It is not yet clear whether the empty form appears on its own after sending the next data set, or if you have to open the empty webform yourself every time.

Let’s say that the empty form appears on its own. Then, I could make some necessary changes to your last script:


---GET EXCEL DATA--
on getData()
	set colA to {}
	set colB to {}
	set colC to {}
	set colD to {}
	set colE to {}
	set colF to {}
	set colG to {}
	tell application "Microsoft Excel"
		
		activate
		tell active sheet
			set lastRow to first row index of (get end (last cell of column 1) direction toward the top)
			
			repeat with i from 2 to lastRow
				set end of colA to (value of range ("A" & i))
				set end of colB to (value of range ("B" & i))
				set end of colC to (value of range ("C" & i))
				set end of colD to (value of range ("D" & i))
				set end of colE to (value of range ("E" & i))
				set end of colF to (value of range ("F" & i))
				set end of colG to (value of range ("G" & i))
			end repeat
		end tell
	end tell
	
	return {colA, colB, colC, colD, colE, colF, colG}
end getData

--SET VARIABLE--

set {foodColor, foodSender, foodReceiver, foodValue, foodAmount, foodName, foodShape} to getData() -- EDITED

---PASTES DATA TO THE WEBFORM---
repeat with i from 1 to lastRow - 1 -- EDITED
	tell application "Safari"
		activate
		do JavaScript "document.getElementById('foodColor').value= '" & (item i of foodColor) & "';" in document 1 -- EDITED
		do JavaScript "document.getElementById('foodSender').value= '" & (item i of foodSender) & "';" in document 1 -- EDITED
		do JavaScript "document.getElementById('foodReceiver').value= '" & (item i of foodReceiver) & "';" in document 1 -- EDITED
		do JavaScript "document.getElementById('AddValue').value='Yes';" in document 1
		do JavaScript "document.getElementById('foodValue').value= '" & (item i of foodValue) & "';" in document 1 -- EDITED
		do JavaScript "document.getElementById('foodAmount').value= '1';" in document 1
		do JavaScript "document.getElementsByName('foodName')[0].value= '" & (item i of foodName) & "';" in document 1 -- EDITED
		do JavaScript "document.getElementsByName('foodShape')[0].value= '" & (item i of foodShape) & "';" in document 1 -- EDITED
	end tell
end repeat

NOTE: how you send the filled form is unclear too. You click some “Send” button using JavaScript?

Feanux · December 17, 2020, 5:08pm

Thanks KniazidisR, that was the correct guess and it appears to be working as needed! I really appreciate the help, it cleared a lot up and I learned quite a bit.