How to create timestamp of last change in online spreadsheat?

Cloud
,
1

ONLYOFFICE cloud: Free
Do you want to Ask a how-to question

Hello,

I am looking for a way how to automatically create a timestamp of (last) change in the cell next to the cell changed.

In excel I used this formula =IF(A2<>“”,IF(B2<>“”,B2,NOW()),“”) but it needs iterative calculation.

So is there any other way? macro?

2

Hello @tkoutny

You can use this macro to insert timestamp into next cell by specific columns (one to listen and another to paste):

(function()
{
    const data = new Date();
    Api.attachEvent("onWorksheetChange", function(range) {
        timestamp(range);
    });
 
    function setdata(addr){
        let ws = Api.GetActiveSheet();
        
        let h = data.getHours();
        let m = data.getMinutes();
        let d = data.toDateString();
 
        let value = `${ h }:${ m } ${ d }`
    
        ws.GetRange(addr).SetValue(value);
        ws.GetRange(addr).AutoFit(false, true);
    }
    function timestamp(range) {
        let column = range.GetCol()
        let row = range.GetRow()
        
        if (column == 1 && row > 1) {
            let addr = 'C' + (row)
            setdata(addr);
        }
        if (column == 3 && row > 1) {
            let addr = 'D' + (row)
            setdata(addr);
        }
    }
})();

In this example, editor listens for changes in column C and pastes timestamp into cell to the right in column D.
You can change the column to listen for changes in and column to paste date and time by changing respective letters in these strings:

  • for column to listen in let addr = 'C' + (row)
  • for column to insert timestamp in let addr = 'D' + (row)

To make it run automatically do not forget to set the macro to autostart.