Export an index of annotations to a numbers spreadsheet

This script will export an index of annotations and source documents to an already open Numbers spreadsheet. It works best with any of the annotation templates in this thread: [url]Make an Annotation with Links, Notes, Tags v2]

The spreadsheet has 5 columns:
a) The date of the source document or annotation
b) The name of the source document or annotation
c) The page number of the source document referenced by the annotation.
d) The comments, if any to the annotation.
3) The referenced source document, if the document is an annotation.

a) Open a numbers spreadsheet
b) Select annotations (rtf) and/or source documents (pdfs)
c) Run script
d) Choose a name for the table to be created in numbers

The script takes a set of documents like this

and turns them into a table in numbers like this

property pTitle : "Compile index to numbers"

tell application id "DNtp"
	-- each headerRow column has two entries: the name of the column and its width
	set headerRows to {{"Date", 71, left}, {"Description", 300}, {"Page", 71}, {"Comment", 300}, {"Source Document", 300}}
	set theseRecords to the selection
	set tableList to {}
	set theTableName to display name editor pTitle info "Title of Index"
	repeat with this_record in theseRecords
		set theNote to comment of this_record
		set thisUrl to URL of this_record
		set theCurrentPage to ""
		set theRecordname to ""
		if (count of thisUrl) > 61 then
			set onlypagerefs to (characters 57 through 61) of thisUrl as string
			if onlypagerefs is "?page" then
				set theCurrentPage to ((characters 63 through -1) of thisUrl) as string
				set theCurrentPage to (theCurrentPage as integer) + 1
				set theUUID to (characters 21 thru 56 of thisUrl) as string
				set theRecord to (get record with uuid theUUID)
				set theRecordname to (name of theRecord)
			end if
		end if
		set record_list to {creation date of this_record as string, name of this_record as string, theCurrentPage as string, theNote as string, theRecordname as string}
		set number_of_items to count tableList
		set end of tableList to record_list
	end repeat
	-- This code is drawn from http://macosxautomation.com/applescript/iwork/numbers/examples.html 
	-- Build the table in an already open Numbers spreadsheet
	-- theTableName must not conflict with an already existing table
	tell application "Numbers"
		set useHeaders to true -- change to false to omit headers
		if useHeaders is true then
			set the rowCount to (the count of tableList) + 1
			set the columnCount to (the count of item 1 of tableList)
			set the rowCount to the count of tableList
			set the columnCount to the count of item 1 of tableList
		end if
		-- create and populate a table reading the data as row based
		tell document 1
			tell active sheet
				set thisTable to ¬
					make new table with properties ¬
						{row count:rowCount, column count:columnCount, name:theTableName, header row count:1, header column count:0}
				tell thisTable
					if useHeaders is true then
						-- set the starting indexes for using headers
						set rowIndex to 1
						set columnIndex to 0
						-- since headers are used, name the column & row headers
						-- title and style the column header cells
						set x to 1
						repeat with i from 1 to the columnCount
							set columnDesc to item i of headerRows
							set width of column i to item 2 of columnDesc
							tell cell 1 of column i
								set value to item 1 of columnDesc
								set alignment to center
								set vertical alignment to center
							end tell
							set x to x + 1
						end repeat
						set width of column 1 to 71
						set width of column 2 to 525
						set width of column 3 to 71
						-- set the starting indexes for not using headers
						set rowIndex to 0
						set columnIndex to 0
					end if
					-- populate the table with the data
					set the rowCellCount to count of cells of row 2
					repeat with i from 1 to count of the tableList
						-- get a data set from the data set list
						set thisRowData to item i of the tableList
						tell row (rowIndex + i)
							-- iterate the data set, populating row cells from left to right
							repeat with q from 1 to the count of thisRowData
								tell cell (columnIndex + q)
									set value to item q of thisRowData
									set alignment to center
									set vertical alignment to center
								end tell
							end repeat
						end tell
					end repeat
				end tell
			end tell
		end tell
	end tell
end tell

There is an alternative version of this script which builds the table in Devonthink’s sheets. That version is more exportable to Excel or other programs:[url]Put up Example page]


This is very clever and can be used by many disciplines and researchers. Thank you!! 8)

