How to pass row names to keynote in applescript

> Gives: "Keynote got an error: Can’t make some data into the expected type." number -1700 to item

That's unfortunate. It works for me when I create a spreadsheet with the data you provided.

produces:

That indicates that the source spreadsheet has some difference - either the layout is not the same, or some of the data is not in the form that Keynote can process.

I'm not sure how you expect anyone to debug your script when you don't show your script.

How do we know what you're trying to do? or how you're trying to do it?

In general, it's pretty easy to create and fill out out a Keynote table via AppleScript. This script demonstrates the key steps. Just fill in the details with your data out of Excel:

tell application "Keynote"
	tell slide 1 of document 1
		-- make a new table
		set newTable to make new table
		-- target the table
		tell newTable
			-- set a name (optional, but sometimes useful)
			set name to "My Fancy Table"
			-- move it wherever you like
			set position to {100, 200}
			-- define the number of rows and columns
			set row count to 7
			set column count to 4
			-- set cell values, either by row or column order
			set value of cell 2 of column 4 to "w00t"
			set value of cell 1 of row 6 to "foobar"
		end tell
	end tell
end tell

My Bad. You did say 'chart', but somehow I read 'table'.

That said, seeing the source script helps a lot.

The first thing that stands out is I don't understand your handling of lists.

This line, specifically, makes no sense to me:

			set cols to "{" & {cols} & "}"

This creates a text object consisting of the character { followed by a textual representation of whatever's in cols followed by the character }

To be clear, this is not a list, and the add chart command specifically calls for a list.

The same applies to your rownames variable. The script:

		set rownames to "{" & {"Systolic" & "," & "Diastolic" & "," & "Pulse"} & "}"

Creates a text object with the specific characters:

{Systolic,Diastolic,Pulse}

This is not a list. this is just a string of characters.

To make a list in AppleScript with three items, you would write this as:

set rownames to {"Systolic", "Diastolic", "Pulse"}

This is an AppleScript list containing three text items, and this should be valid as the rownames parameter to the add chart command.

It's a common mistake, probably driven by your attempt to display the data before creating the chart:

			display dialog {cols}

The textual representation of a list is not the same as AppleScript's internal representation.

Since I don't have your source data file, I can't run the entire script, but this looks more valid to me as far as managing lists is concerned:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

tell application "Microsoft Excel"
	tell sheet "Sheet1" of workbook 1
		set commalist to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Z"}
		set final to 4
		set cellvalue to (value of cell "A2")
		
		set rownames to {"Systolic", "Diastolic", "Pulse"}
		
		set finish to 25
		set cols to value of cell "A2"
		set cols to (month of cols as integer as text) & "/" & day of cols as text
		repeat with i from 3 to finish
			if value of cell ("A" & i) = "" then
				exit repeat
			end if
			set celldata to (value of cell (item 1 of commalist & i))
			set celldata to (month of celldata as integer) & "/" & day of celldata
			set cols to cols & celldata
			
		end repeat
	end tell
end tell
tell application "Keynote"
	activate
	tell front document
		tell current slide
			set its transition properties to {transition effect:wipe, automatic transition:false, transition delay:0.5, transition duration:1.0}
			add chart type vertical_bar_2d row names rownames column names cols
			
		end tell
	end tell
end tell

However, there's still nowhere in your script where you're putting any data into the chart, beyond the row and column labels. I suspect that's part of your intent here?

I think we're close - clearly we can create a chart - it's just a matter of extracting the data from Excel in the right format.

Can you share a sample of the Excel data? Then I can probably knock this out, but I'm flying half blind.

I think I found another bug in the logic, but it may be because I'm not working with valid data. It looks like the line:

			set cols to cols & celldata

creates a text object, not a list. That's because the & looks at the class of the first item to determine what it's doing - if the first item is a list, then it appends the second item to the list. If the first item is a text object, then the second item is concatenated to the text. For example:

set myList to {"A"}
set myText to "A"

set newList to myList & "B" --> {"A", "B"}
set newText to myText & "B" --> "AB"

in this case, because myList is a list object, "B" is added as a new list item. Similarly, because myText is a text object, "B" is concatenated.

In your script, cols is a text object because of the line:

		set cols to (month of cols as integer as text) & "/" & day of cols as text

Changing this line to:

		set cols to {(month of cols as integer as text) & "/" & day of cols as text}

(e.g. simply wrapping it in curly braces} turns it into a list, so the line:

set cols to cols & celldata

extends the list in a format that should be compatible with Keynote's data parameter.

I took your sample data and simplified your script a little - for example, given the data you provided, I'm not sure of the purpose of the commalist variable.

In any case, this script takes a spreadsheet of the data as presented and builds a chart of the values:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

tell application "Microsoft Excel"
	tell sheet 1 of workbook 1
		-- read the column headers directly
		-- there may be a better way of doing this, but I'm taking the fast route for now
		set ColumnHeaders to {value of cell "B1", value of cell "C1", value of cell "D1"}
		-- initialise a couple of lists
		set RowHeaders to {}
		set cellData to {}
		set rowNum to 2
		repeat
			-- do we have data in the current row?
			if value of cell ("A" & rowNum) = "" then
				exit repeat
			end if
			-- get the first cell
			set rowDate to value of cell ("A" & rowNum)
			--			do some quick date formatting (note this may (should?) be possible within Excel)
			set rowDate to (month of rowDate as integer as text) & "/" & day of rowDate as text
			-- add the date to the row list
			set RowHeaders to RowHeaders & rowDate
			-- get the data for the cells
			set cellData to cellData & value of cell ("B" & rowNum & ":D" & rowNum)
			-- and move on to the next row
			set rowNum to rowNum + 1
		end repeat
	end tell
end tell

tell application "Keynote"
	activate
	tell the front document
		tell the current slide
			add chart row names RowHeaders column names ColumnHeaders ¬
				data cellData type vertical_bar_2d group by chart column
			
		end tell
	end tell
end tell

Is that closer to your goal?

> As far as date formatting is concerned, I specified Month/Day in Excel's cell format menu, and that's how it shows up on screen. However, when you extract the data, it comes out in long system format, and needs to be massaged to produce an attractive presentation

I see that.

When you ask for the value of a cell, you get the raw, underlying data, regardless of formatting, and you have the right approach in filtering this.

If you're open to using Numbers.app rather than Excel, Numbers has a formatted value property for cells, which returns data in the format it's viewed on the sheet, rather than the underlying value. In this way you can set the format on the sheet and have it transfer through to the presentation.

For example:

tell application "Numbers"
	tell table 1 of sheet 1 of document 1
		get value of cell "A4" --> date "Monday, March 21, 2022 at 12:00:00 AM"
		get formatted value of cell "A4" -- "3/21"
	end tell
end tell

This filters to other fields, too. For example, percentages:

tell application "Numbers"
	tell table 1 of sheet 1 of document 1
		get value of cell "A4" --> 0.456432424
		get formatted value of cell "A4" -- "45.64%"
	end tell
end tell

In this way you can use Numbers formatting to get the data into the format you want to see and have it automatically filter through. Not sure if Excel vs. Numbers is in scope, though.