When working with teams or automating workflows in the cloud, it's often necessary to convert Microsoft Excel files (.xlsx) into Google Sheets format. While you can do this manually by uploading files to Google Drive and opening them with Google Sheets, a more scalable and automated solution is to use Google Apps Script. This allows you to programmatically upload and convert Excel files, ideal for business workflows, scheduled tasks, or integrations.

In this detailed blog post, we’ll walk through:

• Why convert Excel to Google Sheets

• Step-by-step Apps Script to automate conversion

• Upload via Drive and convert automatically

• Handling folders and multiple files

• Scheduling with Triggers

Why Convert Excel to Google Sheets?

Google Sheets is a cloud-native spreadsheet app that allows real-time collaboration, seamless sharing, and integration with Google Workspace tools. Some reasons to convert Excel to Sheets:

• Work in the cloud without Excel dependencies

• Enable team collaboration in real-time

• Use powerful features like Apps Script, Google Forms, and add-ons

• Automate data processing or reporting in Sheets

Manual conversion is fine occasionally, but if you're working with many Excel files (from email, Drive uploads, API, or local automation), it’s better to automate.

Step 1: Open the Google Apps Script Editor

1. Go to Google Apps Script

2. Click on "New Project"

3. Give your project a meaningful name like "Excel to Sheets Converter"

Step 2: Write the Script to Upload and Convert Excel

Here's the full script that does the following:

• Looks in a specific folder in Google Drive

• Detects .xlsx files

• Converts each file to Google Sheets format

• Saves it in a destination folder

javascript
CopyEdit
function convertExcelFilesToGoogleSheets() {
  var sourceFolderId = 'YOUR_SOURCE_FOLDER_ID'; // Folder containing Excel files
  var destinationFolderId = 'YOUR_DESTINATION_FOLDER_ID'; // Where converted Sheets will go

  var sourceFolder = DriveApp.getFolderById(sourceFolderId);
  var destinationFolder = DriveApp.getFolderById(destinationFolderId);
  
  var files = sourceFolder.getFilesByType(MimeType.MICROSOFT_EXCEL);
  var convertedCount = 0;
  
  while (files.hasNext()) {
    var file = files.next();
    Logger.log('Processing: ' + file.getName());

    var blob = file.getBlob();
    
    var resource = {
      title: file.getName(),
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{ id: destinationFolderId }]
    };
    
    var convertedFile = Drive.Files.insert(resource, blob, {
      convert: true
    });
    
    Logger.log('Converted: ' + convertedFile.title);
    convertedCount++;
  }
  
  Logger.log('Total files converted: ' + convertedCount);
}

Step 3: Enable Advanced Drive API

To use Drive.Files.insert, you must enable the Advanced Drive API:

1. Click on "Services" (left panel in Apps Script editor)

2. Click "+" to add a service

3. Search for Drive API and add it

Step 4: Set Folder Permissions and Get Folder IDs

To find the folder IDs:

1. Open the Google Drive folder

2. Copy the URL. The string after /folders/ is your Folder ID.

Example:

bash
CopyEdit
https://drive.google.com/drive/u/0/folders/1A2B3C4D5E6F

Here, 1A2B3C4D5E6F is the folder ID.

Set the folder with Excel files as the sourceFolderId, and where you want the Google Sheets to go as the destinationFolderId.

Step 5: Run the Script

Click the Run ▶️ button. Authorize the script the first time it runs. Then it will begin processing Excel files in the source folder and save the converted Google Sheets versions into the destination folder.

You can view logs under View > Logs to see the status.

Optional: Set Up a Time-Based Trigger

To automate this process daily or hourly:

1. Go to Triggers (left-side clock icon)

2. Click "+ Add Trigger"

3. Choose convertExcelFilesToGoogleSheets as the function

4. Choose time-based (e.g., every day, hour, or custom frequency)

This will run the script automatically and check the folder for any new Excel files to convert.

Bonus: Delete Original Excel Files After Conversion (Optional)

If you want to clean up the folder by deleting Excel files after converting:

javascript
CopyEdit
file.setTrashed(true); // Move file to Trash

Place this line right after the Drive.Files.insert() call.

Be careful! Once trashed, files are not easily recoverable.

Real-World Use Cases

1. Client Submissions: Clients email Excel reports that get uploaded to Drive, converted, and inserted into a dashboard.

2. Form Responses: Exported Excel files from third-party apps are automatically added to Sheets.

3. Backups: Automatically archive converted data in Sheets format.

4. Data Pipelines: Convert Excel before running Apps Script data processing.

Troubleshooting Tips

• File format issues: Only .xlsx format is reliably supported.

• File too large: Google Sheets has row/column/cell limits.

• Quota limits: Google Apps Script and Drive APIs have daily quotas. Use QuotaLimits class to check usage.

• Conversion failed: Add error handling using try...catch blocks to gracefully skip files that cause issues.

Conclusion

Automating Excel to Google Sheets conversion using Apps Script saves time, ensures consistency, and integrates well with Google Workspace workflows. Whether you are a developer, data analyst, or business owner, setting this up can simplify many repetitive tasks.

This script is highly customizable — you can filter by file name, send email notifications after conversion, or even process data in the converted Sheets.

Start with the base script provided and build on it as per your business logic!