📋 Convert Google Sheet to Excel XLSX Spreadsheet

1. Overview

\[ \begin{array}{l} \textbf{Google Apps Script allows you to convert Google Sheets into Excel XLSX files} \\ \text{programmatically.} \\ \textbf{This is useful for exporting data to a widely used file format} \\ \text{for sharing, reporting, or integration with other software.} \end{array} \]

2. Use Cases

            
                - Automatically export reports or data to Excel format
                - Create Excel backups of Google Sheets at regular intervals
                - Integrate with third-party applications requiring Excel files
                - Provide stakeholders with data in a format they can easily use
            
        

Converting to Excel format enhances compatibility with a variety of tools and stakeholders.

3. Script to Convert Google Sheet to XLSX

            
                function saveAsXLSX() {
                  const ss = SpreadsheetApp.getActiveSpreadsheet();
                  const folder = DriveApp.getFolderById("YOUR_FOLDER_ID"); // Folder to save the file
                  const fileName = ss.getName() + ".xlsx"; // Name for the Excel file
                  
                  const blob = ss.getAs("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
                  blob.setName(fileName);
                  
                  folder.createFile(blob);
                }
            
        

This script converts the active Google Sheet into an Excel XLSX file and saves it to a specified folder.

4. Specify Folder to Save the Excel File

            
                function saveAsXLSXToFolder() {
                  const ss = SpreadsheetApp.getActiveSpreadsheet();
                  const folder = DriveApp.getFolderById("YOUR_FOLDER_ID"); // Folder to save the file
                  const fileName = ss.getName() + " Exported.xlsx"; // Custom Excel file name

                  const blob = ss.getAs("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                  blob.setName(fileName);

                  folder.createFile(blob); // Save to folder
                  Logger.log("File saved to: " + folder.getName());
                }
            
        

Replace `"YOUR_FOLDER_ID"` with the actual Google Drive folder ID where the file will be saved.

5. Automate XLSX Export with Triggers

            
                function setupExportTrigger() {
                  ScriptApp.newTrigger('saveAsXLSX')
                    .timeBased()
                    .everyDays(1) // Adjust frequency
                    .atHour(9) // Set the time
                    .create();
                }
            
        

This sets up a daily trigger to automatically export the sheet to an Excel file at 9 AM.

6. Optional: Add Timestamp to the Filename

            
                function saveWithTimestamp() {
                  const ss = SpreadsheetApp.getActiveSpreadsheet();
                  const folder = DriveApp.getFolderById("YOUR_FOLDER_ID");

                  const date = new Date();
                  const timestamp = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd_HH:mm:ss");
                  const fileName = ss.getName() + "_" + timestamp + ".xlsx";

                  const blob = ss.getAs("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                  blob.setName(fileName);

                  folder.createFile(blob);
                }
            
        

This script appends the current timestamp to the filename to avoid overwriting previous exports.

7. Conclusion

            
                Converting Google Sheets to Excel files with Google Apps Script automates data export 
                and makes it easy to share your Google Sheets with others who prefer the Excel format.
            
        

By automating the export, you ensure regular backups or scheduled reporting without manual effort.