Thursday, July 9, 2020

#1 2020-04-05 01:44:05 pm

chan
Member
Registered: 2016-01-17
Posts: 9

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

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!

Applescript:


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

Offline

 

#2 2020-04-05 02:51:55 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 14

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

Try something like this:

Applescript:


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

Last edited by roosterboy (2020-04-05 03:02:54 pm)

Offline

 

#3 2020-04-06 12:39:54 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 1259

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

It is simple:

Applescript:


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:

Applescript:

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:

Applescript:


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:  {{"1*1=", 1}, {"1*2=", 2}, {"1*3=", 3}, {"1*4=", 4}, {"1*5=", 5}, {"1*6=", 6}, {"1*7=", 7}, {"1*8=", 8}, {"1*9=", 9}, {"2*1=", 2}, {"2*2=", 4}, {"2*3=", 6}, {"2*4=", 8}, {"2*5=", 10}, {"2*6=", 12}, {"2*7=", 14}, {"2*8=", 16}, {"2*9=", 18}, {"3*1=", 3}, {"3*2=", 6}, {"3*3=", 9}, {"3*4=", 12}, {"3*5=", 15}, {"3*6=", 18}, {"3*7=", 21}, {"3*8=", 24}, {"3*9=", 27}, {"4*1=", 4}, {"4*2=", 8}, {"4*3=", 12}, {"4*4=", 16}, {"4*5=", 20}, {"4*6=", 24}, {"4*7=", 28}, {"4*8=", 32}, {"4*9=", 36}, {"5*1=", 5}, {"5*2=", 10}, {"5*3=", 15}, {"5*4=", 20}, {"5*5=", 25}, {"5*6=", 30}, {"5*7=", 35}, {"5*8=", 40}, {"5*9=", 45}, {"6*1=", 6}, {"6*2=", 12}, {"6*3=", 18}, {"6*4=", 24}, {"6*5=", 30}, {"6*6=", 36}, {"6*7=", 42}, {"6*8=", 48}, {"6*9=", 54}, {"7*1=", 7}, {"7*2=", 14}, {"7*3=", 21}, {"7*4=", 28}, {"7*5=", 35}, {"7*6=", 42}, {"7*7=", 49}, {"7*8=", 56}, {"7*9=", 63}, {"8*1=", 8}, {"8*2=", 16}, {"8*3=", 24}, {"8*4=", 32}, {"8*5=", 40}, {"8*6=", 48}, {"8*7=", 56}, {"8*8=", 64}, {"8*9=", 72}, {"9*1=", 9}, {"9*2=", 18}, {"9*3=", 27}, {"9*4=", 36}, {"9*5=", 45}, {"9*6=", 54}, {"9*7=", 63}, {"9*8=", 72}, {"9*9=", 81}}

Last edited by KniazidisR (2020-04-06 04:01:30 am)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#4 2020-04-06 11:11:48 am

chan
Member
Registered: 2016-01-17
Posts: 9

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

Thanks to all for the help!

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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)