As anyone who writes HTML knows, routine coding tasks — arduous or not — can rob you of a lot of time you’d rather spend on more-important stuff. Wouldn’t it be great if you could make a three-click process that handled drudgery such as converting tabular data from a Microsoft Excel spreadsheet to an HTML table? Well, you can. Straight out of the box, Bare Bones Software’s BBEdit 6.0 (
) makes HTML coding easy. But we’ll show you how to take it one step further. Using Glossary items and AppleScripts, you can automate BBEdit and dramatically decrease the time you spend on mindless, repetitive tasks.
Glossary items are fragments of frequently used text that you can insert quickly — into your document from BBEdit’s Glossary palette. AppleScripts let you control just about every function of BBEdit’and in BBEdit 6.0, you can run scripts made in Script Editor with just a few clicks. If you combine the two tools, you can create intelligent Glossary items that will change the contents of your document in a flash.
We’ll show you how to use a Glossary item and a script in tandem to turn Excel data — or any tab-delimited content — into an HTML table in no time. But this is only one example of how you can make coding time fly by: these techniques can be applied to almost anything you do frequently using BBEdit 6.0.
JASON SNELL is the editor of Macworld.com and has been coding Web pages since 1992.
1: Set Up for Script-Making
First you’ll need to cut and paste the relevant part of your Excel spreadsheet into BBEdit and set the Script Editor to record your script.
In Excel, select the data (as I’ve done here ((A)) that you want to put into the HTML table, choose Copy from the Edit menu (or press command-C), and switch to BBEdit 6.0. Create a new BBEdit document and paste in the data (B).
The Excel data will translate as tab-delimited text in BBEdit. (An invisible tab character appears after the contents of each cell, and an invisible carriage return appears at the end of each row. Later, you will change this formatting into HTML.)
Launch Script Editor and press the Record button (C)
Select the pasted text in BBEdit (D), and choose Find & Replace from the Search menu.
2: Record Your Script
Convert the text to HTML table code while recording your script. Remember that Script Editor is recording every move you make, so be sure you’ve rehearsed all the steps. If you make a mistake, start over.
In the Find & Replace dialog box, choose Search Selection Only (A), so that just your pasted-in table will be affected. Make sure none of the other boxes is checked.
Because there’s a tab (referred to within BBEdit’s Find & Replace window as
) between each block of data, tell BBEdit to search for all those tabs by typing
in the Search For field (B), and to replace them with closing and opening HTML table tags by typing
in the Replace With field (B).
Click on Replace All (D).
Indicate the end of one table row and the beginning of the next. Search for a carriage return by typing
in the Search For field, and in the Replace With field enter the HTML equivalent:
(for closing the last cell in the row and the first cell of the next row, with a carriage return to make your HTML code more readable). Click on Replace All.
3: Modify and Save Your Script
Now you must put a line in the script that will tell the Glossary item which text to turn into a table.
Switch back to Script Editor and click on the Stop button (A). In the Script window, you’ll discover an AppleScript that will elicit the very steps you’ve just performed (B).
Above the final
line of the script (B), insert this line:
return contents of selection of text window 1.
Click on the Check Syntax button (D), and then choose Save from the File menu.
Name your script (E), and save it in Compiled Script format (F), in this location: BBEdit 6.0: BBEdit Support: Glossary Items: HTML Glossary.html (G).
4: Create a Glossary Item
Now set up a Glossary item that will generate the top and bottom of our table, and will enable the script to convert the Excel data into HTML table format.
Create a new BBEdit document (command-N). Enter the HTML code to create the top and bottom of the table (A).
To run a script from within a BBEdit Glossary item, type:
#script [name of script]#
Save this Glossary item in the same place you saved your script in step 3. Here the item is named “Tab-Delimited to Table.”
5: Try Out Your New Glossary Item
It’s time to put your combination to use — it will save you a lot of time that you’d otherwise spend recoding HTML tables.
Copy a range of cells in an Excel spreadsheet and paste them into a BBEdit file (as you did in step 1).
Select the pasted content (A). In the Glossary window, double-click on the item you created in step 4 (here Tab-Delimited To Table) (B).
Your script and Glossary item will run, and the tab-delimited text you copied from Excel will be replaced with basic HTML table code (B).
Save your file with a name that ends in .html. Open it in your Web browser, and you’ll see that you’ve quickly created a valid HTML table with just two clicks.