Is it feasible with Numbers to make “the referenced source document” (item #3) into a clickable link to the source in DEVONthink. I realize that this table might be sent to persons who do not have access to the database – but for internal use a link is nice to have.

Rather than Numbers, the technique could be adapted to create a table using MultiMarkdown syntax, export the report, and print or convert to PDF with Marked.

I am sure it is. The Numbers object model seems pretty rich although its missing a few obvious things. I personally would never use it in the way you suggest so I will leave it as an exercise to someone else. If I needed an index document to link back to the individual records I would rather use a variation of your very neat Index script korm. ([url]Put rtf links to selected group and children on clipboard] )


Thanks Frederiko for this splendid script, as Korm says, “very clever” indeed and very useful. 8)

Just wondering if this script will work if one doesn’t use the “Make an Annotation with Links, Notes, Tags v2” script – but creates tags through another method. Would it still be possible to select the tagged files (even if they’re in a Markdown format), the source files, and then run the script?


Yes. It should work with any document which uses the url field to link back to another document.



I copied the code, opened ScriptEdtior, pasted the script and hit “Compile” – but then a ScriptEdtior window popped up saying, “Unexpected end of line but found identifier.” and then highlighted the word “sheet” in the script – as in…

tell document 1
tell active sheet

…as part of the code.

Is there a problem with the code? Or is there an updated version of it? Or am doing something wrong?


You didn’t copy all the code.

In the original post, click “Select All” at the top of the code listing. Immediately after, use Copy – ⌘C. Then go to Script Editor. Create new document. Click in the new document. Paste – ⌘V Then Compile – Script > Compile or ⌘K

The script copies and compiles without error for me in Script Editor and in Script Debugger – running 10.11.3.

I followed your instructions exactly, but unfortunately I experience the same exact error.

Let me know if I can provide any further info about this. Or if anyone has any ideas / suggestions about how to fix this. Thanks!

Did you read the internal documentation in the script and the instructions in the original post?

The script requires you have Numbers installed, that a Numbers document is open when the script is executed, that a sheet in that document is selected, and that the sheet does not contain a table whose name conflicts with the name of the table created by the script. The script will fail if any of those conditions is false.

“Unexpected end of line but found identifier” usually means you’ve somehow either not copied the code correctly, or have inserted some character (possibly an control character) into the code that the compiler cannot parse. Again, it’s not a fault with the script posted here. User error most likely.

Here is a compiled copy of the script. Gatekeeper might prevent you from opening it, however.

Export an index of annotations.scpt.zip (14 KB)

Ok, I’ve installed Numbers, and successfully compiled and saved the script! But for some reason I haven’t had success getting the script to work. I’ve looked over the internal documentation in the script and the instructions in the original post, but I’m overlooking some crucial step.

To recap, I’ve done the following:

(1) I open a Numbers spreadsheet – in fact, I’ve even saved it in the same DTP DB – with file column
(2) Select annotations. (I’ve tried to include the source documents, but it’s been hard to identify the exact source document that corresponds with the annotations.) So, for now, I’ve just selected the annotation documents.
(3) Then I run script
(4) I choose a name for the table to be created in Numbers

But then…nothing happens. When I select the annotation documents first, then Numbers document, and then run the script, it generates activity in Numbers - but it doesn’t process the selected annotations.

Anyway, please feel free to let me know what I’m doing wrong. Thanks!


Just wondering if I could get any more guidance on how to execute this process properly and/or an assessment of what I’m doing wrong, based on what I detailed above - so I can correct the process.

Thanks very much.

I have attached a sample database which definitely works including a numbers spreadsheet of what the result should look like. There is nothing special about the construction of the database.

The script simply looks through the selected files and extracts information from each document. If the file has a url which ends in the form ?page= then it assumes that the file is an annotation and includes a reference to the original document and the page number in the export to numbers. The script does nothing with files that do not have a url ending in ?page= except export the file name to numbers.

I personally keep annotations in a separate folder but even if you don’t simply select all the documents that might be relevant. The spreadsheet will show you which documents are annotations and the rest can be deleted from the spreadsheet.

I hope this helps.
export to numbers test.dtBase2.zip (128 KB)

I want to use this script with Devonthink v.3 with Numbers 5.2, but the script only insert a table with 2 rows. Which first row is heading and the second is the title of file. I can extract annotations with new feature in Devonthink 3: Summarize Highlights in Tools menu. So there isn’t problem in source file.
Also I can’t download the zip files because they are for 3 years ago.