Блог

Я надеюсь использовать форму Google, чтобы мои ученики подписывались на мероприятия. Я добавил столбец справа от данных формы, где я могу поставить «x», как только я встречусь с ними. У меня есть отдельная вкладка под названием «Упорядоченный», где я использую запрос для сортировки и отображения только записей без x . Как только я встречаюсь со студентом, я могу поставить x на вкладке «исходные данные», и запись скроется с вкладки «Упорядоченный» (что-то вроде очереди для моих студентов).

Я бы хотел, чтобы мне не приходилось переключаться между 2 вкладками каждый раз, когда мне нужно кого-то отметить. Есть ли способ добавить аналогичный столбец на упорядоченной вкладке, который скроет готовую запись?

Вот лист, на который я ссылаюсь: https://docs.google.com/spreadsheets/d/1fQHF0EoGLk5NEI6GvyRk4InBl-uEq0jq7qqmyOmFjy8/edit#gid=379798836

Я делюсь этим листом с другими преподавателями, которые не знакомы с электронными таблицами, поэтому решение должно быть удобным для пользователей.

Как сказал @Aerials, здесь вы, вероятно, столкнетесь с проблемами циклической зависимости. Из-за этого я бы предложил использовать Apps Script и избавиться от QUERY формулы. Вы хотите сделать следующее:

• Каждый раз, когда кто-то отправляет форму, Ordered лист обновляется новыми данными отправки.
• Каждый раз, когда x добавляется на Form Responses 1 лист, соответствующая строка в Ordered удаляется.
• Каждый раз, когда на x лист добавляется элемент Ordered , соответствующая строка в Ordered удаляется, а элемент x добавляется в соответствующую строку в Form Responses 1 .

Возможным способом было бы что-то вроде следующих строк (откройте скрипт, привязанный к вашей электронной таблице, щелкнув Tools > Script editor ):

• Установите триггер onFormSubmit, чтобы Ordered он обновлялся новыми данными при каждой отправке формы. Триггер можно установить вручную или программно, скопировав эту функцию в свой скрипт и запустив ее один раз:

 function onFormSubmit(e) {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("copyAndFilterData")
  .forSpreadsheet(ss)
  .onFormSubmit()
  .create();
}
  

• Как только триггер будет установлен, функция copyAndFilterData будет выполняться каждый раз при отправке формы. Эта функция должна быть обновлена Ordered на основе данных в Form Responses 1 , удалив все отправленные данные, которые были помечены как завершенные x . Эта функция может быть следующей:

 function copyAndFilterData() {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName("Form Responses 1");
  var sheet2 = ss.getSheetByName("Ordered");
  var sourceData = sheet1.getDataRange().getValues();
  var filteredData = sourceData.filter(sourceRow => sourceRow[5] !== "x")
  .sort((a, b) => a - b)    
  .map(sourceRow => {
       sourceRow.pop();
       sourceRow.splice(1, 1);
       return sourceRow;
  });
  sheet2.getRange(1, 1, sheet2.getLastRow(), 4).clear({contentsOnly: true});
  var destRange = sheet2.getRange(1, 1, filteredData.length, filteredData[0].length)
  destRange.setValues(filteredData);
}
  

• На этом этапе потребуется еще одна часть функциональности: обновить электронную таблицу на основе добавления или удаления x на любом из обоих листов. Для этого потребуется триггер onEdit, например:

 function onEdit(e) {
  var editedSheet = e.range.getSheet();
  if (editedSheet.getName() === "Form Responses 1") copyAndFilterData();
  else if (editedSheet.getName() === "Ordered") removeRows(e, editedSheet);
}
  

• Как только эта функция скопирована и проект сохранен, при каждом Form Responses 1 редактировании Ordered листы обновляются (функция copyAndFilterData ), и при каждом Ordered редактировании запускается функция removeRows . Эта функция должна выполнить две вещи: (1) удалить строку, которая была помечена как завершенная в Ordered ( x добавлена) и (2) добавить соответствующую x в Form Responses 1 . Даты отправки можно сравнить, чтобы идентифицировать строку:

 function removeRows(e, editedSheet) {
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  var value = range.getValue();
  if (column == 5 amp;amp; row > 1 amp;amp; value == "x") {
    var date = editedSheet.getRange(row, 1).getValue();
    var formSheet = e.source.getSheetByName("Form Responses 1")
    var values = formSheet.getRange(2, 1, formSheet.getLastRow() - 1).getValues(); 
    var markedRow = values.findIndex(value => value[0].getTime() === date.getTime());
    formSheet.getRange(markedRow   2, 6).setValue("x");
    editedSheet.deleteRow(row);
  }
}
  

Ссылка:

• Обзор скрипта Google Apps
• Простые Триггеры
• Устанавливаемые триггеры