I am a novice at coding and so I have sort help from ChatGPT to help me write a code for Google Sheets. Basically I would like to sort each row in the sheet based on the value in column B based on a hierarchy. The current code I am using does this, however when a value in column B is changed and it finishes sorting, all formulas within the sheet disappears. Is there a way of preserving the formulas within the sheet?
Here is the code:
function onEdit(e) {
const sheet = e.source.getActiveSheet();
const range = e.range;
// Check if the edited cell is in column B and not in the header row (assuming row 1 is the header row)
if (range.getColumn() === 2 && range.getRow() > 1) {
const numRows = sheet.getLastRow() - 1;
const numCols = sheet.getLastColumn();
const dataRange = sheet.getRange(2, 1, numRows, numCols); // Starting from row 2 to skip the header row
// Define the custom sort order based on the hierarchy
const sortOrder = {
"In Transit": 1,
"In Preparation": 2,
"Ready": 3,
"Sold - Pending Delivery": 4,
"Sold - Delivered": 5
};
// Get the data from the entire sheet (excluding the header row) as a 2D array
const data = dataRange.getValues();
// Sort the data based on the hierarchy defined in the sortOrder object
data.sort((a, b) => sortOrder[a[1]] - sortOrder[b[1]]);
// Write the sorted data back to the sheet
dataRange.setValues(data);
}
}