Как настроить проставление даты при изменении соседней ячейки в Google Sheets?

Question

[Qubickkkkkkkkkkkkkkkk]

Чуваки, всем доброго дня!
Пишу другу базу данных на основе Гугл таблиц. Поставленная задача: чтобы посещаемость заполнялась через Гугл Формы. По ряду технических решений, посещаемость отправляется в журнал без пометки о времени. Причем, отправляется из одной таблицы в другу, и там с одного листа на другой с помощью Querry. Вероятно, это далеко не самое грамотное решение, но я изучал функционал таблицы по мере того, как делал базу. Поэтому местами решения "не очень".
Вопрос следующий: в листе "Посещаемость" в столбце A должна записываться дата, а в столбце B с помощью Querry дозаписываются ученики. Подскажите, пожалуйста, как мне настроить автозаполнение столбца А с текущей датой по мере того, как заполняется посещаемость? (как только появилась следующая запись в столбце B, в столбце А должна отобразиться дата записи)

Comments

[iwnch]

Попробуй эту формулу
=ARRAYFORMULA(IF(B:B <> "", TODAY(), ""))
вставляй ее в столбец А (там где должна быть дата)

Answer 1

[Григорий Боев]

Чтобы дата не менялась, нужно копировать данные скриптом и фиксировать дату копирования.
1) Получить все данные из источника
2) Убрать все строки (по какому-то ключевому столбцу) которые уже есть в приёмнике
3) Добавить дату копирования в каждую из оставшихся после фильтра строк
4) Добавить эти данные в приёмнике

Comments

[Qubickkkkkkkkkkkkkkkk]

Добрый вечер! Не очень понятна теоретическая часть... Если вам не трудно, не могли бы вы показать, как это выглядит в формульном виде?

[Григорий Боев]

Без таблицы это не поможет, да и гадание на кофейной гуще.
Примерный код:

// 1) Получить все данные из источника
const sourceSsId = "asdfg";
const sourceSheetName = "Sheet1";
const destinationSsId = "asdfg2";
const destinationSheetName = "Sheet2";
let [sourceHeaders, ...sourceData] = SpreadsheetApp.openById(sourceSsId).getSheetByName(sourceSheetName).getDataRange().getValues();
// 2) Убрать все строки (по какому-то ключевому столбцу) которые уже есть в приёмнике
const destinationSsId = "asdfg";
const destinationSheetName = "Sheet1";
let [destinationHeaders, ...destinationData] = SpreadsheetApp.openById(destinationSsId).getSheetByName(destinationSheetName).getDataRange().getValues();

const sourceKeyColumnId = 0;  //  Столбец A в источнике
const destinationKeyColumnId = 0;  //  Столбец A в приёмнике

let sourceKeys = sourceData.map(dataRow=>dataRow[sourceKeyColumnId]);

let filteredData = destinationData.filter(dataRow=>{
  let destinationKey = dataRow[destinationKeyColumnId];
  return !sourceKeys.includes(destinationKey);
});
// 3) Добавить дату копирования в каждую из оставшихся после фильтра строк
let now = new Date();
filteredData = filteredData.map(dataRow=>[...dataRow, now])
// 4) Добавить эти данные в приёмнике
let outSheet = SpreadsheetApp.openById(destinationSsId).getSheetByName(destinationSheetName);
if (filteredData.length){
  outSheet.getRange(outSheet.getLastRow()+1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
};

[Qubickkkkkkkkkkkkkkkk]

Понял. Благодарю вас за код, постараюсь разобраться и подстроить под свою таблицу!

Answer 2

[Alexander Ivanov]

Скрипт для вставки даты и времени при редактировании ячейки в Google Таблицах

Задача:
Иногда возникает необходимость автоматически записывать дату и время внесения изменений в определенные ячейки. Это может быть полезно для отслеживания активности, ведения журналов изменений и обеспечения контроля над обновлениями данных.

Когда это полезно:
1. Отслеживание изменений: Ведение журнала изменений данных для аудита или анализа.
2. Контроль сроков выполнения: Отслеживание времени выполнения задач или обновлений информации.
3. Повышение прозрачности: Учет времени и даты изменений для повышения прозрачности и ответственности сотрудников.

Решение:
Для автоматического добавления даты и времени при редактировании ячеек используем скрипт Google Apps Script.

/**
 * Триггер события EDIT
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function onEdit(e){
  // Определяем имя листа, на котором обрабатываем событие редактирования ячейки
  const watchingSheet = 'Добавить дату в строке при изменении ячейки';
  // Определяем индекс столбца в котором обрабатываем событие редактирования ячейки 
  const watchingColIndex = 6;
  // Определяем индекс столбца в который будем записывать дату/время 
  const targetColIndex = 8;
  // Из объекта события получаем измененный диапазон и из диапазона лист, на котором он расположен
  const sheet = e.range.getSheet();

  // Проверяем, что имя листа, на котором произошло событие соответствует указанному в переменной `targetSheetName`
  if (sheet.getName() !== watchingSheet) {
    // И, если событие произошло на другом листе, то прекращаем выполнение функции
    return;
  }

  // Определяем индекс столбца в котором произошло событие
  const column = e.range.getColumn();
  // Проверяем, в нужном ли столбце произошло событие
  // и соответствует ли новое значение нужному нам
  // проверку на значение можно убрать

  // для этого нужно удалить этот фрагмент: "&& e.value === 'Готово'"
  if (column == watchingColIndex && e.value === 'Готово') {
    // Устанавливаем дату и время в ячейку на пересечении строки, в которой было изменение
    // и столбца, указанного в переменной `targetColIndex` 
    e.source.getActiveSheet().getRange(e.range.getRow(), targetColIndex).setValue(new Date());
  }
}

Пример тут https://docs.google.com/spreadsheets/d/1IHDKe-5Tzv...

Оригинал тут https://t.me/GoogleSheets_ru/213

Answer 3

[iwnch]

Попробуй эту формулу
=ARRAYFORMULA(IF(B:B <> "", TODAY(), ""))
вставляй ее в столбец А (там где должна быть дата)

Comments

[Qubickkkkkkkkkkkkkkkk]

В таблицах формулы обновляются каждый раз, когда что-то меняется в диапазоне. Массив поможет в решении этой проблемы?

[iwnch]

Достаточно просто вставить один раз формулу в нужную ячейку и все