Rich Text in Sheet Cells

I’ve got an interesting problem and it has to do with dealing with rich text and custom meta data.

I have several variants of an AppleScript that collect custom meta data from certain records — the records list is volatile from day to day and has to do with the status of a project whether it is in the places this Script works on.

The goal is to collect relevant metadata, annotations, and custom meta data and dump them into a table — it’s a customized “Create metadata overview” however it does some work in addition to what that script does besides filtering. Plus, I was never able to figure out how to run the “create metadata overview” by way of a script, so I just did it. :slight_smile:

At the end of one section of the script, it has build an AppleScript list of records, one of the list items is a rich text object copied from a custom meta data setting with the type Rich Text.

I dump the list of records into the cells property of a sheet I created in the script.

So the script does this:

set DTCells to myGiantListOfRecords
Set mySheetName to “any name you want “
create record with {name:MySheetName columns:colDescriptions cells:DTCells type:sheet}

It works other than placing the rich text object. Is there a way to do this and get the rich text in the cell of the sheet?

I can’t answer your question, but I suggest that you provide more relevant information. For example, illustrate how you build yourGiantListOfRecords. In my mind (but I’m not an AppleScript person), a list of records would be something like
{ {name: value, otherName: secondValue}, {...}, {...}}
If that contains, as you said, a “rich text object”, it wouldn’t be a list of “records” but of different types of data. Again: I’m not an AppleScript person, so only speaking very generally here.

This handler (see the context) gets the plain text for Custom Meta Data of type Rich Text:

