How can I automatically fill in with the current date when a value in one of the columns changes, BUT so that this value will never be refilled?

Now I did this:

=IF(A69<>"", TODAY(), "")

but every time the sort is applied, the date is refilled to today.

  • The formula can not change the values ​​of other cells. The formula can not change itself. In my opinion, the problem is not solved. - Akina
  • I didn’t quite understand: (Forormula inserts the value into its cell (where it is) by the event that the other cell A69 changes. The problem is that when you change the sorting, the formula is restarted and the old data is refilled (See? Ivan Kucherov
  • The problem is that I understand perfectly. when the sorting is changed, the formula is restarted and the old data is refilled True. To avoid this, you need to replace the formula in the cell with its value. But this - changing itself from the formula to the value - it can not do. The solution is only one - VBA-procedure, in terms of Excel - a macro. - Akina
  • Is there a Macro in google spreadsheet? It seems that I had heard about some scripts executed in them. - Ivan Kucherov

1 answer 1

This can only be done using Google Apps Script:

 function myFunction(e) { try { if (!e || !e.range) return; if (e.range.columnStart > 1) { var targetCell = SpreadsheetApp.getActiveSheet().getRange(e.range.rowStart, 1, e.range.rowEnd - e.range.rowStart + 1); /* if you want insert the date once uncomment the row below * **/ // if(!targetCell.getValue()) targetCell.setValue(new Date()); } } catch (err) { SpreadsheetApp.getActiveSpreadsheet().toast(err.message, 'Error!!1'); } }

Assign myFunction trigger function of the EDIT event.

Working example and description https://gist.github.com/oshliaer/b179e819c0ee0f44c37bba7cfbfd4f54