Bringing a new question to the table today, I am trying to figure out something that is driving me a bit bonkers and my Google-Fu is failing me at this point so I am hoping someone much more knowledgable than me can point me in a right direction.
I am trying to use AppleScript to read through a table in Numbers (Single column with 10 rows), I can get it to point to the table and can get it to enter some data but I cannot get it to fill in all the data correctly, ideally what I am looking for it to do is to read through the table and see if it contains any data (by default it will be blank) then to prompt to ask for data to be entered (No need to enter this previously if possible to save entering in data when not needed) then to iterate through the list and enter the data into the column.
Here’s what I currently have being used:
tell table Table 1 of sheet Sheet 1
repeat with rowNumber from 1 to 10
try
set cellValue to value of cell ("A" & rowNumber)
if cellValue is " " then exit repeat
on error
exit repeat
end try
end repeat
set Q1 to text returned of (display dialog "Q1" default answer "")
set Q2 to text returned of (display dialog "Q2" default answer "")
set Q3 to text returned of (display dialog "Q3" default answer "")
set Q4 to text returned of (display dialog "Q4" default answer "")
set Q5 to text returned of (display dialog "Q5" default answer "")
set Q6 to text returned of (display dialog "Q6" default answer "")
set Q7 to text returned of (display dialog "Q7" default answer "")
set Q8 to text returned of (display dialog "Q8" default answer "")
set Q9 to text returned of (display dialog "Q9" default answer "")
set Q10 to text returned of (display dialog "Q10" default answer "")
set valueList to {Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10} -- Table variables for input
repeat with i from 1 to 10
set value of cell ("A" & rowNumber) to (item i of valueList)
end repeat
end tell
For one I am unsure how to get it to stop the repeat if it detects data in the list, then to continue on, so any help from those wiser and more knowledgable is appreciated
Not sure that I fully understand your objective but try this:
Basically, it cycles through the cells in your column. If the cell contains a space then it is skipped — but the user is notified; if it does not, then the user is asked to input the new value for the cell. When completed, the script provides the list of values for the cells.
use scripting additions
tell application "Numbers"
set d1 to document 1
set s1 to sheet 1 of d1
set t1 to table 1 of s1
tell t1
set valueList to {}
repeat with rn from 1 to 10
if value of cell ("A" & rn) is space then
display dialog "Row " & rn & " is a space" with title "End of the line"
set end of valueList to space
else
set qq to text returned of (display dialog "Q" & rn default answer "")
set end of valueList to qq
set value of cell ("A" & rn) to item rn of valueList
end if
end repeat
end tell
end tell
valueList
--> {"1", "2", "3", "4", " ", "6", "7", " ", "9", "10"}
The idea behind it is to check whether the table contains data, if it does then it can exit the loop and then continue on through the next segment of code, if it’s empty then prompt a series of questions (names, dates, times etc for specific cells) to build out a list then populate the cells in the table with the information that was provided.
So if any cell in the table contains data then skip everything that’s in your original post, but if every cell is empty, then cycle through them with the ‘q’ dialogues? Is that correct?
A couple of questions… is the data in the cell range all numbers or all text or a combination? Will the ‘empty’ cells be really empty? You use a ‘space’ in your original script. An empty cell should have a missing value. If they are truly empty, then you could do something like this:
tell application "Numbers"
set d1 to document 1
set s1 to sheet 1 of d1
set t1 to table 1 of s1
set cellValues to value of cells of t1
repeat with v in cellValues
if contents of v is not missing value then
set addData to false
display dialog "False"
exit repeat
else
set addData to true
end if
end repeat
if addData then
-- put q-loop here
end if
end tell
If there is a situation where a cell has some contents but you don’t want it to skip the adding of data, then you’d have to modify the test somewhat.
By the way… I didn’t elaborate in my first post but rather than trying to create a series of variables, just create a list of the values. That’s how it’s handled in my post.
I’ve not used Numbers very much but, if it’s known that empty cells return missing value when retrieving their value property, it ought to be possible to do this:
tell every cell in t1 to set addData to ¬
missing value is in its value
Alternatively, to get a list of only empty cells (by name):
set emptyCells to the name of every cell
in t1 whose value = missing value
I will again thank you as ever for the input and learning with it, this has helped immensely, different solutions for different requirements, I just couldn’t figure out how to get it to register if there is data in the cells to progress, sometimes the simplest solution works the best, I was just using the space as a placeholder as I would have a space in someway shape or form so it would help me confirm if there was some data in there or not.
The reason for the question loop was because there is a number of them each with different requirements and I was wanting to just streamline it e.g. prompt for all the questions to compile the list then cycle through inputting the items in the list.
Thank you for the response, I shall have to tinker with this and see what it does, I can tell you that Numbers will report if cells are missing value, I could get it to report that, mine was the reverse, I couldn’t get it to register if there was data in the cell ideally, so Mockmans response of is not missing value is the answer to my question because I wanted it to try and register if there is a value instead of is not missing a value but I do appreciate the other point of way to look at things also.
Ah, I understand. Yes, @Mockman’s solution creates and sets a variable addData that will have rhe value true when the table is completely empty, and false otherwise.
My snippet was therefore wrong, as it assigns true to addData if any cell in the table is empty, and thus addData is false only when none of the cells are empty. As you said, I got it completely reversed, which I didn’t spot initially. My apologies to you and @Mockman both. The reverse logic, as you have also intimated, isn’t as straight-forward as merely negating the origjnal, and that’s undoubtedly why @Mockman took what I now see was a very sensible approach to attack the problem iteratively.
Here’s my hat: . And here’s what I shall eat it with:.
And, after drawing a truth table with pen and paper, I can offer the corrected version of my code snippet for interest:
set addData to not (exists (the cells in t1 ¬
whose value is not missing value))
Thanks CJK for the update no need eat the hat for we are all here to learn from each other and that sometimes means we have to make mistakes, I know I am making plenty of mistakes myself in learning this and sometimes questioning my sanity but that is why when I reach an end point where I seem stuck in a corner I come and ask here and all the more knowledgable and experienced of you help me see another method than I previously did.
I shall have to have a tinker with the snippet when I have a bit more time but for now it is getting late.
Thanks. That is interesting. I tried for awhile to figure out a non-iterative approach but eventually ceded the field to sensibility.
FYI, per the cell entry in my version of the Numbers dictionary, there are three properties that may return missing value; value and formatted value will do so when the cell is empty. formula will do so when the cell contents are not a formula.
. And here’s what I shall eat it with:
.