What's the best way to save Google Sheets as Excel with just values and formatting?

Other Questions Google Sheets
1

Hey folks, I need some help with exporting Google Sheets

I’ve got a script that exports a Google Sheet to Excel, but it’s not working quite right. Here’s what I’m trying to do:

  • Export a specific Google Sheet to Excel format
  • Keep only the values and formatting (no formulas)
  • Save the file in a particular Google Drive folder
  • Have a button to confirm the export

The problem is, when I run the script, it exports everything including the formulas. I just want the values and formatting.

Here’s a simplified version of my code:

function exportToExcel() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var fileName = sheet.getRange('Info!A1').getValue() + '.xlsx';
  var folderId = '1ABC123xyz';
  
  var url = 'https://docs.google.com/spreadsheets/d/' + sheet.getParent().getId() + '/export?format=xlsx';
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });
  
  DriveApp.getFolderById(folderId).createFile(response.getBlob().setName(fileName));
}

Any ideas on how to modify this to export only values and formatting? Thanks in advance!

2

I’ve dealt with this issue before and discovered a workaround that may help. Instead of using the export URL directly, create a temporary spreadsheet and copy only the values and formatting from the original sheet. I first generated a new spreadsheet, transferred the necessary data while omitting formulas, exported the temporary sheet as an Excel file, and then removed the temporary file. Although this process may slow down large spreadsheets, it guarantees that only values and formatting are included. For better performance with big files, consider using the Sheets API for more control.

3

hey luna, i’ve had similar issues. try using the getValues() method to grab just the data, then create a new sheet with those values. export that new sheet instead. it’s a bit roundabout but should work. let me know if u need more help!

4

To export Google Sheets as Excel with only values and formatting, you can modify your script to use the Advanced Sheets Service. This approach allows more control over the export process. First, enable the Advanced Sheets Service in your Apps Script project. Then, use the Sheets.Spreadsheets.get method to retrieve the spreadsheet data, and Sheets.Spreadsheets.batchUpdate to clear formulas. Finally, export the modified sheet. This method is more efficient for larger spreadsheets and provides precise control over the exported content. Remember to handle potential API quota limits if you’re working with numerous or very large sheets.