I’m inside a larger Applescript, retrieving daily information from a webpage and inputting into a Numbers page (call it #1) via copying and pastong the webpage into Textwrangler to make this a little simpler. Now I want to search a second Numbers (call it #2) sheet for a particular row in order to copy and paste that entire row into #1. I’ll be matching the A column for both Numbers sheets. To clarify, these are two sheets of the same file.
So I’m stumped on how to search numbers
. And how to search “contents of A3.”
Then how to copy that entire row.
Paste into row of #1.
Thanks so much! If I be any more clear feel free to ask.
I guess that what you call “sheets” are in fact “tables”.
The contents of a cell is extracted with :
tell application "Numbers"
tell document theDocName
tell sheet theSheetName to tell table theTableName
set aValue to value of cell rowNum of column colNum
end tell
end tell
end tell
in which
theDocName is the name of the document
theSheetName is the name of the sheet containing the source table named theTableName
rowNum is the index of the row whose value of column colNum is to be extracted.
Yvan KOENIG (VALLAURIS, France) dimanche 4 janvier 2015 23:13:58
That is great! But in order to know which row to be extracted I must locate it via a search.
So can I run a search in Numbers?
Can I do so with the contents of clipboard?
And once found, how can I get that row to be the row in your script for the extraction?
I’m not a soothsayer so I can’t guess where is the relevant data supposed to be stored.
I may imagine that it’s always stored in a given column but I am not sure of that.
It would also be useful to know the way the value to search is defined.
Define precisely your problem and the answer will be easier to give.
Here is a piece of code which make no assumption upon the location of the searched value in the table.
set theValue to "f12"
tell application "Numbers" to tell document "4see.numbers"
tell sheet "Feuille 1" to tell table "Tableau 1"
set theValues to value of every cell of every row
end tell
end tell
repeat with r from 1 to count theValues
set maybe to item r of theValues
set maybeText to my recolle(maybe, tab)
if maybeText contains theValue then
set theRow to r
repeat with j from 1 to count maybe
if item j of maybe contains theValue then
set theColumn to j
exit repeat
end if
end repeat
exit repeat
end if
end repeat
{theRow, theColumn, item theColumn of maybe}
--> {12, 6, "ff12"}
#=====
on recolle(l, d)
local oTIDs, t
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
set t to l as text
set AppleScript's text item delimiters to oTIDs
return t
end recolle
#=====
It assumes that the searched value may be a subset of the contents of a cell.
If the searched value must be the exact contents of a cell, the code may be reduced to
set theValue to "ff12"
tell application "Numbers" to tell document "4see.numbers"
tell sheet "Feuille 1" to tell table "Tableau 1"
set theValues to value of every cell of every row
end tell
end tell
repeat with r from 1 to count theValues
set maybe to item r of theValues
if maybe contains theValue then
set theRow to r
repeat with j from 1 to count maybe
if theValue = item j of maybe then
set theColumn to j
exit repeat
end if
end repeat
exit repeat
end if
end repeat
{theRow, theColumn, item theColumn of maybe}
--> {12, 6, "ff12"}
Yvan KOENIG (VALLAURIS, France) lundi 5 janvier 2015 11:01:22
Okay so that finds it the cell. But as I have already said, once found I need to select the row, then copy columns a:e of that row. Then paste onto another sheet (still columns a:e but row:4).
It seems that you didn’t read carefully what I wrote.
You wrote : To clarify, these are two sheets of the same file.
which makes no sense.
We can’t copy cells or row from a sheet.
Cells aren’t in sheets but in tables.
To give a complete answer, I must know if the source and target tables are in the same sheet.
I wish to add that copy and paste would require the use of GUI Scripting which is often said to be wrong scheme.
The clean protocol would be to extract the values then set the values in the other table.
Here I assume that the tables are in different sheets.
set theValue to "ff12"
tell application "Numbers" to tell document "4see.numbers"
tell sheet "Feuille 1" to tell table "Tableau 1"
set theValues to value of every cell of every row
repeat with r from 1 to count theValues
set maybe to item r of theValues
if maybe contains theValue then
set theRow to r
(*
repeat with j from 1 to count maybe
if theValue = item j of maybe then
set theColumn to j
exit repeat
end if
end repeat
*)
exit repeat
end if
end repeat
# Here we know the index of the source row
# Extract the values from column 1 thu 5, a.k.a. column "A" thru "E"
set theValues to value of cells 1 thru 5 of row theRow
end tell # Table "Tableau 1"
# Now jump to the destination table
tell sheet "Feuille 2" to tell table "Tableau destination"
tell row 4 # adjust the row num to fit your needs
repeat with i from 1 to count theValues
set value of cell i to item i of theValues
end repeat
end tell # row
end tell # Table "Tableau destination"
end tell # document of Numbers
If you don’t work with perfect match, edit the script according to what I posted yesterday.
Yvan KOENIG (VALLAURIS, France) mardi 6 janvier 2015 11:27:11
Like Yvan, I find it very difficult to understand what’s wanted here.
As I interpret it, you have a Numbers document with a sheet containing two tables. Given a certain key value, you want to search for it in column “A” of table 2, then copy all the values in the row in which it occurs to a particular row in table 1. If that’s the case, a rough outline would be:
set keyValue to "This value"
set rowInTable1 to 4
tell application "Numbers"
tell sheet 1 of document 1
try
tell table 2
set foundRow to row of first cell of column "A" whose value is keyValue
set rowValues to value of cells of foundRow
end tell
on error errMsg
-- Something's gone wrong. Maybe the value doesn't exist in column A.
-- Display the error message and stop the script.
display dialog errMsg
error number -128
end try
tell table 1
repeat with i from 1 to (count rowValues)
set value of cell i of row rowInTable1 to item i of rowValues
end repeat
end tell
end tell
end tell
I’m very sorry guys. I honestly started applescript last week so I’m sure that I am not helping very much with being clear about what I want. Nigel’s script works out perfectly EXCEPT for one thing.
Is there anyway that I can “set theValue to (contents of the clipboard)”?
The reason is that this spreadsheet is based on the starting lineups of a professional baseball team, so it will vary from day to day. I figured how to get the lineup off of a webpage so I can have theValue on my clipboard. So how do I set it?
Just a note in addition to what Yvan’s said: the ‘the’ in ‘the clipboard’ is part of the command name and isn’t optional as it is elsewhere in AppleScript.
set theValue to the clipboard as text
tell application "Numbers"
# your code here
end tell
At least, thanks to Nigel, this thread allowed me to learn something : a neat way to get the coordinates of the cell whose value contains a given value ” not necessarily a cell whose value IS the given one.
set theValue to "f12"
tell application "Numbers" to tell document "4see.numbers" to tell sheet "Feuille 1" to tell table "Tableau 1"
first cell whose value contains "f12"
set {theRowNum, theColumnNum} to {address of row, address of column} of result
--> {12, 5}
get value of cell theRowNum of column theColumnNum
--> "ff12"
end tell
Yvan KOENIG (VALLAURIS, France) mercredi 7 janvier 2015 17:05:26
Thanks for your help guys. Yvan as I’ve searched the Internet for answers your post on different forums has been a consistent refrain. I appreciate your help in helping me get what I needed.
Same here. I used to use ‘the’ quite a lot in my early AppleScripting days to try and make my scripts look as English-like as possible, but I haven’t bothered for years.
