Tutorial 20: Online Protocol Collection using OpenOffice to Increase Efficiency

Instead of using a clip board, paper and a pen, protocol taking is also possible with the OpenOffice software running on the Linux systems in most of the labs. There are a few options- spreadsheet format with OpenOffice Calc, document/table formats with OpenOffice Write and so on. The bottom-line reason to be using this as an option for protocol taking is that you can simply convert the file into a pdf and save it in the directory with the actual data files from the experiment, so you have just saved the time it takes to 1) scan the protocol into a pdf, 2) transfer it to a place where it is stored together with the data and 3) look for the protocol info in a binder several weeks after the experiment was done. In addition, today´s students are better at typing than handwriting.

So, here, we will give an example of how the spreadsheet option is used for protocol collection in our lab and highlight a few recently implemented features to show how protocol taking has been simplified a bit.

It is very easy to use the OpenOffice files, as they are similar to Microsoft Excel xls files. You can create a custom template with headers and layouts that fit your experiments. Below is an example of the layout that we use in our lab.

Recommendations

Enter all relevant information that you may need later. Take advantage of lulls in activity to record what´s been done before you forget. Try to stick to the standard format and terminology that´s established for your lab, so your protocols are recorded reasonably consistently from one experiment to the next. Review your protocol sheet at the end of the experiment to fill in any details you may have missed in the course of the experiment.

Time Recording

One of the most useful features of protocol taking in a spreadsheet is writing in the date and time of recording. This has been automated by a handy macro, activated by a single click. In the Calc program, there is a clock icon in the toolbar. Simply clicking on it enters the current time in the cell you are in. Then we enter our protocol info at that time point, and move on. This avoids problems arising from clocks not working in a synchronized way, or different note-takers looking at different clocks in the lab. Everything is synchronized to the data capture computer´s internal clock, including the capture start time now recorded by the capture program itself.

As of the 2015 release of the SCRC capture software, the capture program now records the time at which data capture started for each run. The start time is recorded and saved in the run file descriptor. Previously, users had to estimate capture time based on the file modification time for the frame file or waveform files, but this was unreliable as the modification time could change after capture, when filtering waveforms, changing calibration, or making any other changes to a run. The new version records it more permanently, and it can be displayed by analysis, frmsel or dumprun. This capture time can be used to refer to entries in the protocol, e.g. to see what drugs were administered prior to a particular run of captured data.

Time Recording in OpenOffice - How it´s done:

We started with a simple trick we discovered online, for entering the current time in an OpenOffice spreadsheet.

If you would like to know the details about this - here it is: =n <Enter> <F9> <Enter> <Enter> (6 keystrokes). The sequence "=n<Enter>" seems to pick "=now()" by default, so you don't need to enter the full function name, if you just use the extra <Enter> to pick the default. The F9 recalculates, so it changes the =now() function into its current value and enters that in the current cell. (If you skip the F9, you end up with a cell whose time keeps updating to the current time.) You can also change the final <Enter> to a <Tab> if you want to move to the right rather than down after entering the time in the cell.

Some online tips also describe how to enter a function in a Calc spreadsheet to reduce the number of keystrokes needed each time you enter the time, but that requires setup for each spreadsheet, and entering the time still involves a cryptic sequence of keystrokes. Not content to leave it at that, we set out to find something even easier to use, with a single click rather than a hard to remember key sequence. We also worked out a way to add this clickable toolbar icon to both Writer and Calc, so you´d have access to it regardless of which application you choose for writing your protocols. This has already been done on the Linux capture computers at the SCRC, but we explain it here so other labs can implement it...

OpenOffice Writer implementation

Adding an Insert Time feature to Open Office Writer is easy, as most of the work is done already.  Start Writer (Applications->Office->OpenOffice.org Writer), and you can see there's an entry in Writer's menu for this: Insert->Fields->Time.

But to make things much quicker and easier, you can add a toolbar button or keyboard shortcut to do this with one click or keystroke combination.  First, decide where you want to add the toolbar button.  A good place is in the cut-copy-paste section, such as right after the Format Paintbrush tool.  Find the Paintbrush tool and right-click on it, and select "Customize Toolbar..." from the context menu.  In the Customize dialog box, the Toolbars tab should be selected, and the Toolbar field set to Standard.  Scroll down the list of Commands in the Toolbar Content section of the dialog box, and select the Format Paintbrush there, then click the "Add..." button to the right.  In the Add Commands dialog box, select the Category called Insert, then scroll down to the end of the Commands and select Time, and click Add and then Close.

If you wish to add a keyboard shortcut for this feature, click on the Keyboard tab in the Customize dialog box, and scroll down the Shortcut keys section to find an unused key combination that you'd like to use.  A good choice for this is Ctrl+Insert (about 2/3 the way down).  Click on this entry to select it, then under Functions, select the Category called Insert, and the Function called Time (at the bottom of the list).  Then click on the Modify button to the right, and the selected key combination should show up in the Keys section at the bottom right of the dialog box.  Click OK to close the Customize dialog.

