Skip navigation

Using Applescript to Copy a Table Across Sheets

1314 Views 6 Replies Latest reply: Oct 29, 2013 1:32 PM by Nat Kuhn RSS
Kozure OOkami Calculating status...
Currently Being Moderated
Mar 30, 2011 9:05 AM
Hi,

I am learning to use Applescript and I am not sure how to do the following:

Copy a cell value from one table in one sheet to another table in another sheet.

I tried the following:

tell sheet “B”
set value of cell 1 of row 1 of table “X” to (value of cell 1 of row 1 of table “X” of sheet “A”)
end tell

But that does not work.
Mac OS X (10.6.7)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Mar 30, 2011 10:00 AM (in response to Kozure OOkami)
    set value of cell 1 of row 1 of table “X” of sheet “B” to (value of cell 1 of row 1 of table “X” of sheet “A”)

    or

    set a_val to value of cell 1 of row 1 of table “X” of sheet “A”
    set value of cell 1 of row 1 of table “X” of sheet “B” to a_val

    or

    tell sheet "A" to tell table "X"
    set a_val to value of cell 1 of row 1
    end tell
    tell sheet "B" to tell table "X"
    set value of cell 1 of row 1 to a_val
    end tell

    The late one is ready to expand to the transfer of several cells.

    Yvan KOENIG (VALLAURIS, France) mercredi 30 mars 2011 19:00:36
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.6.7)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Mar 30, 2011 11:31 AM (in response to Kozure OOkami)
    I forgot to write that you must ALWAYS coerce values to strings.

    set value of cell 1 of row 1 of table “X” of sheet “B” to (value of cell 1 of row 1 of table “X” of sheet “A”) as text

    or

    set a_val to value of cell 1 of row 1 of table “X” of sheet “A”
    if class of a_val is date then set a_val to a_val - (time to GMT)
    set value of cell 1 of row 1 of table “X” of sheet “B” to a_val as text

    or

    tell sheet "A" to tell table "X"
    set a_val to value of cell 1 of row 1
    end tell
    tell sheet "B" to tell table "X"
    if class of a_val is date then set a_val to a_val - (time to GMT)
    set value of cell 1 of row 1 to a_val as text
    end tell

    It's necessary for :

    (1) decimal numbers because an Applescript's decimal number use always the period character as decimal separator. Coercing to string replace this standard character by the localized one which is the comma in several countries.

    (2) Numbers doesn't know the object date_time of Ap^pleScript. This is why you got the error : "Numbers got an error: AppleEvent handler failed.”

    Coercing the date to string (or text which is exactly the same task), you send to Numbers a known structure. As you saw, in the 2nd and 3rd pieces of code, I substract the value (time to GMT) when a value is a date_time one. It's required to take care of the Numbers internal behavior.

    Yvan KOENIG (VALLAURIS, France) mercredi 30 mars 2011 20:29:40
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.6.7)
  • AlekThunder Calculating status...
    Currently Being Moderated
    Jul 4, 2013 12:35 AM (in response to KOENIG Yvan)

    Thank you a lot for class date notice. I wish I ran across this much before. I spent huge time for nothing, trying to copy cell with date. I couldn't even imagine that guys from Apple refused to make friend their kids im more intellegent manner

  • Nat Kuhn Level 1 Level 1 (0 points)
    Currently Being Moderated
    Oct 29, 2013 1:32 PM (in response to KOENIG Yvan)

    Yvan-- Sie sind der KOENIG!  You are the KING!  Vous êtes le ROI!  You seem to solve all my Applescript problems!

     

    I am still having a little problem: my date is 12/31/13.  At that point, I will be 5 hours off GMT.  Right now I'm 4 hours off.  So a_val is 12/30/13 at 7PM (this is in the barbaric American system, i.e. 30-XII-13 1900), and a_val - (time to GMT) is 11PM on 12/30, not 0000 on 12/31.  I have, for the moment, just added 3600 (one hour's worth of seconds).  This is not the end of the world, because when the time changes it will come out as 1 am on 12/31/13 and my display will round it away.  Or I can write code to push whatever date down to 00h00.  But it is not very elegant!  Anyway, thank you very very much.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.