Answer the question
In order to leave comments, you need to log in
Time substitution in GoogleSheets, how to list multiple columns?
The task is to automatically put down the time in a cell when a neighboring cell changes. There is a script that works correctly, but with one set of columns, since writing skills - 0 could not correctly duplicate this for a number of columns. 5 - 7, 8 - 10, 11 - 13, 14 - 16, 17 - 19, 20 - 22, 23 - 25, 26 - 28, 29 - 31, 32 - 34
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var idCol = e.range.getColumn();
var idRow = e.range.getRow();
if ( sheet.getName() == 'Расход 09.19' || sheet.getName() == 'Расход 10.19' || sheet.getName() == 'Расход 11.19' || sheet.getName() == 'Расход 12.19' || sheet.getName() == 'Расход 01.20' || sheet.getName() == 'Расход 02.20' ){ //на каком листе
var tcol = 5; //столбец который нами меняется
var toffset = 2; //смотрим что в ближайшем столбце справа + слева -
var trange = 7; //где вписываем
}
if ( idCol == tcol) {// проверка столбца который нами меняется
var Value = e.range.offset(0, toffset).getValues(); // проверяем ячеку от вказаного выше сролбца
if ( Value == "" ) {
var vartoday = getTime();
sheet.getRange(idRow, trange).setValue( vartoday ); //где вписываем
}
}
}
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');
}
Answer the question
In order to leave comments, you need to log in
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
var names = ['Расход 09.19', 'Расход 10.19', 'Расход 11.19', 'Расход 12.19', 'Расход 01.20', 'Расход 02.20'];
if (names.indexOf(sheet.getName()) == -1) {
return false;
}
var col = range.getColumn();
var row = range.getRow();
var offset = 2;
if ((col > 4) && ((col - 5) % 3 == 0)) {
if (range.offset(0, offset).getValues() == '') {
sheet.getRange(row, col + offset).setValue(Utilities.formatDate(new Date(), 'GMT+03:00', 'HH:mm'));
}
}
}
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question