Your Script Doctor

Now to style the paragraphs. Hopefully you have set up your document using paragraph styles. At this point we will just style the paragraphs manually.

PROOF OF CONCEPT

Now that we have worked through the process manually, we can write a Proof of Concept script to make sure the script will work when we replace the manual steps above with code. We will have the user define the text frame for importing the table:

set fileRef to choose file
tell application "Adobe InDesign CC 2019"
	set selList to selection
	set itemRef to item 1 of selList
	tell itemRef to place fileRef
	set tableRef to table 1 of itemRef
	tell tableRef to convert to text column separator tab row separator return
	set storyRef to story -1 of document 1
	tell storyRef to delete (paragraphs 1 thru 2)
end tell

Running this script with a sample Excel file gives us enough information that we can go ahead and write our script. But, even with this we will start simple. For testing, our data file will have only one item to start with. Our document will only have one page with a Primary Text Frame. We will have the user select the text frame for placing the file. The script will test to make sure a text frame is selected as part of a handler routine.

set itemRef to getFrameSelection()
(*Returns text frame reference if first item of selection*)
on getFrameSelection()
	tell application "Adobe InDesign CC 2019"
		set selList to selection
		if selList is {} or class of item 1 of selList is not text frame then
			error "Requires text frame to be selected"
		else
			set itemRef to item 1 of selList
		end if
	end tell
end getFrameSelection

With the text frame selection verified, the script will have the user select the file. Again, a handler is used with the text for a user prompt and file extension passed to it as part of its call:

--Get Excel file
set promptStr to "Select Excel file to import"
set fileExt to "xls"
set fileRef to getFileRef(promptStr, fileExt)
(*Returns alias reference to file if file extension for file chosen is as specified*)
on getFileRef(promptStr, fileExt)
	set fileChoice to choose file with prompt promptStr
	set fileInfo to info for fileChoice
	if name extension of fileInfo is not fileExt then
		error "Requires Excel file with file extension of " & fileExt
	end if
	return fileChoice
end getFileRef

PUTTING THE SCRIPT TOGETHER

With the code for getting the text frame and file verified, we can combine the two to place the file and convert the table to text. To add to this we will develop a handler to place the file. Adding a call to a handler named placeTableToText to the top section, the script will now read as follows:

set promptStr to "Select Excel file to import"
set fileExt to "xls"
set itemRef to getFrameSelection()
set fileRef to getFileRef(promptStr, fileExt)
set storyRef to placeTableToText(itemRef, fileRef)
theResult

The handler (added to the script):

on placeTableToText(itemRef, fileRef)
	tell application "Adobe InDesign CC 2019"
		tell itemRef to place fileRef
	end tell
	set storyRef to story -1 of document 1
	return storyRef
end placeTableToText

Make sure you also have the getFrameSelection and getFileRef handlers from above at the bottom of the script. With your document open and the Primary Text Frame selected, you should be able to run the script to have the table placed in your document. If you get an “Import Failed” error, try the script with a new document. We have received this error with some of our tests but have yet to define exactly what causes the problem.

Once satisfied that the script is ok at this point, we can flesh out the placeTableToText handler to delete the first two lines of text placed (table head and column heads) and change tabs to returns. This requires another handler for finding and changing text.

(*Place Excel file, convert to text, and change tabs to returns*)
on placeTableToText(itemRef, fileRef)
   tell application "Adobe InDesign CC 2019"
       tell itemRef to place fileRef
       set tableRef to table 1 of itemRef
       tell tableRef
	   convert to text column separator tab row separator return
       end tell
       set storyRef to story -1 of document 1
       --delete first two paragraphs
       tell storyRef to delete (paragraphs 1 thru 2)
       set findText to tab
       set changeText to return
       set foundSet to my findChangeText(storyRef, findText, changeText)
    end tell
    return storyRef
end placeTableToText
(*Returns reference list of text changed*)
on findChangeText(objRef, findText, changeText)
   --clear find text preferences
   tell application "Adobe InDesign CC 2019"
       set find text preferences to nothing
       set change text preferences to nothing
       --set properties of find change text 
       set find what of find text preferences to findText
       set change to of change text preferences to changeText
       tell objRef
	   set textChanged to change text
       end tell
       set find text preferences to nothing
       set change text preferences to nothing
   end tell
   return textChanged
end findChangeText

For this test, we have added items to our Excel file. Because we created the document with a Primary Text Frame, pages are added to accommodate the number of items. (We only have 16 items in our test data file, but the process should scale up with any number of items–there must be a limit.)

All that is left at this point is to style the paragraphs.

STYLING PARAGRAPHS USING NEXT STYLE

If you use Next Style to set up paragraph styling, the StyleText handler can use a looping structure to style the paragraphs. Note: This code only works if each paragraph style in the group assigns the next one as its next style. This includes the last style which now must use the first style as its next style–can’t be Same Style or No Style.

We now add a call to a handler to style the text with a reference to the story passed together with the name of the first paragraph style in the looping group.

set firstName to "name"--name for first paragraph style of group
styleText (storyRef, firstName)
(*Styles text using paragraph styles having Next Style defined; last style has the first style as its next style*)
on styleText (storyRef, firstName)
   tell application "Adobe InDesign CC 2019"
       set paraCount to number of paragraphs in storyRef
       set theCount to 1
       set firstStyle to paragraph style firstName of document 1
       copy firstStyle to thisStyle
       repeat with i from 1 to paraCount
	   tell paragraph theCount of storyRef to apply paragraph style using thisStyle without clearing overrides
	   set nextStyle to next style of thisStyle
	   copy nextStyle to thisStyle
	   set theCount to theCount + 1
	end repeat
   end tell
end styleText

THE FINAL SCRIPT

Putting all of the pieces together the top portion for the final script now reads as follows. You will need to make sure all the handlers are included at the bottom of the script (getFrameRef, getFileRef, placeTableToText, findChangeText, and styleText)

set promptStr to "Select Excel file to import"
set fileExt to "xls"
set firstName to "name" --name of first paragraph style 
set itemRef to getFrameSelection()
set fileRef to getFileRef(promptStr, fileExt)
set storyRef to placeTableToText(itemRef, fileRef)
styleText (storyRef, firstName)