Почему при сортировке удаляются формулы?
Код в GAS сортирует данные в гугл таблице в диапазоне A2:F999
В столбцах ABCD стоят формулы В столбцах EF стоит текст в виде числа, вроде такого: -3,45 4 -6,78 5,2 94 Причем сортирует он правильно всё, но не могу побороть что при сортировке строк в столбцах ABCD, он устанавливает вместо формул их значение (результат формулы) Основное ядро сортировки вот в этой функции sortSheetByColumn() где и кроется проблема: function sortSheetByColumn(columnIndex, shouldConvertToPercent) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); clearZeroPercentages(); // Очистка нулевых процентов перед сортировкой removePercentageSign(); // Удаление символа процента var lastRow = sheet.getLastRow(); var range = sheet.getRange('A2:F' + lastRow); var data = range.getValues(); // Сопоставляем значения указанного столбца с индексами строк var indexedData = data.map(function(row, index) { var value = row[columnIndex - 1]; // Используем columnIndex для динамического определения столбца var number = value ? parseFloat(value.toString().replace(',', '.')) : null; return { index: index, value: number }; }); // Сортировка данных с учетом пустых значений indexedData.sort(function(a, b) { if (a.value === null) return 1; // Пустые значения в конец if (b.value === null) return -1; // Пустые значения в начало return b.value - a.value; // Сортировка по убыванию }); // Создаем новый массив данных в соответствии с отсортированными индексами var sortedData = indexedData.map(function(item) { return data[item.index]; }); // Записываем отсортированные данные обратно в диапазон range.setValues(sortedData); Весь код: // сортирует отлично в столбцах E и F но единственная проблемма, то что он удаляет формулы в ячейках в столбцах A B C D , а так всё отлично. Отлично сортирует отрицательные и положительные числа function sortSheetByColumnE() { sortSheetByColumn(5, false); // Сортировка по столбцу E без изменения формата addPercentageToCells(); // Вызов функции для добавления символа процента в конец каждой ячейки диапазона E2:F999 } function sortSheetByColumnF() { sortSheetByColumn(6, true); // Сортировка по столбцу F с изменением формата на проценты addPercentageToCells(); // Вызов функции для добавления символа процента в конец каждой ячейки диапазона E2:F999 } // добавляем символ процента в конец каждой ячейки диапазона E2:F999 function addPercentageToCells() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('E2:F999'); // Выбираем диапазон E2:F999 var values = range.getValues(); // Получаем значения в диапазоне // Перебираем все строки и столбцы в диапазоне for (var row = 0; row < values.length; row++) { for (var col = 0; col < values[row].length; col++) { if (values[row][col] !== '') { // Проверяем, не пустая ли ячейка values[row][col] += '%'; // Добавляем символ процента } } } range.setValues(values); // Обновляем ячейки новыми значениями } // очищаем все ячейки где стоит 0% в диапазоне E2:E 999 function clearZeroPercentages() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lastRow = sheet.getLastRow(); var rangeE = sheet.getRange("E2:E" + lastRow); var rangeF = sheet.getRange("F2:F" + lastRow); var valuesE = rangeE.getValues(); var valuesF = rangeF.getValues(); // Очищаем ячейки, содержащие 0% в столбцах E и F valuesE.forEach(function(row, i) { if (row[0] === '0%') row[0] = ''; }); valuesF.forEach(function(row, i) { if (row[0] === '0%') row[0] = ''; }); // Устанавливаем обновленные значения обратно в лист rangeE.setValues(valuesE); rangeF.setValues(valuesF); } // удаляем из диапазона E2:E999 из всех ячеек символа процента function removePercentageSign() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange("E2:F999"); var data = range.getValues(); // Удаляем символ процента из всех ячейк var newData = data.map(function(row) { return row.map(function(cell) { if (typeof cell === 'string' && cell.endsWith('%')) { return cell.slice(0, -1); } return cell; }); }); // Устанавливаем обновленные данные обратно в диапазон range.setValues(newData); } // функция сортировки внутри которой мы сначала чистим ячейки в диапазоне E2:E999 где стоит ноль, // потом удаляем из E2:E999 в ячейках символы процентов function sortSheetByColumn(columnIndex, shouldConvertToPercent) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); clearZeroPercentages(); // Очистка нулевых процентов перед сортировкой removePercentageSign(); // Удаление символа процента var lastRow = sheet.getLastRow(); var range = sheet.getRange('A2:F' + lastRow); var data = range.getValues(); // Сопоставляем значения указанного столбца с индексами строк var indexedData = data.map(function(row, index) { var value = row[columnIndex - 1]; // Используем columnIndex для динамического определения столбца var number = value ? parseFloat(value.toString().replace(',', '.')) : null; return { index: index, value: number }; }); // Сортировка данных с учетом пустых значений indexedData.sort(function(a, b) { if (a.value === null) return 1; // Пустые значения в конец if (b.value === null) return -1; // Пустые значения в начало return b.value - a.value; // Сортировка по убыванию }); // Создаем новый массив данных в соответствии с отсортированными индексами var sortedData = indexedData.map(function(item) { return data[item.index]; }); // Записываем отсортированные данные обратно в диапазон range.setValues(sortedData); } |
сам разобрался. вот так сработало:
function sortSheetByColumnE() { sortSheetByColumn(5, false); // Сортировка по столбцу E без изменения формата addPercentageToCells(); // Вызов функции для добавления символа процента в конец каждой ячейки диапазона E2:F999 fillEmptyWithZeroPercentage() } function sortSheetByColumnF() { sortSheetByColumn(6, true); // Сортировка по столбцу F с изменением формата на проценты addPercentageToCells(); // Вызов функции для добавления символа процента в конец каждой ячейки диапазона E2:F999 fillEmptyWithZeroPercentage() } // добавляем символ процента в конец каждой ячейки диапазона E2:F999 function addPercentageToCells() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('E2:F999'); // Выбираем диапазон E2:F999 var values = range.getValues(); // Получаем значения в диапазоне // Перебираем все строки и столбцы в диапазоне for (var row = 0; row < values.length; row++) { for (var col = 0; col < values[row].length; col++) { if (values[row][col] !== '') { // Проверяем, не пустая ли ячейка values[row][col] += '%'; // Добавляем символ процента } } } range.setValues(values); // Обновляем ячейки новыми значениями } // очищаем все ячейки где стоит 0% в диапазоне E2:E 999 function clearZeroPercentages() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lastRow = sheet.getLastRow(); var rangeE = sheet.getRange("E2:E" + lastRow); var rangeF = sheet.getRange("F2:F" + lastRow); var valuesE = rangeE.getValues(); var valuesF = rangeF.getValues(); // Очищаем ячейки, содержащие 0% в столбцах E и F valuesE.forEach(function(row, i) { if (row[0] === '0%') row[0] = ''; }); valuesF.forEach(function(row, i) { if (row[0] === '0%') row[0] = ''; }); // Устанавливаем обновленные значения обратно в лист rangeE.setValues(valuesE); rangeF.setValues(valuesF); } // удаляем из диапазона E2:E999 из всех ячеек символа процента function removePercentageSign() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange("E2:F999"); var data = range.getValues(); // Удаляем символ процента из всех ячейк var newData = data.map(function(row) { return row.map(function(cell) { if (typeof cell === 'string' && cell.endsWith('%')) { return cell.slice(0, -1); } return cell; }); }); // Устанавливаем обновленные данные обратно в диапазон range.setValues(newData); } // во все ячейки где пустота , ставим 0% в диапазоне E2:F999 function fillEmptyWithZeroPercentage() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lastRow = sheet.getRange("A" + sheet.getLastRow()).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); var rangeE = sheet.getRange("E2:E" + lastRow); var rangeF = sheet.getRange("F2:F" + lastRow); var valuesE = rangeE.getValues(); var valuesF = rangeF.getValues(); // Заполняем пустые ячейки текстом "0%" в столбцах E и F valuesE.forEach(function(row, i) { if (!row[0]) row[0] = '0%'; // Если ячейка пуста, заполняем её "0%" }); valuesF.forEach(function(row, i) { if (!row[0]) row[0] = '0%'; // Если ячейка пуста, заполняем её "0%" }); // Устанавливаем обновленные значения обратно в лист rangeE.setValues(valuesE); rangeF.setValues(valuesF); } // функция сортировки внутри которой мы сначала чистим ячейки в диапазоне E2:F999 где стоит ноль, // потом удаляем из E2:F999 в ячейках символы процентов function sortSheetByColumn(columnIndex, shouldConvertToPercent) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); clearZeroPercentages(); // Очистка нулевых процентов перед сортировкой removePercentageSign(); // Удаление символа процента // Вызываем кастомную функцию сортировки customSort(sheet, columnIndex); } function customSort(sheet, columnIndex) { // Получаем данные и формулы var lastRow = sheet.getLastRow(); var range = sheet.getRange('A2:F' + lastRow); var data = range.getValues(); var formulas = range.getFormulas(); // Привязываем значения и формулы к их индексам var combinedData = data.map(function(row, index) { return { value: row[columnIndex - 1], formulas: formulas[index], index: index, row: row }; }); // Сортировка combinedData.sort(compare); // Разделяем отсортированные значения и формулы var sortedValues = combinedData.map(function(item) { return item.row; }); var sortedFormulas = combinedData.map(function(item) { return item.formulas; }); // Записываем отсортированные данные range.setValues(sortedValues); // Обновляем формулы sortedFormulas.forEach(function(rowFormulas, rowIndex) { rowFormulas.forEach(function(formula, colIndex) { if (formula) { // Обновляем только ячейки с формулами sheet.getRange(rowIndex + 2, colIndex + 1).setFormula(formula); } }); }); } function compare(a, b) { var valA = a.value === '' || a.value === '0%' ? 0 : parseFloat(a.value.replace(',', '.')); var valB = b.value === '' || b.value === '0%' ? 0 : parseFloat(b.value.replace(',', '.')); // Положительные числа идут перед нулями и отрицательными числами, сортируются по убыванию if (valA > 0 && valB <= 0) return -1; if (valB > 0 && valA <= 0) return 1; if (valA > 0 && valB > 0) return valB - valA; // Нули идут после положительных и отрицательных чисел if (valA === 0 && valB !== 0) return 1; if (valB === 0 && valA !== 0) return -1; // Среди отрицательных чисел меньшее по абсолютной величине число идет перед большим if (valA < 0 && valB < 0) return Math.abs(valA) - Math.abs(valB); return 0; // Если все проверки пройдены и значения равны, порядок не меняется } |
Часовой пояс GMT +3, время: 19:58. |