Hi, All -
I am not a coder, and could use some help.
I’ve hacked together this very, very hacky (auto-correct suggested “tacky”, and it’s not wrong) script to create new items in DT from a Numbers sheet. It works, and I’m unreasonably proud of this fact, but there are two things I could use help with:
-
The script is formatting values - where the sheet says “4” in the third column, it’s appearing in DT as “4.0”. I would guess I just need to find a different type of variable to avoid this behavior?
-
For cells that do not have data, the script is writing “missing value”. Any suggestions as to how to handle that - to set the variable to something blank? Or to check if it’s empty and skip including it in the MD creation?
I’ll attach a screenshot of the sample spreadsheet I’m working with, and the script.
Many, many thanks.
tell application "Numbers"
tell document 1
tell active sheet
---------------------------------------------------------------------
# Ensure there IS a selection:
---------------------------------------------------------------------
try
set activeTable to (the first table whose class of selection range is range)
on error
error "Something is wrong with the selection in the front document."
end try
---------------------------------------------------------------------
# Data Extraction
---------------------------------------------------------------------
tell activeTable
tell application "Numbers"
set activeSheet to active sheet of front document
set scriptStart to current date
tell table 1 of activeSheet
set aRange to "A2:A6"
set aRows to the rows of range aRange
repeat with loopvar from 1 to count of aRows
set rowList to rows of selection range
set theRow to item loopvar of rowList
set rowNum to (address of theRow) as text
set City to value of first cell of range ("A" & rowNum)
set Repository to value of first cell of range ("B" & rowNum)
set Shelfmark to value of first cell of range ("C" & rowNum)
set Notebook to value of first cell of range ("D" & rowNum)
if first cell of range ("E" & rowNum) is not null then
set Notebook2 to value of first cell of range ("E" & rowNum)
end if
if first cell of range ("F" & rowNum) is not null then
set PhotosYN to value of first cell of range ("F" & rowNum)
end if
if first cell of range ("G" & rowNum) is not null then
set Extra to value of first cell of range ("G" & rowNum)
end if
set loopvar to (loopvar + 1)
#display dialog Repository & " " & Shelfmark & " " & Notebook & " " & Notebook2
set recName to Repository & " " & Shelfmark
tell application id "DNtp"
set newDocument to create record with {name:recName, type:markdown, content:City & ", " & Repository & ", " & Shelfmark & linefeed & linefeed & "---" & linefeed & "Notebook: " & Notebook & linefeed & "Notebook Additional: " & Notebook2 & linefeed & "Photos: " & PhotosYN & linefeed & "Other: " & Extra}
end tell
end repeat
end tell
end tell
end tell
---------------------------------------------------------------------
end tell
end tell
end tell
I’m going back one step. Why do this? What is your goal? Why are you not just using the Numbers formatted and setup as you want and then store the Numbers file in DEVONthink without going to all this? Numbers much more capable than DEVONthink for spreadsheets.
Totally fair question - the numbers sheet is (indexed) in DT. It, in turn, is a record of a decade of physical and digital notes on individual medieval manuscripts. Having items in DT for each item in the sheet makes possible links and back links and such, to anchor my research. There are several hundred records in the spreadsheet, so I’m hoping to avoid creating documents in DT manually.
1 Like
Trying to answer your questions:
- append
as string to those values that might be numbers. Perhaps you need to enclose them in parenthesis first
- afaik, you can test for
missing value in AppleScript, eg in an if statement.
Apart from that: I’d export the Numbers document as CSV and dissect that in a shell script. Seems a lot easier to do then with AppleScript. cut and sed are commands that might help here.
1 Like
Thanks, both, for your help.
- “As string” didn’t solve the problem, but the first comment, re: Numbers itself, gave me the idea to format the cells as “text” in Numbers itself, rather than auto format.
- “missing value” was indeed the key, rather than null. Cheers.
In the unlikely event anyone else wants to create a boatload of markdown notes from a spreadsheet:
tell application "Numbers"
tell document 1
tell active sheet
---------------------------------------------------------------------
# Ensure there IS a selection:
---------------------------------------------------------------------
try
set activeTable to (the first table whose class of selection range is range)
on error
error "Something is wrong with the selection in the front document."
end try
---------------------------------------------------------------------
# Data Extraction
---------------------------------------------------------------------
tell activeTable
tell application "Numbers"
set activeSheet to active sheet of front document
set scriptStart to current date
tell table 1 of activeSheet
set aRange to "A2:A6"
set aRows to the rows of range aRange
repeat with loopvar from 1 to count of aRows
set rowList to rows of selection range
set theRow to item loopvar of rowList
set rowNum to (address of theRow) as text
set City to value of first cell of range ("A" & rowNum)
set Repository to value of first cell of range ("B" & rowNum)
set Shelfmark to value of first cell of range ("C" & rowNum)
set Notebook to value of first cell of range ("D" & rowNum)
set Notebook2 to value of first cell of range ("E" & rowNum)
if Notebook2 is missing value then
set Notebook2 to " "
end if
set PhotosYN to value of first cell of range ("F" & rowNum)
if PhotosYN is missing value then
set PhotosYN to " "
end if
set Extra to value of first cell of range ("G" & rowNum)
if Extra is missing value then
set Extra to " "
end if
set loopvar to (loopvar + 1)
#display dialog Repository & " " & Shelfmark & " " & Notebook & " " & Notebook2
set recName to Repository & " " & Shelfmark
tell application id "DNtp"
set newDocument to create record with {name:recName, type:markdown, content:City & ", " & Repository & ", " & Shelfmark & linefeed & linefeed & "---" & linefeed & "Notebook: " & Notebook & linefeed & "Notebook Additional: " & Notebook2 & linefeed & "Photos (Y/N): " & PhotosYN & linefeed & "Other: " & Extra}
end tell
end repeat
end tell
end tell
end tell
---------------------------------------------------------------------
end tell
end tell
end tell
Why do you set the missing values to a space instead of an empty string? And is it really necessary to increment loopvar inside the repeat? I’d have thought that’s the job of the repeat itself. But then, I don’t really know Applescript.
Ignorance, I think. Changed. Thanks.
