## [Calc,Python,Basic,Base] Several ways to time stamps

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

### [Calc,Python,Basic,Base] Several ways to time stamps

 Edit: 2009-03-28: How to insert a time stamp into Calc without any macro

Type =NOW() without entering the formula. Hit F9 to calculate the formula right on the input line. Then hit Enter twice (confirm and enter).
Using a shortcut to NOW() (a tiny little bit shorter):
Name: n
Refers to: NOW()
Type into a cell: =n F9,Enter,Enter

NOW() returns the full date and time info as a formatted number. Having such a full stamp in a A1, =INT(A1) returns the day-portion, MOD(A1;1) returns the time-fraction only. You can use number formats to display only the date or the time without changing the value.

 Edit: 2010-12-29: How to insert a time stamp depending on another cell value into Calc without any macro

menu:Tools>Options...OOoCalc>Calculate: Enable iteration with 1 step
Code: Select all   Expand viewCollapse view
`Cell B1: =IF(A1="";"";IF(B1="";NOW();B1))`

returns the current time when A1 is not empty nor empty string.
Thanks to this forum's most valued member Robert Tucker pointing us to http://www.mcgimpsey.com/excel/timestamp.html

Several macros to write time stamps into spreadsheet cells. It writes naked numeric values, leaving all formatting up to you. Straight ahead, no gimmicks, no error-handling. Just tested against the assumed scenarios.

1. NowToActiveCell
Writes the time stamp to the currently active input cell. Best used with a shortcut.

2a. NowToTimeStampCell
Writes the time stamp to a named cell "TimeStamp" anywhere in the current document.
2b. NowToEmptyTimeStampCell
Same as above, leaving alone an already filled stamp-cell.

3.a NowToColumnAByValidation
Triggered by a failed validation. Writes the time stamp to the same row's column "A" when a validated cell has been modified. Set validation to "Text Length = 0" with action set to "Macro". The validation fails, when you put something into the cell (length becomes >0), which triggers the macro.
3b. NowToEmptyColumnAByValidation
Same as above, leaving alone an already filled stamp-cell.

Python Install
Single user:
Does not work with version 3.0. Use the application wide directory until the release of 3.0.1
<user profile>/user/Scripts/python/

Application wide:
v2.x <install dir>/share/Scripts/python/
v3.0 <install dir>/basis3.0/share/Scripts/python/
Symlinks do work under Linux, so you can create links to files in any directory.

Basic Install
Re: [Tutorial] How to install a code snippet

 Edit: 2008-12-21 Replaced Python attachment. It can be called from a menu or button now.

 Edit: 2010-09-24: Simplified the Python module, added NowToRightNeigbour

Basic version:
Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****REM I won't update this Basic code. You may refer to the latest PythonSub test_splitStringAddress()REM instead of splitting the string address you may simply use the active cell,REM ignoring the strings from the validation trigger. See updated Python codeDim s\$,sSh\$,sRg\$   a=Array("'my sheet'.A1", _   "\$'my sheet'.A", _   "'file:///doc.ods'#'my sheet'.A1", _   "\$'file:///doc.ods'#\$'my sheet'.A")   for i= 0 to uBound(a())      s = a(i)      splitStringAddress(s,sSh,sRg)      print sSh,sRg   nextEnd Subsub splitStringAddress(s\$, sSheet\$, sRange\$)dim sURL\$, linkPos%, sheetPos%   ' remove leading \$ in any case:   if left(s, 1) = "\$" then s = mid(s, 2)   linkPos = instr(s, "'#")   if linkPos > 0 then      sheetPos = instr(linkPos, s, ".")      sURL = left(s, linkPos +1)      sSheet = mid(s, linkPos +2, sheetPos -linkPos -2)      sRange = mid (s, sheetPos +1)   else      sheetPos = instr(s, ".")      if sheetPos > 0 then         sSheet = left(s, sheetPos -1)         sRange = mid (s, sheetPos +1)      else      sRange = s      endif   endif   if left(sSheet, 1) = "\$" then sSheet = mid(sSheet, 2)   if (left(sSheet, 1) = "'") and (right(sSheet, 1) = "'") then      sSheet = mid(sSheet, 2, len(sSheet) -2)   endif   sSheet = sURL & sSheetend sub'-------usefull helper-function, returning focussed cell 'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348REM 2006-08-09: fixed error when row > 8191'       ;sh;                     ;lSheet +3'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0Function getActiveCell(oView)Dim as1(), lSheet&,lCol&,lRow\$, sDum as String,bErr as Boolean   as1()  = Split(oView.ViewData, ";")   lSheet = CLng(as1(1))   sDum = as1(lSheet +3)   as1() = Split(sDum, "/")   on error goto errSlash      lCol = CLng(as1(0))      lRow = CLng(as1(1))   on error goto 0   getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)exit FunctionerrSlash:   if NOT(bErr) then      bErr = True      as1() = Split(sDum, "+")      resume   endifEnd FunctionSub printStamp(oCell)    '''Put the current time into the active cell.    'No formatting intended. Apply any date/time formatting you like.'''    oCell.setFormula("=NOW()")    oCell.setValue(oCell.getValue())End SubSub _NowToColumnAByValidation(sAddr, bOverwrite)Dim s\$,r\$    iCol = 0    doc = thisComponent    splitStringAddress(sAddr,s,r)    sh = doc.Sheets.getByName(s)    rg = sh.getCellRangeByName(r)    iRow = rg.RangeAddress.StartRow    c = sh.getCellByPosition(iCol, iRow)    if bOverwrite or (len(c.getFormula())=0)then printStamp(c)End SubSub _NowToTimeStampCell(bOverwrite)    oNames = thisComponent.NamedRanges    oName = oNames.getByName("TimeStamp")    oRg = oName.getReferredCells()    c = oRg.getCellByPosition(0,0)    if bOverwrite or (len(c.getFormula())=0)then printStamp(c)End SubSub NowToEmptyColumnAByValidation(sFormula, sAddr):    '''Current time to empty column A, triggered by failing validation'''    _NowToColumnAByValidation(sAddr, False)End SubSub NowToColumnAByValidation(sFormula, sAddr):    '''Current time to column A, triggered by failing validation'''    _NowToColumnAByValidation(sAddr, True)End SubSub NowToTimeStampCell():    '''Put current time into a cell named "TimeStamp"'''    _NowToTimeStampCell(True)End SubSub NowToEmptyTimeStampCell():    '''Put current time into an empty cell named "TimeStamp"'''    _NowToTimeStampCell(False)End SubSub NowToActiveCell():    '''Put current time into the currently active input cell'''    oDoc = thisComponent    oView = oDoc.getCurrentController()    oCell = getActiveCell(oView)    printStamp(oCell)End Sub`

 Edit: 2009-03-28: Adding a link to another approach involving c.s.s.util.XModifyListener. Not as easy to implement since the listener needs to be attached to the respective range(s). Trouble with race conditions when used with volatile functions such as NOW(). This is why I suggest the NowToColumnAByValidation with the validation-trick: Re: Run a python macro when cell contents change