Now, when entering text in a Writer document, you can click the new toolbar button or use the new key combination to enter the current time at the insertion point.

OpenOffice Calc implementation

Adding an Insert Time feature to Open Office Calc (the spreadsheet program) is a bit more work, but well worth the effort if you use the spreadsheet for logging, such as when tracking your experiment protocol during the preparation or data capture.  Start Calc (Applications->Office->OpenOffice.org Calc), and you can see there is no similar entry in Calc's Insert menu.  So, you must begin by adding an OpenOffice macro to do this: select Tools->Macros->Organize Macros->OpenOffice.org Basic... from the Calc menu bar.  The Basic Macros dialog box should show, under Macro from, the Module1 macros in My Macros->Standard. Select this if not already selected.  In the section titled "Existing macros in: Module1", select Main, then click the Edit button on the right.  Then copy the following code from here, and paste it into the "My Macros & Dialogs" editor window, just after the final "End Sub" line (after "Sub Main"):


sub InsertTime
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
dim ct as Date
ct = Now()
args2(0).Name = "StringName"
args2(0).Value = Format(ct, "yyyy-mm-dd HH:MM:SS")

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args3())

end sub


After pasting in this code, select File->Save (or Ctrl+S), and close the editor window.  Now you can use this macro for the toolbar button and keyboard shortcut.  As for the Writer tool bar above, decide where you want to add the button.  Again, a good place is in the cut-copy-paste section, such as right after the Format Paintbrush tool.  Find the Paintbrush tool and right-click on it, and select "Customize Toolbar..." from the context menu.  In the Customize dialog box, the Toolbars tab should be selected, and the Toolbar field set to Standard.  Scroll down the list of Commands in the Toolbar Content section of the dialog box, and select the Format Paintbrush there, then click the "Add..." button to the right.  In the Add Commands dialog box, scroll down to the bottom of the Category list, click the triangle next to OpenOffice.org Macros, then the one next to My Macros, and finally the one next to Standard, and select Module1.  In the Commands list, select InsertTime, and click Add and then Close.  Unlike the built-in selection in Writer, this one doesn't have an icon associated with it, so we'll add one now: make sure the InsertTime function is still selected in the Commands list, click the Modify button, then select "Change Icon...".  In the Change Icon dialog box, scroll down (once should do it) until you see the little clock icon with the blue border, as you had seen for the Insert Time toolbar button in Writer.  Select it and click OK.

If you wish to add a keyboard shortcut for this feature, click on the Keyboard tab in the Customize dialog box, and scroll down the Shortcut keys section to find an unused key combination that you'd like to use.  A good choice for this is Ctrl+Insert (about 2/3 the way down).  Click on this entry to select it, then under Functions, scroll down to the bottom of the Category list, click the triangle next to OpenOffice.org Macros, then the one next to "user", and finally the one next to Standard, and select Module1.  In the Function list, select InsertTime, then click on the Modify button to the right, and the selected key combination should show up in the Keys section at the bottom right of the dialog box.  Click OK to close the Customize dialog.

Now, when entering data in a Calc spreadsheet, you can click the new toolbar button or use the new key combination to enter the current time in the current cell.  The macro will automatically move to the cell to the right, so you can make an entry for the entered time. The first time you use this in a new sheet, the time will not be formatted correctly: it will likely show up as "###" because the column width is too narrow.  Click and drag the right-edge of the column heading to expand the column, and the date and time will show up.  If you want more precision (seconds rather than minutes) in the displayed time, select the column and then select Format->Cells... from the menu bar.  Select Time from the Category list, then choose the format that suits you.

You can also add another toolbar button to make quick work of selecting time format for a cell or column of cells: note that the toolbar under the standard one (with cut-copy-paste) features a section with other formatting options (currency, percent, standard, add/delete decimal place).  Right-click on the last of these, and select "Customize Toolbar..." from the context menu.  In the Customize dialog box, the Toolbars tab should be selected, and note that this time the Toolbar field should be set to "Formatting". Scroll down the list of Commands in the Toolbar Content section of the dialog box, and select the last of the "Number Format" commands there, then click the "Add..." button to the right.  In the Add Commands dialog box, select the Format entry in the Category list, then select "Number format: Time" from the Commands list (about 3/4 the way down), and click Add and then Close.  Again, unfortunately there is no icon for the button, so we add one: with "Number Format: Time" still selected in the Commands list of the Customize dialog box, click the Modify button and select "Change Icon...".  This time scroll down to the bottom of the collection of icons, and select the other clock icon (white with a black border).  The mouse-over text for this icon shows it as ".uno:TimeField".  Select it and click OK.  Click OK again to close the Customize dialog.

Now, you can quickly format a cell or column for the standard time format.  Note that with this button, it will show time only, without the date, so you won't need to widen the column.  However, if you prefer that both date & time are displayed, you can always widen the column and select a different time format as above.  The date & time are both stored, whether they're both displayed or not, so you can go back and forth between different formats without losing any stored information.