Сдвинуть заполненные ячейки

Question

Помогите с решением проблемы. Есть заполненные ячейки A1:A3, есть также заполненные ячейки B1:B3. Я добавляю значение в ячейку A4, и мне нужно, чтобы имеющиеся данные в диапазоне B1:B3, смещались вниз, то есть ячейка B1 стала пустой, а значения, которые были в ячейках B1:B3, переместились в ячейки B2:B4 ну собственно выравнивание второго столбца должно всегда происходить по нижнему заполненному значению первого столбца

До

После

стоит отметить, что ячейки в первом столбце заполнены функцией IMPORTAGE

тригер на изменение добавил и дал ему все разрешения

я совершенно не рабираюсь в javascript.

Я попробовал следующий код, но он не работает:

function onEdit(e) {
  if (e && e.source) {
    if (e.source.getSheetName() !== 'Calculation') return;
  }
  if (e) {
    if (e.range && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value != '') {
      var row = e.range.rowStart + 2;
      var column = e.range.columnStart + 1;
      var nextRow = row + 1;
      var nextColumn = column;
      var range = e.range.offset(nextRow, 0);
      if (SpreadsheetApp.getSheetByName('Calculation').getRange(range).isBlank()) {
        // Если диапазон пуст, активируем его
        SpreadsheetApp.getSheetByName('Calculation').getRange(range).activate();
      } else {
        // Если диапазон не пуст, добавляем сообщение об ошибке
        SpreadsheetApp.getActiveSpreadsheet().toast("Ошибка: диапазон не пуст");
      }
    } else if (e.range && e.range.columnStart == 1 && e.range.rowStart >= 2 && e.range.rowStart <= 3) {
      var row = e.range.rowStart + 1;
      var column = e.range.columnStart + 1;
      var nextRow = row + 1;
      var nextColumn = column;
      var range = e.range.offset(nextRow, 0);
      if (SpreadsheetApp.getSheetByName('Calculation').getRange(range).isBlank()) {
        // Если диапазон пуст, активируем его
        SpreadsheetApp.getSheetByName('Calculation').getRange(range).activate();
      } else {
        // Если диапазон не пуст, добавляем сообщение об ошибке
        SpreadsheetApp.getActiveSpreadsheet().toast("Ошибка: диапазон не пуст");
      }
    }
  }
}

Что ж, я написал свой собственный код, который работает почти хорошо. но, к сожалению, функция onEdit не учитывает, что диапазон изменяется функцией IMPORTAGE. Мне пришлось добавить флажок, при изменении которого функция начинает работать. но я хотел бы автоматически отслеживать изменения, принимая во внимание, что диапазон изменяется с помощью функции ИМПОРТА.

  function onEdit(e) {
 if (e && e.source) {
 if (e.source.getSheetName() !== 'Calculation') return;
 }
 if (e) {
 if (e.range && e.range.columnStart == 37 && e.range.rowStart >= 1 && e.range.rowEnd <= 1) {
 const sheet = e.range.getSheet();
 const values = sheet.getDataRange().getValues();
 const valuesA = sheet.getRange('AK4:AK102').getValues();
 const valuesB = sheet.getRange('AL4:AL102').getValues();
 // Здесь обрабатываем filteredValues
 // Подсчёт значений во всём диапазоне
 const allValuesA = valuesA.flat(); // Сглаживаем массив значений
 const stringValuesA = allValuesA.filter(String); // Удаляем нестроковые значения
 const countA = stringValuesA.length; // Считаем количество оставшихся значений
 const allValuesB = valuesB.flat(); // Сглаживаем массив значений
 const stringValuesB = allValuesB.filter(String); // Удаляем нестроковые значения
 const countB = stringValuesB.length; // Считаем количество оставшихся значений
 // Находим индекс последней строки с данными в диапазоне AK4:A103
 const lastRowWithDataIndexA = valuesA.findLastIndex(row => row.join('').trim() !== '')+4;
 // Находим индекс последней строки с данными в диапазоне AL4:AL103
 const lastRowWithDataIndexB = valuesB.findLastIndex(row => row.join('').trim() !== '')+4;
 const firstRowWithDataIndexB = valuesB.findIndex(row => row.join('').trim() !== '')+4;
 const firstDataB = lastRowWithDataIndexB-countB+1
 const targetFirstDataPosB = lastRowWithDataIndexA-countB+1
 const targetRange = sheet.getRange(targetFirstDataPosB, 38, 10, 1); // Целевой диапазон смещен на rowCount строк вниз
 sheet.getRange(firstDataB, 38, valuesB.length, 1).copyTo(targetRange, {contentsOnly: true}); // Копирование содержимого столбца B в целевой диапазон
 const delData = targetFirstDataPosB-4
 if (targetFirstDataPosB >= 4) {
 sheet.getRange(4, 38, delData, 1).clear({contentsOnly: true});
 }

 }
 }
}

не могли бы вы подсказать мне, как лучше всего это реализовать?

Answer 1

Задача странная, но она дает повод продемонстрировать, как можно двигать диапазоны в Таблицах с помощью Apps Script

Полный листинг

function moveDataDown_() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Сдвигать данные вниз');
  const values = sheet.getDataRange().getValues();

  const lastIndexFormula = values.findLastIndex((row) => row[0] !== '');
  const lastIndexData = values.findLastIndex((row) => row[1] !== '');

  if (lastIndexData >= lastIndexFormula) {
    return;
  }

  const range = sheet.getRange(2, 2, lastIndexData, 1);
  range.moveTo(range.offset(lastIndexFormula - lastIndexData, 0));
}

/* exported triggerActionMoveDown */
function triggerActionMoveDown() {
  moveDataDown_();
}

Функцию triggerActionMoveDown нужно просто передать в триггер по времени, который срабатывает каждую минуту. Ну, и, навсякий случай, добавить в меню.

Пример в Таблице https://docs.google.com/spreadsheets/d/1qdh4mQLiEL19_xDherl28CN75eUhQgUNbZLWvsHxD24/edit?gid=79424921#gid=79424921&range=A1

БЛОГ НА HUSL

Сдвинуть заполненные ячейки

Ответы (1 шт):