I am a relatively new user of DT3 automation and not a programmer.
I have a spread sheet stored as an CSV File. The rows contain some columns thavt contain the following names:
desired name for a DT3 bookmark,
the specific URL of the website for that bookmark,
the date which I entered this information into the spreadsheet and
3 other columns of attributes to included with each record, one is type - text and the other two are type - decimal numbers
I have successfully copied a script from the forum that allowed me to create a record, type:Sheet.
After experimenting around to make all attributes searchable in DT3, I have identified the date column and the 3 other attribute columns as custom meta data. I have defined those custom meta data names in my preference settings.
Now I am attempting to create a script to properly create and populate DT3 bookmark records/documents of every row in the sheet document. I seem to be stuck on the proper way to add the custom meta data values after creating the record.
Below is the script file I have created. It has compiled successfully. I suspect the line “set theRecord to the record” is where I am having difficulty.
After the lines of code, I have also included a screenshot of the first few lines of the sheet document.
tell application id "DNtp"
repeat with thisRecord in (selection as list)
if (type of thisRecord) = sheet then
set sheetData to (cells of content record of think window 1)
repeat with thisData in sheetData
try
set recordName to (item 3 of thisData)
set startDate to (item 6 of thisData)
set this_URL to (item 7 of thisData)
set range to (item 2 of thisData)
set reliability to (item 4 of thisData)
set bias to (item 5 of thisData)
create record with {type:bookmark, name:recordName, URL:this_URL} in current group
set theRecord to the record
add custom meta data startDate for "Start_Date" to theRecord
add custom meta data range for "News_Range" to theRecord
add custom meta data reliability for "Reliability_Value" to theRecord
add custom meta data bias for "Bias_Value" to theRecord
end try
end repeat
end if
end repeat
end tell
This is the sheet document with data rows I am using in the script
Please encode code blocks in backticks like so
``` code goes here
```
In general, posts are formatted as is Markdown.
I fixed your code and list.
The correct syntax for metadata is add custom metadata startDate to "2024-01-01" for theRecord
As it’s stated in the scripting dictionary:
add custom meta data
any : The value to add. for text : The key for the user-defined value. to record : The record.
You should use selected records instead of selection as list.
I’m not sure if your code will do what you want it to do as you don’t seem to iterate over the rows of the sheet, only the columns.
Finally, create record returns a record. So, you could simplify the code to set theRecord to create record …
(I hope, I’m not using AppleScript).
tell application id "DNtp"
set theRecords to (selected records of think window 1)
repeat with thisRecord in theRecords
if (type of thisRecord) is sheet then
set allRows to (cells of thisRecord)
repeat with thisRow in allRows
try
set {theRange, theMedia, theReliability, theBias, theStartDate, theURL} to (items 2 thru 7 of thisRow)
set newRecord to create record with {type:bookmark, name:theMedia, URL:theURL} in current group
add custom meta data theStartDate for "Start_Date" to newRecord
add custom meta data theRange for "News_Range" to newRecord
add custom meta data theReliability for "Reliability_Value" to newRecord
add custom meta data theBias for "Bias_Value" to newRecord
end try
end repeat
end if
end repeat
end tell
Actually, for requires a “key for the user-defined value”, which refers to the identifier of a custom metadata field. to does indeed require a record.
PostScript: I now have finished creating a 130 record file with 1 additional column and saved as CSV. I have also updated the script to reflect the additional column.
However, when I use File > Import > Files and Folders to bring the records in….the imported file is no longer imported as a sheet but as kind=comma-separated-values. Which is not correct for my needs to run the new script.
I can’t tell that I have changed anything and have tried many different variations.
I just tried a very simple script with a CSV document here. The type in the script is sheet, and I can easily get the number of columns (see below). I’m not inclined to test anything else.
Did you actually run your script against this file? If so, did you see any errors? If not, what happens if you do?
(() => {
const app = Application("DEVONthink 3");
console.log(app.thinkWindows[0].contentRecord.type());
// prints "sheet"
console.log(app.thinkWindows[0].numberOfColumns());
// prints 8, and there are eight columns
})()
Thank you again for your help and patience. Given your most recent input, I stopped “assuming” that the script was not working due to CSV file type. Looking at the log, it had read all the lines in the sheet, but had not created the records.
On a hunch that adding my new variable to the script might be the problem, I changed the new variable name from “theMediaType” to just “theType” on the two affected lines and recompiled…
Voila! It successfully ran. So Im all set to start on my smart rules.
I’m not an AppleScript person, but even in this language, changing the name of a variable should not change the behavior of the code. I think, that this is purely coincidental, but since you don’t show your code
tell application id "DNtp"
set theRecords to (selected records of think window 1)
repeat with thisRecord in theRecords
if (type of thisRecord) is sheet then
set allRows to (cells of thisRecord)
repeat with thisRow in allRows
try
set {theMediaType, theRange, theMediaName, theReliability, theBias, theStartDate, theURL} to (items 2 thru 8 of thisRow)
set newRecord to create record with {type:bookmark, name:theMedia, URL:theURL} in current group
add custom meta data theStartDate for "Start_Date" to newRecord
add custom meta data theMediaType for "Media_Type" to newRecord
add custom meta data theRange for "News_Range" to newRecord
add custom meta data theReliability for "Reliability_Value" to newRecord
add custom meta data theBias for "Bias_Value" to newRecord
end try
end repeat
end if
end repeat
end tell
New code with changed variable
tell application id "DNtp"
set theRecords to (selected records of think window 1)
repeat with thisRecord in theRecords
if (type of thisRecord) is sheet then
set allRows to (cells of thisRecord)
repeat with thisRow in allRows
try
set {theType, theRange, theMedia, theReliability, theBias, theStartDate, theURL} to (items 2 thru 8 of thisRow)
set newRecord to create record with {type:bookmark, name:theMedia, URL:theURL} in current group
add custom meta data theStartDate for "Start_Date" to newRecord
add custom meta data theType for "Media_Type" to newRecord
add custom meta data theRange for "News_Range" to newRecord
add custom meta data theReliability for "Reliability_Value" to newRecord
add custom meta data theBias for "Bias_Value" to newRecord
end try
end repeat
end if
end repeat
end tell
First script did not populate the new records… Second script worked as needed…
Read your code again. A real language (ie not AppleScript) tells you if you use uninitialized variables like theMedia in your create record call. I’ll rant about that at the end of this post – please ignore that at will.
You’d perhaps also have spotted the problem if you’d run the code in Script Editor with the protocol window active.
In any case, it’s just as I supposed: Simply changing the name of a variable doesn’t do anything to make incorrect code correct. In the “new” version, you assigned a column value to theMedia, which thus has a value and the create record call works.
<rant>
I know that many people find AS easy to write. But it’s a PITA to read (especially if all variable names are called “the” or “this” something because one always has to read over three/four meaningless and useless characters to get to the real meaning) and to find errors in. As has just been demonstrated.
There’s at least one better designed language, which is also under active development (which AS is not), that can be used to script MacOS apps. And JavaScript has better support for array (list) methods, strings, Regular Expressions, a strict mode that tells you of undefined variables, functions as first class objects, a better object system etc. Not to mention a formal grammar instead a bunch of ill-defined pseudo-human language constructs.
</rant>
Good morning. Thanks for helping me understand the issue and what happened. You have been (somewhat) patient with me.
Given your input and me rethinking what actually occurred, I have now concluded the following:
Lesson learned: Using script kiddie techniques and sloppily updating my script/code in a rush, combined with haphazard code/script debugging, caused me problems with using borrowed code and having subsequent problems with fixing it. I only resolved my issue by having you point out my defective analysis and thinking.
Net Conclusion: The scripting/coding language wasn’t too difficult for my use. The scripter/coder (me) embarked on using other people’s code without appropriate focus, rigor and time spent to properly solve the problem on my own without multiple interventions from the posting community.
Thank you again, Jim
—————————————————
Detailed analysis of what occurred and where I went wrong in this instance:
when I added additional columns into my spreadsheet, I needed to update the original script that I had earlier copied and successfully used for my test file (thank you again meowky!),
when I updated the script file, I created a change to a key variable on one line but not the required subsequent one that created the file.
when I ran the revised script, the script no longer processed the file. I looked at the changes I made for the new variable and the column numbers of the input file and could not find a problem.
When I updated my script I recompiled and only later realized I had not renamed my updated script before doing so.
my script debug analysis was being done in a rushed and haphazard manner between vacation beverages and other activities.
I discovered that the file type of my input file (excel to Devonthink file) was listed as “CSV”. I attributed that at first to being the problem as the script was not producing output.
You pointed out to me this was likely not be the problem
After brief reflection, I decided that perhaps my updated code was the problem. So I recopied the original script text from meowky and readded the new variable under a different name and updated the column numbers in the script.
Voila! The script successfully ran and I attributed it to my changing the variable name.
You rightly pointed out the foolishness of my assumption and pointed out where the problem was.
I sheepishly understood where I went wrong and how my undisciplined and rushed analysis did not allow me to find and correct the problem myself.
Thank you again for helping a newbie like me leverage the information I have into Devonthink with searchable data that can be grouped with DT automation.
