Put first two cells of Excel sheet into a two dimensional list

chan · April 5, 2020, 7:44pm

I am trying to get the first two cells of an Excel sheet into an array.
I want to loop thru the sheet instead of just using —set mynewArray to (value of range “A1:B123”)

My end game is to make this array a Master format to check many other excel sheets against to see if they retain the same format.
Im sending out a questionnaire to some employees on Excel sheets, when they send them back
I´ll use the Master format array to check them to see if the format is the same as the MASTER Sheet I originally sent them.
I will then run a script on the returned sheets, if the format is the same, to “grade and categorize “the answers.

Loop thru sheet is working ok!
Gets question_num and question_text correctly!

The problem is the array is not being populated!

Im not versed in applescript, but have unsuccessfully looked for a solution.

Thanks for your help!


use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

set Row1 to {}
set Row2 to {}
set mynewArray to {{Row1, Row2}} --trying to make two dimension array



tell application "Microsoft Excel"
	tell active sheet
		
		try
			
			set my_file to POSIX file "/Users/chanxxx/Documents/Cuestionario de la Guia de Referencia II Nom-035 -3.xlsx"
			open my_file
			delay 5
			
			--------------  START LOOP THRU EXCEL TO PUT FIRST TWO CELLS OF EACH ROW IN AN ARRAY--------
			
			------------- This section creates and populates the array from Excel sheet --------
			
			repeat with i from 1 to 123 -- stepping forward starting at the top row 
				
				
				set question_num to value of cell ("A" & i) as string -- sets the value of the current cell to the first cell of the current row , a number
				set question_text to value of cell ("B" & i) as string -- sets the value of the current cell to the second cell of the current row , a text
				
				if question_num is not equal to "" then -- only gets non empty rows of sheet
					
					------- loop thru columns here ---------------------------	
					repeat with z from 1 to 2 --num of columns
						set item z of (item i of mynewArray) to question_num
						set item z of (item i of mynewArray) to question_text
					end repeat
					
					
					
				end if
			end repeat
		end try
	end tell
end tell

Model: macbook pro
Browser: Safari 604.3.5
Operating System: macOS 10.14

roosterboy · April 5, 2020, 8:51pm

Try something like this:


use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

-- classes, constants, and enums used
property NSArray : a reference to current application's NSArray
property NSPredicate : a reference to current application's NSPredicate

to getExcelDataFromRange:rangeAddress filteringEmpties:doFilter
	
	set returnList to {}
	
	try
		tell application "Microsoft Excel"
			
			tell active sheet
				set testRange to range (rangeAddress)
				set numCols to count of columns of testRange
				set returnList to (string value of testRange)
			end tell
			
		end tell
		
		if class of returnList is string then set returnList to {{returnList}}
		
		if doFilter then
			set testArray to {}
			repeat with i from 1 to numCols
				set end of testArray to ""
			end repeat
			
			set xlDataArray to NSArray's arrayWithArray:returnList
			set filterPred to NSPredicate's predicateWithFormat:"self != %@" argumentArray:{testArray}
			set returnList to (xlDataArray's filteredArrayUsingPredicate:filterPred) as list
		end if
	end try
	
	return returnList
	
end getExcelDataFromRange:filteringEmpties:

on run
	
	set filteredData to my getExcelDataFromRange:"$A$1:$B$123" filteringEmpties:true
	
end run

Oh, some explanation…

We grab the entire Excel range as a nested list in one swoop using string value rather than loop over the range and repeatedly hit up Excel for the values. It’s faster to do it that way, especially if you are working with a large range.

Note: If the range is just a single cell then string value returns, naturally, a string, so in that case we have to convert it to a nested list.

Then we can use Foundation methods to filter out any empty data if we want to.

Model: MacBook Pro (15-inch, 2016)
AppleScript: 2.7
Browser: Safari 605.1.15
Operating System: macOS 10.14

KniazidisR · April 6, 2020, 6:39am

It is simple:


set my_file to POSIX file "/Users/chanxxx/Documents/Cuestionario de la Guia de Referencia II Nom-035 -3.xlsx"
set mynewArray to {} --trying to make two dimension array

tell application "Microsoft Excel" to tell active sheet
	try
		open my_file
		delay 5
		
		--------------  START LOOP THRU EXCEL TO PUT FIRST TWO CELLS OF EACH ROW IN AN ARRAY--------
		
		------------- This section creates and populates the array from Excel sheet --------
		repeat with i from 1 to 123 -- stepping forward starting at the top row 
			
			set question_num to value of cell ("A" & i) as string -- sets the value of the current cell to the first cell of the current row , a number

			set question_text to value of cell ("B" & i) as string -- sets the value of the current cell to the second cell of the current row , a text
			
			if question_num is not equal to "" then -- only gets non empty rows of sheet
				set end of mynewArray to {question_num, question_text}
			end if
			
		end repeat
	end try
end tell

return mynewArray

Simple example to understand:

set anArray to {}

repeat with i from 1 to 5
	set end of anArray to {i, i * i} -- i and i*i table creating
end repeat

return anArray

→ RESULT: {{1, 1}, {2, 4}, {3, 9}, {4, 16}, {5, 25}}

Multiplication table:


set anArray to {}

repeat with i from 1 to 9
	repeat with j from 1 to 9
		set theAction to "" & i & "*" & j & "="
		set theResult to i * j
		set end of anArray to {theAction, theResult} -- Multiplication table creating
	end repeat -- j
end repeat -- i

return anArray

→ RESULT: {{“11=", 1}, {"12=”, 2}, {“13=", 3}, {"14=”, 4}, {“15=", 5}, {"16=”, 6}, {“17=", 7}, {"18=”, 8}, {“19=", 9}, {"21=”, 2}, {“22=", 4}, {"23=”, 6}, {“24=", 8}, {"25=”, 10}, {“26=", 12}, {"27=”, 14}, {“28=", 16}, {"29=”, 18}, {“31=", 3}, {"32=”, 6}, {“33=", 9}, {"34=”, 12}, {“35=", 15}, {"36=”, 18}, {“37=", 21}, {"38=”, 24}, {“39=", 27}, {"41=”, 4}, {“42=", 8}, {"43=”, 12}, {“44=", 16}, {"45=”, 20}, {“46=", 24}, {"47=”, 28}, {“48=", 32}, {"49=”, 36}, {“51=", 5}, {"52=”, 10}, {“53=", 15}, {"54=”, 20}, {“55=", 25}, {"56=”, 30}, {“57=", 35}, {"58=”, 40}, {“59=", 45}, {"61=”, 6}, {“62=", 12}, {"63=”, 18}, {“64=", 24}, {"65=”, 30}, {“66=", 36}, {"67=”, 42}, {“68=", 48}, {"69=”, 54}, {“71=", 7}, {"72=”, 14}, {“73=", 21}, {"74=”, 28}, {“75=", 35}, {"76=”, 42}, {“77=", 49}, {"78=”, 56}, {“79=", 63}, {"81=”, 8}, {“82=", 16}, {"83=”, 24}, {“84=", 32}, {"85=”, 40}, {“86=", 48}, {"87=”, 56}, {“88=", 64}, {"89=”, 72}, {“91=", 9}, {"92=”, 18}, {“93=", 27}, {"94=”, 36}, {“95=", 45}, {"96=”, 54}, {“97=", 63}, {"98=”, 72}, {“9*9=”, 81}}

chan · April 6, 2020, 5:11pm

Thanks to all for the help!

I´m excited to finish my project with the help you’ve given me