How do you iterate data from Excel to Chrome?

Scripting Forums AppleScript | Mac OS X
#1

Hello,

I’m VERY new to scripting, so please bear with me. I’m trying to set data (i.e. names and the number of names) from an Excel spreadsheet to the clipboard, which can then be used as variables to tell Google Chrome how to GUI script a website.

Ideally, the name variable would be able to iterate the names in the spreadsheet to perform the “keystroke” command, and the number variable would be able to perform the “repeat" command by the number of names in the spreadsheet–hopefully that makes sense :slight_smile:

Here’s a simple example:

Excel Spreadsheet:

Column A

Dave
Josh
Ben
Greg
Chris

“Dave" would then be keystroked into a website, followed by “Josh”, etc. The process would be repeated five times (i.e. the number of names in column A).

The script is very much a work in progress, so please forgive the incompleteness of it, but any help in piecing it together would be much appreciated.

tell application “Microsoft Excel”
activate
open “Excel spreadsheet filepath” – to be filled in
tell active sheet
set CollectiveArtists to value of used range
set IndividualArtist to paragraphs of CollectiveArtists
set NumberofArtists to count lists of CollectiveArtists
set value of IndividualArtist to the clipboard – this is where the script seems to fail.

end tell

end tell

set VarNum to NumberofArtists
set VarName to IndividualArtist – is this where iterate with each name should be?

tell application “Google Chrome”
activate
open location “Website URL” – to be filled in
delay 5
tell application “System Events”
repeat VarNum times – this number should equal the number of rows in the spreadsheet
key code 3 using command down
delay 0.1
keystroke “show advanced”
delay 0.1
key code 53
delay 0.1
key code 36
delay 0.25
key code 48
delay 0.25
key code 48
delay 0.25
key code 48
delay 0.25
keystroke VarName – this is where the names from the spreadsheet should be entered
key code 3 using command down
delay 0.1
keystroke “preview report”
delay 0.1
key code 53
delay 0.1
key code 36
end repeat
end tell
end tell

AppleScript: 2.11
Browser: Safari 537.36
Operating System: macOS 10.14

#2

If I understand well the code below would do the job.

tell application "Microsoft Excel"
	activate
	open "Excel spreadsheet filepath" -- to be filled in
	tell active sheet
		set CollectiveArtists to value of used range -- grab the values from the range
		set the clipboard to CollectiveArtists -- fill the clipboard with them
	end tell
end tell

tell application "Google Chrome"
	activate
	open location "Website URL" -- to be filled in
	delay 5
end tell
tell application "System Events"
	tell process "Googgle Chrome" -- I don't use it so, check the name of the process
		set frontmost to true
		repeat with VarName in IndividualArtist -- loop treating one name at a time
			key code 3 using {command down]
			delay 0.1
			keystroke "show advanced"
			delay 0.1
			key code 53
			delay 0.1
			key code 36
			delay 0.25
			key code 48
			delay 0.25
			key code 48
			delay 0.25
			key code 48
			delay 0.25
			keystroke VarName
			key code 3 using {command down]
			delay 0.1
			keystroke "preview report"
			delay 0.1
			key code 53
			delay 0.1
			key code 36
		end repeat
	end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) jeudi 23 janvier 2020 22:15:42

#3

Yvan, thank you SO much! Your reply was very helpful.

The script now works :slight_smile:

This is a great community and resource.