Apple numbers Filling data into a table using AppleScript

applescriptautomationnumbers

I have a document on numbers with 3 sheets.

1-data entry table (based on the "data" sheet).
2-copy dada table with:

— I.an additional column containing the following formula

        if(reference no of data entery=reference no of this row;1;0)

— II.filter table, greater than 0

3-data table

I want to use Applescript in order to fill the data from sheet 1 to sheet 2

tell application "Numbers" to ¬
     tell table 2 of sheet 1 of document (1)
          set getRef to the value of cell "A1"
          set fileinfolder to the value of cell "A2"
          set Outof to the value of cell "A3"
          set valqora to the value of cell "A9"
          set valqno to the value of cell "A10"
          set valQout to the value of cell "A11"
          set valcomment to the value of cell "A13"
          set valnoclue to the value of cell "A14"
          set valhard to the value of cell "A15"
          set valok to the value of cell "A16"
          set valeasy to the value of cell "A17"

end tell

tell application "Numbers" to ¬
    tell table 1 of sheet 3 of document (1)
        set the value of cell "K2" to the value of fileinfolder
        set the value of cell "L2" to the value of Outof
        set the value of cell "M2" to the value of valqora
        set the value of cell "N2" to the value of valqno
        set the value of cell "O2" to the value of valQout
        set the value of cell "P2" to the value of valcomment
        set the value of cell "Q2" to the value of valnoclue
        set the value of cell "R2" to the value of valhard
        set the value of cell "S2" to the value of valok
        set the value of cell "T2" to the value of valeasy

end tell

i keep recicing Syntax Error- Can’t get value of true

Best Answer

For your second block of assignments, remove the second 'the value of' from each line so that they look like this:

set the value of cell "K2" to fileinfolder

That second 'value' is redundant.

As an aside, the error is peculiar in that it should probably be a 'script error' that can't get the value of '1.0', for example. So whichever cell is generating the error is probably indicating to the script that it's a boolean or something. But this isn't the source of your problem, just a symptom.

NB To test this, I simply used a column of numbers ranging from 1 to 17 to supply the first block of assignments.

Related Solutions

Getting the First Cell NOT Containing Certain Text in Numbers

As it turns out, I've figured it out. It did require Applescript. What I did was loop through the rows and selected the values in the row where "not" was not present in column B. Here's the Applescript I used via Automator:

on run {input, parameters}

    tell application "Numbers"
        tell table "Calculator 1" of sheet 2 of document of front window
            set aRange to "A2:A22"
            set bRange to "B2:B22"
            set aRows to the rows of range aRange
            set bRows to the rows of range bRange

            set correctRow to -1
            set correctCol to -1

            repeat with i from 1 to count of bRows
                set aCell to "A" & (i + 1)
                set bCell to "B" & (i + 1)

                if value of (cell bCell) is not "not" then
                    set correctRow to value of (cell aCell)
                    set correctCol to value of (cell bCell)
                end if
            end repeat

            set the value of (cell "C2") to correctRow
            set the value of (cell "C4") to correctCol
        end tell

        display dialog "Updated!"
    end tell

    return input
end run

Keep in mind that the cells I used are specific to my spreadsheet, but generally this method could be applied for any 2D MATCH() function.

Create a table has the name of the each row in each row of the header column, and use the desired MATCH() function per row in this way (see the picture of the table in my question).
Use this Applescript to loop through the MATCH() functions and find the correct row and column. Change "C2" to whichever cell will be your Correct Row and "C4" to whichever cell will be your Correct Column.
Use the INDEX() function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.

AppleScript and Numbers: How to add a row

Instead of:

tell table "WorkoutDB" of sheet "WorkoutSheet"

Use:

tell table "WorkoutDB" of sheet "WorkoutSheet" of document 1

This way Numbers know which document to communicate with.

Best Answer

Related Solutions

Getting the First Cell NOT Containing Certain Text in Numbers

AppleScript and Numbers: How to add a row

Related Question