on getPlainTextFromRTFDdata(theData)
	try
		set theNSData to (current application's NSArray's arrayWithObject:theData)'s firstObject()'s |data|()
		set {theAttributedString, theError} to (current application's NSAttributedString's alloc()'s initWithData:theNSData options:(missing value) documentAttributes:(missing value) |error|:(reference))
		if theError ≠ missing value then error (theError's localizedDescription() as string)
		set theString to theAttributedString's |string|()
		return theString as string
	on error error_message number error_number
		activate
		if the error_number is not -128 then display alert "Error: Handler \"getPlainTextFromRTFDdata\"" message error_message as warning
		error number -128
	end try
end getPlainTextFromRTFDdata

As the handler uses AppleScriptObjC you need to include this at the top of your script:

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions
1 Like

Thanks — it’s all there already. I was using asobjc to grab properties also already and using it to filter and make sets so this is good.

I have a different question about that however - arraywitharray:objectforkey refuses to grab values for regular meta data such as “addition date”

Do you have a way to get property values from a record using strings?

Say I have a list {“addition date”, “kind”, “name”}…. I want to iterate the list and get the values for those record properties in order. I thought arrayWithArray:objectforkey or valueforkey would do it but it doesn’t (!)

Those methods will grab custom meta data though.

Any ideas ?

Not sure this is what you’re looking for but we can get properties by creating the source and compiling it on run time:

-- Get properties via strings

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions

set theProperty_1 to "name"
set theProperty_2 to "addition date"

set theScript_Source to "tell application id \"DNtp\"
	try
		set theRecords to selected records
		if theRecords = {} then error \"Please select a record\"
		
		set theResultsList to {}
		
		repeat with thisRecord in theRecords
			set thisRecord_property_1 to %@ of thisRecord -- theProperty_1
			set thisRecord_property_2 to %@ of thisRecord -- theProperty_2
			set end of theResultsList to {thisRecord_property_1, thisRecord_property_2}
		end repeat
		
		return theResultsList
		
	on error error_message number error_number
		if the error_number is not -128 then display alert \"DEVONthink\" message error_message as warning
		return
	end try
end tell"

set theScript_Source_withProperties to current application's NSString's stringWithFormat_(theScript_Source, theProperty_1, theProperty_2)
set theScript to current application's NSAppleScript's alloc()'s initWithSource:theScript_Source_withProperties
set theNSAppleEventDescriptor to theScript's executeAndReturnError:theScript_Source_withProperties
set theScript_Result to ((current application's NSArray's arrayWithArray:{theNSAppleEventDescriptor}) as list)'s item 1

(I tried that some time ago for a version of the “Sort by two criteria” script that handles variable properties in order to let me choose via a dialog instead of hard-coding them. But still didn’t finish that script)

1 Like

And it has to be AppleScript because? Doing that with JavaScript is a no-brained, as I told you already over at MacScripter. AS is simply not made for some of the modern programming approaches.

1 Like

Give me a way to put your JavaScript in the middle of an applescript.

This is interesting…. I have a list of records so there’d be no selection or user input.

What’s weird to me is I don’t know why the regular record metadata (whose labels appear in purple color in script editor) won’t respond to arrayWithArray - BUT custom meta data keys - whose labels appear in green color in script editor can be retrieved.

AFAIK, there’s no way to get regular properties other than to (implicitly) get them.

I have been avoiding hardwiring a giant if tree - but I think that’s what this might take.

The goal has been to have an arbitrary list of properties and the handler spit out an ordered list of the values per record.

So {“addition date”, “kind”, “mdDocumentDate”, etc}

The handler looks for “md” to indicate custom meta data, and this part absolutely works. There is a repeat that is iterating the property list by item

else If myproperty begins with “md” then
   Try
        Set ca to current application
        Set theCMDRecord to custom meta data of item kj in childListItemsref
        Set theValue to ((ca’s NSDictionary’s dictionaryWithDictionary:theCMDRecord)’s objectForKey:myproperty)
        If missing value is not equal theValue then
               Set theValue to item 1 of ((ca’s NSArray’s arrayWithObject:theValue) as list)
         Else
               Set the value to missing value
         End if
        On error
                Set the value to missing value
        End try
     Set the end of myPropListref to the Value
   End if

That works

But doing the same thing for any property, such as “addition date” or “kind” does not….

Assume either of those property names came through, then they hit the else clause which does this:

copy the (properties of item kj of childListItemsref) to the Record
else 
   Try
        Set ca to current application
        Set theCMDRecord to custom meta data of item kj in childListItemsref
        Set theValue to ((ca’s NSDictionary’s dictionaryWithDictionary:theRecord)’s objectForKey:myproperty)
        If missing value is not equal theValue then
               Set theValue to item 1 of ((ca’s NSArray’s arrayWithObject:theValue) as list)
         Else
               Set the value to missing value
         End if
        On error
                Set the value to missing value
        End try
     Set the end of myPropListref to the Value
   End if

That results in missing value every time….

So, I either make a hard wired case of if tree in that else block to grab the property values, use the JavaScript suggested somehow, or find the right ASObjC method…. Or use your script handler …. I’m going to try the script handler you wrote since I want to keep the ability to have an arbitrary list of relevant metadata….

If there was a way to run “create metadata overview” from the script, that’d make it easy to cherry pick the TSV and dump the TSV once done. But alas…

Got it to work. Clunky, but it does it!!

Here:

          set theScriptSource to "tell application id \"DNtp\"
          set thisList to {}
          set thisRecord2 to get record with uuid \"%@\" 
          set thisRecordProperty to %@ of thisRecord2
          copy thisRecordProperty to the end of thisList
          copy thisRecord2 to the end of thisList
          return thisList
          end tell"
                                                                                                           
                                                                                                           
          set theScriptSourceWithProperties to current application's NSString's stringWithFormat_(theScriptSource, theRecordHere, myproperty)
          -- log theScriptSourceWithProperties as text
          set theScript to (current application's NSAppleScript's alloc()'s initWithSource:theScriptSourceWithProperties)
          set theNSAppleEventDescriptor to (theScript's executeAndReturnError:theScriptSourceWithProperties)
          set theScriptResult to ((current application's NSArray's arrayWithArray:{theNSAppleEventDescriptor}) as list)'s item 1
                                                                                                           
try
           set theValue to item 1 of theScriptResult
on error
           set theValue to missing value
end try

It is getting properties one by one based on the label of the current list item in a giant repeat loop. That’s perfect.

Sending it the uuid and the property label does it — it finds the record, gets the value, and returns it in the two item list

At some point, I’ll clean up the list returned - no need for the second item to be there, but I wanted to get the result as fast without figuring out how to parse the single item NSAppleEventDescriptor. Just need the value for now and that worked perfectly!

Now, if I could only get the actual rich text into the final sheet! The plain text can work but would prefer to carry the formatting

AFAIK a sheet is just plain text, if that’s true then it’s probably not possible to store RTF data in it. That’s why I posted the getPlainTextFromRTFDdata handler.

Now that (I think) I understood what you’re after: The only way I can think of to store RTF in a plain text format (without loosing its formatting) would be to convert the RTF data to an RTF “source”, i.e. the plain text that one can see if an RTF file is opened in a text editor (e.g. BBEdit). This of course wouldn’t be easy to read for humans.

1 Like

The sheets can have Rich text because the “create meta data overview” function results in a sheet whose columns can have rich text in a cell.

This is why I am stumped — when I use script debugger to look at a sheet’s cells, the entry for a rich text cell looks just like what I’m trying to put into them — it’s an object like: data (HASHENCODEDSTRINGOFLOTSOFCHARSAND02938824352394)

-- Get Custom Meta Data of type "Rich Text" as Base64 text

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions

property theCMD_Identifier : "testrtf" -- set your Custom Meta Data identifier

tell application id "DNtp"
	try
		set theRecords to selected records
		if theRecords = {} then error "Nichts ausgewählt."
		set theRecord to item 1 of theRecords
		
		set theRecord_CMD_RTF to get custom meta data for theCMD_Identifier from theRecord
		if theRecord_CMD_RTF = missing value then error "No Custom Meta Data found"
		set theRecord_CMD_RTF_text to my getRTFCustomMetaDataAsBase64Text(theRecord_CMD_RTF)
		
	on error error_message number error_number
		if the error_number is not -128 then display alert "DEVONthink" message error_message as warning
		return
	end try
end tell

on getRTFCustomMetaDataAsBase64Text(theData)
	try
		set theNSData to (current application's NSArray's arrayWithObject:theData)'s firstObject()'s |data|()
		set theNSData_encodedString to current application's NSString's alloc()'s initWithData:(theNSData's base64EncodedDataWithOptions:0) encoding:(current application's NSUTF8StringEncoding)
		return theNSData_encodedString as string
	on error error_message number error_number
		activate
		if the error_number is not -128 then display alert "Error: Handler \"getRTFCustomMetaDataAsBase64Text\"" message error_message as warning
		error number -128
	end try
end getRTFCustomMetaDataAsBase64Text

That’s right, a sheet or .csv file is just a plain text file and rich text data is therefore base-64 encoded.

1 Like