0

I would like AppleScript to add a new row on a number or excel sheet (or on a SQL database)

I have my data saved on a .plist which I can retrieve with

set myDateFromPlist to value of property list item "myDate" of p_list

the idea is to create a database or sheet with all the variables in the same row (and add a new row with more data when running the script again

e.g

date |var1|var2|var3 date |var1|var2|var3

What I tried so far

tell application "Numbers"
    activate
    try
        if not (exists document 1) then error number 1000
        tell document 1
            try
                tell active sheet
                    set the selectedTable to ¬
                        (the first table whose class of selection range is range)
                    tell the selectedTable to add row below last row
                    tell the selectedTable to set value of first cell to myDateFromPlist
                    tell the selectedTable to set value of second cell to myTimeFromPlist
                    tell the selectedTable to set value of third cell to theIDFromPlist

                end tell
            on error
                error number 1001
            end try
            tell selectedTable
                -- editing statements go here
            end tell
        end tell

the issues with that :

1: numbers have to be open (but I guess that's might be a requirement anyway)

2: the data is always added to the 1st row

  • I just updated my script with my draft script – Kevin Mar 11 '18 at 8:54
1

Row Allocation for Data Entry

To get data added to a new row, you were on the right track with your first script where you wrote tell the selectedTable to add row below last row. You simply then had to reference this newly created row when setting the values of cell 1, cell 2, and cell 3, which were otherwise being referenced as belonging to the selectedTable instead of a specific row in the selectedTable:

    set dataToAdd to {¬
        myDateFromPlist, ¬
        myTimeFromPlist, ¬
        theIDFromPlist}

    tell application "Numbers" to tell ¬
        the front document to tell ¬
        the active sheet to tell ¬
        table 1

        set R to add row below last row

        tell R to ¬
            repeat with i from 1 to count dataToAdd

                set value of cell i to ¬
                    item i in dataToAdd

            end repeat
    end tell

I decided to wrap the setting of cell values inside a repeat loop, because—in theory—you could have 50 cells to assign values to, and this saves a bit of typing, plus make the code a bit easier to read.

Note, however, that cell i inside the repeat loop is, itself, part of a tell R statement, where R is the reference to the new row just created. This tells the script that it's cell i of this new row that should be assigned the values stored in dataToAdd, and not simply cell i of table 1 (I'm assuming your sheet only has one table, in which case there's no need to bother with the lengthy first table whose class of selection range is range. If, however, I've assumed wrongly, then it's still better to give your tables names, which you can then reference specifically from your script, rather than by way of a very tenuous hope that the selected table is always going to be the correct table).


Some Points About Your Question Content and Code Snippet

At the moment, this is the most assistance I can give.

If I might advise you, in posting a question, whilst it's great that you posted your code snippet, it's far more helpful to a) post a code snippet that compiles (yours does not; Script Editor reports an unexpected end of file when attempting to compile it, because your code snippet is incomplete); b) include enough of the code out of the greater whole such that it can be run on someone else's system, which means that variables and such should be defined within the code snippet—currently, I have no idea what the variables myDateFromPlist, myTimeFromPlist, and theIDFromPlist are or contain, so can't, for instance, help you with regards to why your RAM usage escalated.

I suspect there's a bunch of code that precedes this Numbers block, in which you've accessed your plist file to retrieve its data. It might be something in the way you've done this that has your memory being consumed.

As a final critique, whilst error-handling is a good idea, I would leave it out until your script is being finalised, because its current entrapment blocks include too many lines of code where different errors could be caught: for instance, if you receive an error code of 1001, it could be because there is no active sheet; or there is no selection range; or that something went wrong with assigning values to a cell, which could be cell 1, cell 2, or cell 3, but may have been a result of a problem with row 2 or one of the variables. Also, you have two try blocks nested inside one another, which is pretty cavalier.


Should You Want More Help...

If you're happy with the solution this answer provides to your issue of row allocation and data insertion, then I'm happy it was an easy fix. If you have any other queries about this, leave a comment and I'll get back to you. If you want to provide more information regarding your script, variables, and what not, we can try and suss out where this memory consumption is coming from. As you said in your comment "with this method though is my RAM is jumping out of the window", it would be helpful to know what was different between your first method and the one that generated the memory consumption, which may, of course, require you to post two scripts.

What you can do for the purposes of this is use an online paste bin. Pastery doesn't require usernames or passwords, and has AppleScript syntax highlighting, but it's one amongst many. Paste Bin doesn't require any login whatsoever. It's useful to use on Stack Exchange websites when you want/need to supply additional code that is requested specifically, but isn't actually needed for the main focus of the question itself where a code block was already supplied and supplying more would risk drowning the question in a sea of grey.

  • Thank you, this way more clean than my code, I will go through more in details asap but so far it's working great – Kevin Mar 12 '18 at 12:32
0

Probably not the best way but seems to work

    tell application "Numbers"
        activate
        try
            if not (exists document 1) then error number 1000
            tell document 1
                try
                    tell active sheet
                        set the selectedTable to ¬
                            (the first table whose class of selection range is range)
tell the selectedTable to set value of cell 1 of row 2 to myDateFromPlist

                    tell the selectedTable to set value of cell 2 of row 2 to myTimeFromPlist

                    tell the selectedTable to set value of cell 3 of row 2 to theIDFromPlist

                    end tell
                on error
                    error number 1001
                end try
                tell selectedTable
                    -- editing statements go here
                end tell
            end tell
  • Corrected, the issue with this method though is my RAM is jumping out of the window (2Gb+) – Kevin Mar 11 '18 at 14:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .