6 Replies Latest reply: Oct 29, 2013 1:32 PM by Nat Kuhn
Kozure OOkami Level 1 Level 1 (0 points)
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)
  • 1. Re: Using Applescript to Copy a Table Across Sheets
    Level 8 Level 8 (41,760 points)
    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
  • 2. Re: Using Applescript to Copy a Table Across Sheets
    Kozure OOkami Level 1 Level 1 (0 points)
    Thanks! I tried that and I can copy values that are numbers of if the values are dates I get an error: "Numbers got an error: AppleEvent handler failed.” Do you have any idea why?
  • 3. Re: Using Applescript to Copy a Table Across Sheets
    Kozure OOkami Level 1 Level 1 (0 points)
    it seems that copying using

    set TDate to value of cell 1 of row j as string

    and pasting using

    set value of cell 1 of row (j - 4) to TDate

    works except all the dates are wrong (they are shifted by 1 day) when pasted...
  • 4. Re: Using Applescript to Copy a Table Across Sheets
    Level 8 Level 8 (41,760 points)
    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
  • 5. Re: Using Applescript to Copy a Table Across Sheets
    AlekThunder Level 1 Level 1 (0 points)

    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

  • 6. Re: Using Applescript to Copy a Table Across Sheets
    Nat Kuhn Level 1 Level 1 (0 points)

    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.