Attachments
TimeStamp2Calc.py.zip
Last edited by Villeroy on Fri Nov 04, 2016 8:07 pm, edited 20 times in total.

Villeroy
Volunteer

Posts: 29501
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: [Calc,Python,Basic] Several ways to time stamps

The time-stamp feature is typical for a database such as counter.odb
Databases of all kinds and derived record sets integrate perfectly in Calc, even better than values directly stored in the spreadsheet.

Testing the counter.odb with a spreadsheet:
Open the form therein.
Enter a few integer numbers (other values will be rejected).
There is no need to store anything. Databases write records to disk.
Select "Tables" in the main database window.
Open "Stamps" and see the time stamps and auto-IDs (to be used in further, interrelated tables).

Call menu:Tools>Options...OOo Base>Databases and register the database document.
Call the beamer [key F4]
Browse the database>Tables>Stamps
Drag Stamps from the left pane into the spreadsheet.
Select "Import1"
and check options
[X] Insert/delete cells
[X] Keep formatting

Enter some formula in D2 (directly beneath the first "Value"), say =D2*2 and copy down the formula.
Apply any number formats you like to see in the import range. All values are guaranteed to be integers and date-times without gaps.

Edit some values using input form, the table or the beamer (invalid values and duplicate IDs will be rejected, the time stamps can be overwritten).

Add some new integers into field "Value" using input form, the table or the beamer.

Delete some records.

Auto-resizing lists, including chart ranges and adjacent formulas, together with enforced consistancy, the ability to get arbitrary filtered and recombined data sets in arbitrary order of rows and columns, the fact that all data are stored in one place but usable in all Writer and Calc documents, including data pilots in Calc, without a single line of macro-code. All this can't be done using spreadsheets as the only tool.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29501
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: [Calc,Python,Basic] Several ways to time stamps

Starting with the Calc-problem and a simplistic macro-solution, turning the view to a completely different approach (separation of storage and calculation model), now for one of the most frustrating shortcomings in OOo Base. The table designer in Base can not add time stamps as default values even though this is supported by the underlying HSQLDB.
Design your table without the stamp.
Code: Select all   Expand viewCollapse view
`ALTER TABLE "TableX" ADD COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL BEFORE "X";`

For an already existing field that is:
Code: Select all   Expand viewCollapse view
`ALTER TABLE "TableX" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;`

I used the double-quoted object names as follows:
"TableX" is the table in question.
"Stamp" the name of the stamp field to be added.
"X" is the name of another field. Leave out the clause BEFORE "X" in order to append the column.
NOT NULL makes the field mandatory so you can not delete an existing stamp (it can be edited though).
Use NOT NULL if and only if the table has no data yet, otherwise the insertion of this column will fail.
Using a form bound to a table with a time stamp, such as the above linked counter.odb, requires that you unset the following option:
db-menu:Edit>Database>AdvancedSettings... "Form input checks for required fields". Otherwise the form will reject to store the record with the missing date-value. With this option unset, the underlying HSQLDB takes care of the integrity, adding the field's default value.

Currently, I don't see any option to implement time stamps that update with every change of the record. Within Base this requires a simple macro to be installed. Any macro solution applies to input forms only without affecting the underlying database. You could edit a record in the table without triggering any macro.

The stand-alone version of HSQLDB supports triggers which can do all kinds of actions for update, delete and insert events. In the current Base implementation it is impossible to define triggers for the embedded HSQLDB.

But all this is still better than using spreadsheets as storage. Once you've got everything set up and linked, the combination of Base and Calc can solve many of the most frequent list keeping problems in Calc without further intervention.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29501
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany