I want to create a Google Apps Script function that copies/export specific Google Sheet tabs into an Excel spreadsheet. I need these sheets to be assigned by the Sheet Gid; as the Sheet Name can be updated/revised. The exported Excel file is copied to a specified folder. The Reference & Index sheet tabs need to be excluded from the export.

My current script exports to excel but the formulas are also copied and causing an error, not displayed the amounts/values.

function exportToExcel() {

  var exportSheetName1 = 'Index';  // Exclude sheet from export to excel
  var exportSheetName2 = 'Reference';  // Exclude sheet from export to excel
  var exportTest = "Harold Jenkins";
  
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet();
  var name = sheet.getRange('Reference!M2').getDisplayValue();
  var destination = DriveApp.getFolderById("1HRchNqQ5_0LYzfULw1hWN_ALNuijsy2q"); // Google Drive Folder Location

  // 1. Copy the active Spreadsheet as a tempora Spreadsheet.
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet().copy('tmp');

  // 2. Convert the formulas to the texts.
  const targetRange = spreadsheet.getSheetByName(exportTest).getDataRange();
  targetRange.copyTo(targetRange, {contentsOnly:true});

  // 3. Delete/Exclude sheets: Index and Reference
  spreadsheet.getSheets().forEach(sheet => {
    if (exportSheetName1 == sheet.getName() || exportSheetName2 == sheet.getName()) spreadsheet.deleteSheet(sheet) 
  });

  // 4. Retrieve the blob from the export URL.
  const id = spreadsheet.getId();
  const xlsxBlob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${id}&exportFormat=xlsx`, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}}).getBlob();

  // 5. Create the blob as a file.
  destination.createFile(xlsxBlob.setName(`${name}.xlsx`));

  // 6. Delete the temporate Spreadsheet.
  DriveApp.getFileById(id).setTrashed(true);
}

For the purpose of testing, I’ve included sheet name “Harold Jenkins” into the script (only for displaying desired results). Require Sheet Gid

Several of the columns in the target sheets have formulas, so it is important the export has only values.

Dummy spreadsheet