How to set up substitution of the current date when filling in the adjacent cell?

Question

Google

Good afternoon.
There is a table, data is entered into it and it is necessary that there be tracking by input. Due to some circumstances, forms are not an option.
The formula was written:
=IF(D201="акт";"=now()";"wait")
The formula checks if the word act is present in cell D201, then the formula =now() is substituted. The problem is that the formula is inserted into the cell as text. How to make it work?
thanks in advance

Answer 1

Google docs Script to automatically fill in cells
Below is a script to automatically fill the cells of the first and second columns with the date and time, when changing a cell from the third column.
The script works like this:
if an edit is detected in the document,
we check if the cell from the third column has been changed and this change has been made on the sheet called "Sheet1", if yes, we
check if the cell from the first column is empty (if not empty, then the date was already previously affixed, respectively, the time and date do not change).
if it is empty, then
add the date in the cell of the first column in the format: year.month.day;
add the GMT+03 time in the cell of the second column in the format: hour:minutes

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var idCol = e.range.getColumn();
var idRow = e.range.getRow();
if ( idCol == 3 && sheet.getName() =='Лист1' ) {
var Value = e.range.offset(0, -2).getValues(); // Смотрим что в ячейке с лева на 2
if ( Value == "" ) {

var vartoday = getDate();
var varnow = getTime();


sheet.getRange(idRow, 1).setValue( vartoday );
sheet.getRange(idRow, 2).setValue(varnow);
}
}
}

// Returns YYYYMMDD-formatted date.
function getDate() {
var today = new Date();
today.setDate(today.getDate());
//return Utilities.formatDate(today, 'PST', 'yyyy.MM.dd');
return Utilities.formatDate(today, 'GMT+03:00', 'yyyy.MM.dd');
}

function getTime() {
var today = new Date();
today.setDate(today.getDate());
//return Utilities.formatDate(today, 'PST', 'yyyy.MM.dd');
return Utilities.formatDate(today, 'GMT+03:00', 'HH:mm');
}

Found here
and also here

Answer 2

The complexity of the task is that when using formulas, Google docs update it every time synchronization occurs, situations often arise when we need to fix the date when this or that event occurred in one of the cells.
To do this, I used the Vladimir Kalaganov macro and changed it a little.

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var idCol = e.range.getColumn();
var idRow = e.range.getRow();

if ( idCol == 1 && sheet.getName() =='Проверка' ) { 
//указываем место, где будем писать, точнее его номер и имя листа
  var Value = e.range.offset(0, 2).getValues(); // Смотрим что в ячейке справа на 2
  if ( Value == "" ) {
//если там пусто, то пишем дату
    var vartoday = getDate();
    sheet.getRange(idRow, 3).setValue( vartoday );
//показывает где именно писать дату
} 
}
  if ( idCol == 9 && sheet.getName() =='Проверка' ) {
  var Value = e.range.offset(0, 3).getValues(); 
  var Value1 = e.range.offset(0, 0).getValues();
    if ( Value == "" && Value1=="Одобрено") {
    var vartoday = getDate();

    sheet.getRange(idRow, 12).setValue( vartoday );
} 
}

}



// Returns YYYYMMDD-formatted date.
function getDate() {
var today = new Date();
today.setDate(today.getDate());
//return Utilities.formatDate(today, 'PST', 'dd.MM.yyyy');
return Utilities.formatDate(today, 'GMT+07:00', 'dd.MM.yyyy');
}

Answer 3

You can also do it this way.
If you need to put the current date when entering information in the adjacent column, you can use the following formula:
=ЕСЛИ(C6<>"";ТДАТА();)

Answer 4

I use the formula:
The date is inserted as soon as the desired text appears in cell K5. But - a problem!
If I multiply the formula by column and in cells K6, K7, ..., the necessary text appears - all dates are immediately synchronized to the current date.
How to fix so that adjacent dates remain the same?

Answer 5

Hello.
Tell me why these scripts work only from my account (they do not work for others).
How to fix it? What would work for everyone who uses my table. Thank you.
----------
The problem was solved by disabling Chrome V8.
There is another question:
For my table, I simplified the script to this form:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var idCol = e.range.getColumn();
var idRow = e.range.getRow();

if ( idCol == 4 && sheet.getName() =='Time' ) { //Указываем столбец и имя листа для проверки

    var vartoday = getDate();
    sheet.getRange(idRow, 3).setValue( vartoday ); //Столбец для вставки даты
  
}
  
if ( idCol == 10 && sheet.getName() =='Time' ) { //Указываем столбец и имя листа для проверки

    var vartoday = getDate();
    sheet.getRange(idRow, 9).setValue( vartoday ); //Столбец для вставки даты
}
}



// Returns YYYYMMDD-formatted date.
function getDate() {
var today = new Date();
today.setDate(today.getDate());
//return Utilities.formatDate(today, 'PST', 'dd.MM.yyyy');
return Utilities.formatDate(today, 'GMT+03:00', 'dd.MM.yyyy');
}

He puts down the date rather slowly, I would like to speed up the process.
Is it possible to limit the edit check area? In my case, it is enough to check up to line 27 (inclusive). And will it help?