Adding hyperlinks to text via spreadsheet

Copy link to clipboard
Copied
Hi
I have built a 52 page product document, and now have been asked to produce a PDF version with Hyperlinks on all the 300+ product names.
The client is able to supply a spreadsheet of all the product names and URL in columns (XLS). Is there a way to find the Product Name in the doc and add the URL in to the hyperlinks panel.
thanks
Shane
Copy link to clipboard
Copied
Here is some of the syntax for you. What you will need to add or find someone else to add is some find text function. This worked with a basic test for me. As is… it expects an open document with a text frame containing more words than you have rows in your excel doc. Save your spread sheet as CSV to test it. Hope you manage to get some mileage out of it…
#target indesign function main() { if (app.documents.length == 0) { return; }else{ var doc = app.activeDocument; var csv = selectCSV(); if (csv != null) { var csvArray = readInDelimitedText(csv, ',') for (var i = 0; i < csvArray.length; i++) { // Your Hyperlink name from excel column 1 var hn = csvArray[0]; // Your Hyperlink URL from excel column 2 var url = csvArray[1]; // Not too sure about this method… var find = RegExp(hn,'gi'); if (!inArray(doc.hyperlinks.everyItem().name, find)) { // This needs replacing with some find function? // I only have CS2 better let someone else do this part? // Set a text source var ts = doc.textFrames[0].words; addHyperlink(doc, ts, url, hn); }else{ alert('Hyperlink name is in use…') } } } } } main(); function selectCSV() { if (isMac()) { var csv = File.openDialog('Select an CSV file', function(f) {return (f instanceof Folder)||f.name.match(/\.csv$/i);}); }else{ var csv = File.openDialog('Open Comma-delimited File', 'comma-delimited(*.csv):*.csv;'); } return csv; } function addHyperlink(doc, ts, url, hn) { // Create the Hyperlink var hts = doc.hyperlinkTextSources.add(ts); var urld = doc.hyperlinkURLDestinations.add(url); var hl = doc.hyperlinks.add(hts, urld); // Edit the look of it here… hl.borderColor = [255,0,255]; hl.borderStyle = HyperlinkAppearanceStyle.dashed; hl.highlight = HyperlinkAppearanceHighlight.outline; hl.name = hn; hl.width = HyperlinkAppearanceWidth.medium; hl.visible = true; } function readInDelimitedText(f, d) { var c = Array(); f.open('r'); while(!f.eof) { c.push(f.readln().split(d)); } f.close(); return c; } function inArray(arr, exp) { return exp.test(arr.join('')); } function isMac() { return /(Macintosh)/i.test($.os); }

