Numbers Table to Keynote Chart

Using AppleScript to transfer and transform spreadsheet data into a chart in a presentation.

DO THIS ►

DOWNLOAD the installer for the scripts and files related to the example shown below. Unpack the archive and then “right-click” (hold down Control key) on the “Automation Tools Installer” applet and choose “Open” from the contextual menu. The installer will active the system-wide script menu and install the Numbers AppleScript scripts automatically for you.

tell application "Numbers" tell the first table of the active sheet of the front document set the columnTitles to the formatted value of cells 2 thru -1 of the first row of the cell range set the rowTitles to the formatted value of cells 2 thru -1 of the first column of the cell range set tableData to {} repeat with i from 2 to the count of rows set the end of the tableData to the value of cells 2 thru -1 of row i end repeat end tell end tell tell application "Keynote" tell the current slide of the front document add chart row names rowTitles column names columnTitles ¬ data tableData type vertical_bar_2d group by chart row end tell end tell
applescript://com.apple.scripteditor?action=new&script=tell%20application%20%22Numbers%22%0A%09tell%20the%20first%20table%20of%20the%20active%20sheet%20of%20the%20front%20document%0A%09%09set%20the%20columnTitles%20to%20the%20formatted%20value%20of%20cells%202%20thru%20%2D1%20of%20the%20first%20row%20of%20the%20cell%20range%0A%09%09set%20the%20rowTitles%20to%20the%20formatted%20value%20of%20cells%202%20thru%20%2D1%20of%20the%20first%20column%20of%20the%20cell%20range%0A%09%09set%20tableData%20to%20%7B%7D%0A%09%09repeat%20with%20i%20from%202%20to%20the%20count%20of%20rows%0A%09%09%09set%20the%20end%20of%20the%20tableData%20to%20the%20value%20of%20cells%202%20thru%20%2D1%20of%20row%20i%0A%09%09end%20repeat%0A%09end%20tell%0Aend%20tell%0A%0Atell%20application%20%22Keynote%22%0A%09tell%20the%20current%20slide%20of%20the%20front%20document%0A%09%09add%20chart%20row%20names%20rowTitles%20column%20names%20columnTitles%20%C2%AC%0A%09%09%09data%20tableData%20type%20vertical%5Fbar%5F2d%20group%20by%20chart%20row%0A%09end%20tell%0Aend%20tell

 01-10  A “tell block” addressing the Numbers application. All of the script statements within the opening and closing tell statements are targeting the Numbers application.

 02-09  A “tell block” addressing the first table on the active sheet of the front document of the Numbers application. All of the script statements within these opening and closing tell statements are targeting the first table of the active sheet of the frontmost document in the Numbers application.

 03  Extract the titles of the table columns by getting the contents of the cells in the header row and then store the resulting list of column titles in the variable: columnTitles

 04  Extract the titles of the table rows by getting the contents of the cells in the header column and then store the resulting list of row titles in the variable: rowTitles

 06-08  A repeat loop is used to iterate the rows of the table in order to extract the contents of the non-header cells. The resulting array of lists is stored in the variable: tableData

 12-17  A “tell block” addressing the Keynote application. All of the script statements within the opening and closing tell statements are targeting the Keynote application.

 13-16  A “tell block” addressing the current slide of the front document of the Keynote application. All of the script statements within these opening and closing tell statements are targeting the current slide of the frontmost document in the Keynote application.

 14-15  The add chart command is used to create a new chart using the extracted table data previously stored in the variables: columnTitles, rowTitles, and tableData NOTE: the character ( ¬ ) is used to indicate that the script statement is wrapped to the following line.

EXTRA: macOS Mojave Touch Bar Quick Action

Here’s a link to a variation of the example script that will create a new pie chart in Keynote for either the selected row or column in a Numbers table. The script has been placed in a Run AppleScript action in a macOS Mojave Automator Quick Action workflow which can be accessed from the MacBook Pro Touch Bar:

UNDER CONSTRUCTION

This webpage is in the process of being developed. Any content may change and may not be accurate or complete at this time.

DISCLAIMER