Formulas in Google Sheets disappearing when using sortOrder function

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);
    
  }
}

Leave a Comment