Код в 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);
}
Простой
