Google sheets script is erasing formulas in columns I don't want deleted or cleared

Question

I'm stuck in a very annoying situation, I have a script for google sheets we’re I have two sheets out of several others in the workbook, the script would email one sheet "Pedidos" as an excel file and then delete rows in both "Pedidos"and "Test", in "Test" it deletes all rows below header and the "Pedidos" it should delete all rows after column A and header, I'm doing this because in column A in "Pedidos" I have a formula that I don't want erased so it stays there in all cells in column A for future use. This script is set to run Tuesday to Friday at 8:30pm. Everything works except that when I run it goes ahead and it deletes the aforementioned column A in "Pedidos" sheet, where the formula is and should remain. Any help would be greatly appreciated! This is the script as I am using it:

function sendExcelAndDeleteRows() {
  var spreadsheetId = "####"; // 
  var emailAddress = "###"; // 
  var subject = "Excel Sheet - Pedidos";
  var message = "Please find attached the Excel sheet for Pedidos.";

  // Export Pedidos sheet to Excel format
  var pedidosSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Pedidos");
  var pedidosDataRange = pedidosSheet.getDataRange();
  var pedidosValues = pedidosDataRange.getValues();
  var pedidosHeader = pedidosValues[0];
  var pedidosNewRange = pedidosSheet.getRange(1, 2, 1, pedidosHeader.length - 1);
  pedidosNewRange.setValues([pedidosHeader.slice(1)]);

  var dateToday = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
  var pedidosFileName = "Pedidos_" + dateToday + ".xlsx";
  var pedidosFile = DriveApp.createFile(pedidosFileName, pedidosDataRange.getValues(), MimeType.MICROSOFT_EXCEL);
  MailApp.sendEmail(emailAddress, subject, message, {attachments: [pedidosFile]});

  // Delete rows after row 1 in Test sheet
  var testSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Test");
  testSheet.deleteRows(2, testSheet.getLastRow() - 1);

  // Delete rows after column A in Pedidos sheet
  var lastColumn = pedidosSheet.getLastColumn();
  var lastRow = pedidosSheet.getLastRow();
  if (lastColumn > 1) {
    pedidosSheet.deleteColumns(2, lastColumn - 1);
  }
  pedidosSheet.deleteRows(2, lastRow - 1);

  // Delete the temporary file
  pedidosFile.setTrashed(true);
}

function createTrigger() {
  ScriptApp.newTrigger('sendExcelAndDeleteRows')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.TUESDAY, ScriptApp.WeekDay.WEDNESDAY, ScriptApp.WeekDay.THURSDAY, ScriptApp.WeekDay.FRIDAY)
    .atHour(20)
    .nearMinute(30)
    .create();
}

Answer 1

Instead of trying to delete the columns and rows, you could just clear the contents of the range with data after Row 1 & Column A:

function sendExcelAndDeleteRows() {
  var spreadsheetId = "####"; // 
  var emailAddress = "###"; // 
  var subject = "Excel Sheet - Pedidos";
  var message = "Please find attached the Excel sheet for Pedidos.";

  // Export Pedidos sheet to Excel format
  var pedidosSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Pedidos");
  var pedidosDataRange = pedidosSheet.getDataRange();
  var pedidosValues = pedidosDataRange.getValues();
  var pedidosHeader = pedidosValues[0];
  var pedidosNewRange = pedidosSheet.getRange(1, 2, 1, pedidosHeader.length - 1);
  pedidosNewRange.setValues([pedidosHeader.slice(1)]);

  var dateToday = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
  var pedidosFileName = "Pedidos_" + dateToday + ".xlsx";
  var pedidosFile = DriveApp.createFile(pedidosFileName, pedidosDataRange.getValues(), MimeType.MICROSOFT_EXCEL);
  MailApp.sendEmail(emailAddress, subject, message, {attachments: [pedidosFile]});

  // Delete rows after row 1 in Test sheet
  var testSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Test");
  testSheet.deleteRows(2, testSheet.getLastRow() - 1);

  // Get the last rows in the data range of Pedidos sheet
  var lastColumn = pedidosDataRange.getLastColumn();
  var lastRow = pedidosDataRange.getLastRow();

  // Clear range after column A & row 1
  pedidosSheet.getRange(2,2,lastRow,lastColumn).clearContent()
  
  // Delete the temporary file
  pedidosFile.setTrashed(true);
}

function createTrigger() {
  ScriptApp.newTrigger('sendExcelAndDeleteRows')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.TUESDAY, ScriptApp.WeekDay.WEDNESDAY, ScriptApp.WeekDay.THURSDAY, ScriptApp.WeekDay.FRIDAY)
    .atHour(20)
    .nearMinute(30)
    .create();
}