Tinderbox Forum

Applescript to Export/Copy selected notes in Attribute Browser View to Clipboard to use in Excel?

I am not sure if this exists but does anyone know of an applescript to export a selected group of notes to the clipboard in the Attribute Browser as a tsv for import into Excel?

Use case, AB is SUCH a useful view in tinderbox (my favorite by the way) it would be useful to be to interchange this columnar view with Excel. I know that copying to clipboard in AB has been requested in the past (hence now looking for an applescript method) and export Text-> csv remains an option for the entire file but thought I would post to get other ideas.

Thanks
Tom

AppleScript can easily grab attribute values from just the notes you have selected. You can select them in AB if that is more convenient. Or you can select them in other views. AppleScript won’t know specifically how you have configured AB, so this may not be exactly what you are looking for.

Here are a couple of scripts you might try. With each you first select the notes you want and then run. The output is character-separated-values on the clipboard for pasting into Excel, or Numbers or wherever.

Script 1

This is a bare-bones script that has the virtue of simplicity.

tell application "Tinderbox 9"
	tell front document
		tell selections
			set theNames to name
			set theTexts to attribute "Text"'s value
			set theURLs to attribute "URL"'s value
			set theTags to attribute "Tags"'s value
		end tell
	end tell
end tell

set exportStr to "Name" & tab & "Text" & tab & "URL" & tab & "Tags" & return

repeat with n from 1 to length of theNames
	set exportStr to exportStr & item n of theNames & tab & "\"" & item n of theTexts & "\"" & tab & item n of theURLs & tab & item n of theTags & return
end repeat

set the clipboard to exportStr
return exportStr -- to view in "Result" pane

Grabbing just the attributes you want and arranging them in columns in the order you want requires manual adjustments to the code. That entails about the same level of bother and difficulty as setting up and revising export templates in Tinderbox. As with export templates, you have to fiddle each time your requirements change.

Script 2

The following is a more ambitious script that attempts to make it easy to reconfigure the output without adjusting the AppleScript code. You specify which columns of attributes you want, in what order, and with what delimiter, by simply “filling in a box” within a prompt.

# Select Tinderbox notes (non-contiguous, multiple levels ok) and click run.
# Places quoted character-separated values (CSV) on clipboard for pasting wherever.
# v. 1.2 -- includes simple date conversion and "edit the box" dialog

property entryString : "Name Text t"

set dialogText to "Export selected notes from Tinderbox to the system clipboard in a csv (character-separated value) format for pasting into spreadsheets or other applications." & return & return & "1. Select notes in Tinderbox" & return & return & "2. Edit the box below to select Tinderbox attributes and order them as you want them to appear in the spreadsheet. Use the exact spelling. Add/delete/reorder in the box as needed. Use one or more spaces between names." & return & return & "3. After the last attribute name enter one or more spaces followed by the delimiter to be used to separate items in the exported csv." & return & return & "4. Click 'Continue' to export to the clipboard for pasting. " & return & return & "                  ------------------------" & return & return & "For the delimiter enter a single character:" & return & return & "   t  for tab" & return & "   ,  for comma" & return & "   ;  for semicolon" & return & "   |  for pipe" & return & "           and so on..." & return & return & "Examples of possible entries in the box:" & return & return & "   Name MyString Path Text ," & return & "   Name MyString MyDate MySet Text ;" & return & "   Name MySet MyString MyDate Text   ;" & return & "   Name   Text   MyString   MySet    t" & return

set theResponse to display dialog dialogText default answer entryString buttons {"Cancel", "Continue"} default button "Continue" with title "Tindebox Export to Spreadsheet"

set entryString to text returned of theResponse
tell entryString
	if character -2 is not " " then error "Can't find a delimiter at end"
	set myDelimiter to last character
	if myDelimiter is "t" then set myDelimiter to tab
	set grabAttribs to words of text 1 thru -2 --if "t" present ignore as last "word"
end tell

--  NO USER TINKERING NEEDED BELOW THIS LINE, at least in theory;)

set text item delimiters to quote & myDelimiter & quote

# create separate list to hold lists of attribute values (a "list of lists")
copy grabAttribs to attribVals

set numCols to grabAttribs's length

tell application "Tinderbox 9"
	tell front document's selections -- (note use of plural)
		repeat with i from 1 to numCols
			set attrName to grabAttribs's item i
			tell attribute attrName
				set attribVals's item i to value
				if item 1's type is "date" then copy my convertDates(attribVals's item i) to attribVals's item i
			end tell
			# adds list of this attribute's values to the list of lists
		end repeat
	end tell
end tell

set numRows to attribVals's item 1's length

set outStr to quote & (grabAttribs as string) & quote & return # header row

repeat with i from 1 to numRows
	set rowItems to {}
	repeat with j from 1 to numCols
		set rowItems's end to attribVals's item j's item i
	end repeat
	set outStr to outStr & quote & (rowItems as string) & quote & return # body row
end repeat

set the clipboard to outStr

display notification "Tinderbox data ready for pasting."

return outStr -- uncomment this line to view output in Script Editor 'Result' pane


-- handlers (subroutines)

to convertDates(listOfIsoDates)
	-- convert list of Tinderbox ISO 8601 dates to spaced date-time string for spreadsheets
	set convertedDates to {}
	repeat with i from 1 to listOfIsoDates's length
		tell listOfIsoDates's item i
			if it is "never" then
				set convertedDates's end to ""
			else
				set convertedDates's end to its text 1 thru 10 & " " & text 12 thru 19
			end if
		end tell
	end repeat
	return convertedDates
end convertDates

This code in this second one is clearly is more “involved” (be sure to scroll down to grab the whole thing). But in theory you won’t need to modify it even when your needs change. You can put it in the script menu where it becomes a simple menu pick. For me this script is easier to use than the first script above or export templates within Tinderbox, when exporting to Excel or Numbers (or other apps that accept character-separated values).

1 Like

Wow. Great script! Brilliant!

Works wonderfully in AB view! Ready to copy and paste into Excel.
I too am using Script 2.Made my day!

Many thanks Sumner
Tom

Hi all, BTW, there are two alteartive approaches for this.

  1. In the outline review, add the columns you want to see. Copy the notes and then paste them in excel.
  2. Create an export template the produces a table. Look at it in preview. Copy the preview, paste it in excel.

Thanks Michael,

Great suggestions and alternative approaches as well. I did not think of either.

Tom

Both involve quite a bit more work than using Script 2. Suggest giving it a try.

Also, can see an advantage to making the selection in AB view. The notes can be quickly grouped for selection, without coding.

Thanks for the nice feedback! Tinderbox has a good implementation of AppleScript. Documentation for AppleScript, though, is not always consistent or clear. It’s especially “unorthodox” for Tinderbox. That can make it a challenge to write scripts, even though I’ve found debugging is easier than for TB action code.

AppleScripts, once written, can be easy to use.

That second script above has saved me a lot of time that otherwise would have been spent on unproductive fiddling with templates and such. :grinning: Plus it’s reusable on other Tinderbox documents. Unlike export templates or columns, it doesn’t have to be set up in each new document. Just “fill in the box.”