Copy link to clipboard
Copied
Hi, can anyone help add a repeat loop to this (I forgot to mention I'm more applescript that javascript), as it only work on a single text frame at the moment.
thanks again
Shane

Copy link to clipboard
Copied
Oh and thanks Mark great start!!!
Copy link to clipboard
Copied
With my pretty dated CS2 I was able to simply change…
var ts = doc.textFrames[0].words;
to
var ts = doc.search(hn, true, true);
if (ts.length > 0) { // Just use the first item addHyperlink(doc, ts[0].texts, url, hn); }
And it would do a find based on the contents of column 1 of the Excel spread sheet. These were 'unique' strings in my basic test doc. I did not post this as I believe the find thing in ID has undergone some major changes to what I have. I do think you would need to provide more info on how to find these items within your working document tho… I could have done the same thing in AppleScript but would have only come unstuck at the same issue…

Copy link to clipboard
Copied
Hi Mark
I've tried swapping in your script but it breaks in cs5.
From my knowledge of applescript, find and replace now has a findType, eg. --findType is "text", "grep", or "glyph" (this sets the type of find/change operation to use).
I've attached the FindChangeByList script below as this must use this method…
--FindChangeByList.applescript
--An InDesign CS5 AppleScript
--
--Loads a series of tab-delimited strings from a text file, then performs a series
--of find/change operations based on the strings read from the file.
--
--The data file is tab-delimited, with carriage returns separating records.
--
--The format of each record in the file is:
--findType<tab>findProperties<tab>changeProperties<tab>findChangeOptions<tab>description
--
--Where:
--<tab> is a tab character
--findType is "text", "grep", or "glyph" (this sets the type of find/change operation to use).
--findProperties is a properties record (as text) of the find preferences.
--changeProperties is a properties record (as text) of the change preferences.
--findChangeOptions is a properties record (as text) of the find/change options.
--description is a description of the find/change operation
--
--Very simple example:
--text {find what:"--"} {change to:"^_"} {include footnotes:true, include master pages:true, include hidden layers:true, whole word:false} Find all double dashes and replace with an em dash.
--
--More complex example:
--text {find what:"^9^9.^9^9"} {applied character style:"price"} {include footnotes:true, include master pages:true, include hidden layers:true, whole word:false} Find $10.00 to $99.99 and apply the character style "price".
--
--All InDesign search metacharacters are allowed in the "find what" and "change to" properties.
--
--For more on InDesign scripting, go to http://www.adobe.com/products/indesign/scripting/index.html
--or visit the InDesign Scripting User to User forum at http://www.adobeforums.com
--
tell application "Adobe InDesign CS5"
--Set the user interaction level to allow InDesign to display dialog boxes and alerts.
set user interaction level of script preferences to interact with all
set myTextObjects to {text, text frame, insertion point, character, word, text style range, line, paragraph, text column, cell, table, row, column}
if (count documents) is not 0 then
if (count stories of active document) is not 0 then
set mySelection to selection
if (count mySelection) is not 0 then
if class of item 1 of mySelection is in myTextObjects then
if class of item 1 of mySelection is text frame then
set myObject to object reference of text 1 of item 1 of mySelection
else if class of item 1 of mySelection is insertion point then
set myObject to parent story of item 1 of mySelection
else
set myObject to item 1 of mySelection
end if
my myDisplayDialog(myObject, myTextObjects)
else
set myObject to active document
end if
else
set myObject to active document
my myFindChangeByList(myObject)
end if
else
display dialog ("The current document contains no text. Please open a document containing text and try again.")
end if
else
display dialog ("No documents are open. Please open a document and try again.")
end if
end tell
on myDisplayDialog(myObject, myTextObjects)
tell application "Adobe InDesign CS5"
if class of myObject is insertion point then
set myObject to parent story of myObject
end if
set myDialog to make dialog with properties {name:"FindChangeByList"}
tell myDialog
tell (make dialog column)
set myRangeButtons to make radiobutton group
tell myRangeButtons
make radiobutton control with properties {static label:"Entire document", checked state:true}
make radiobutton control with properties {static label:"Selected story"}
if class of myObject is in myTextObjects and class of myObject is not insertion point then
set myContents to contents of myObject
if myContents is not equal to "" then
make radiobutton control with properties {static label:"Selection", checked state:true}
end if
end if
end tell
end tell
end tell
set myResult to show myDialog
if myResult is true then
set myRange to selected button of myRangeButtons
destroy myDialog
if myRange is 0 then
set myObject to document 1
else if myRange is 1 then
if class of myObject is not story then
set myObject to parent story of myObject
end if
end if
my myFindChangeByList(myObject)
else
destroy myDialog
end if
end tell
end myDisplayDialog
on myFindChangeByList(myObject)
local myFile, myFileRef, myEOF, myFindType
tell application "Adobe InDesign CS5"
set myFile to my myFindFile("FindChangeSupport:FindChangeList.txt")
set myFileRef to open for access (myFile as alias)
set myEOF to get eof myFileRef
set myOldDelimiters to AppleScript's text item delimiters
--That's a tab character between the quotation marks in the next line.
set AppleScript's text item delimiters to {" "}
if myEOF > 0 then
--Delimiter is return--if you have trouble reading your find/change file, it's
--probably because your line end characters are not Mac OS line end characters.
set myText to read myFileRef using delimiter {return}
close access myFileRef
repeat with myCounter from 1 to (count myText)
set myLine to item myCounter of myText
if myLine starts with "text" or myLine starts with "grep" or myLine starts with "glyph" then
if word 1 of myLine is "text" or word 1 of myLine is "grep" or word 1 of myLine is "glyph" then
set {myFindType, myFindPreferences, myChangePreferences, myFindChangeOptions, myDescription} to text items of myLine
if myFindType is "text" then
my myFindText(myObject, myFindPreferences, myChangePreferences, myFindChangeOptions)
else if myFindType is "grep" then
my myFindGrep(myObject, myFindPreferences, myChangePreferences, myFindChangeOptions)
else if myFindType is "glyph" then
my myFindGlyph(myObject, myFindPreferences, myChangePreferences, myFindChangeOptions)
end if
end if
end if
end repeat
end if
set AppleScript's text item delimiters to myOldDelimiters
end tell
end myFindChangeByList
on myFindText(myObject, myFindPreferences, myChangePreferences, myFindChangeOptions)
tell application "Adobe InDesign CS5"
--Clear the find text/change text preferences before each find/change operation.
set find text preferences to nothing
set change text preferences to nothing
set myScript to "tell application \"Adobe InDesign CS5\"" & return & "set properties of find text preferences to " & myFindPreferences & return
set myScript to myScript & "set properties of change text preferences to " & myChangePreferences & return
set myScript to myScript & "set properties of find change text options to " & myFindChangeOptions & return
set myScript to myScript & "end tell" & return
do script myScript language applescript language
tell myObject
set myFoundItems to change text
end tell
--Clear the find text/change text preferences after each find/change operation.
set find text preferences to nothing
set change text preferences to nothing
end tell
end myFindText
on myFindGrep(myObject, myFindPreferences, myChangePreferences, myFindChangeOptions)
tell application "Adobe InDesign CS5"
--Clear the find grep/change grep preferences before each find/change operation.
set find grep preferences to nothing
set change grep preferences to nothing
set myScript to "tell application \"Adobe InDesign CS5\"" & return & "set properties of find grep preferences to " & myFindPreferences & return
set myScript to myScript & "set properties of change grep preferences to " & myChangePreferences & return
set myScript to myScript & "set properties of find change grep options to " & myFindChangeOptions & return
set myScript to myScript & "end tell" & return
do script myScript language applescript language
tell myObject
set myFoundItems to change grep
end tell
--Clear the find grep/change grep preferences after each find/change operation.
set find text preferences to nothing
set change text preferences to nothing
end tell
end myFindGrep
on myFindGlyph(myObject, myFindPreferences, myChangePreferences, myFindChangeOptions)
tell application "Adobe InDesign CS5"
--Clear the find glyph/change glyph preferences before each find/change operation.
set find glyph preferences to nothing
set change glyph preferences to nothing
set myScript to "tell application \"Adobe InDesign CS5\"" & return & "set properties of find glyph preferences to " & myFindPreferences & return
set myScript to myScript & "set properties of change glyph preferences to " & myChangePreferences & return
set myScript to myScript & "set properties of find change glyph options to " & myFindChangeOptions & return
set myScript to myScript & "end tell" & return
do script myScript language applescript language
tell myObject to change glyph
--Clear the find glyph/change glyph preferences after each find/change operation.
set find text preferences to nothing
set change text preferences to nothing
end tell
end myFindGlyph
on myFindFile(myFindFileName)
tell application "Adobe InDesign CS5"
try
set myScript to active script
on error
set myScript to path to me
end try
end tell
tell application "Finder"
set myScript to file myScript
set myParentFolder to (container of myScript) as string
set myFileName to myParentFolder & myFindFileName
set myResult to exists myFileName
if myResult is true then
else
set myFileName to choose file with prompt "Locate your find/change file"
end if
end tell
return myFileName
end myFindFile

