Translate

Convert Excel Files to CSV in Google Drive with Apps Script

Convert Excel Files to CSV in Google Drive with Apps Script – A Complete Guide

Google Drive is a powerful cloud storage solution, and with the help of Google Apps Script, you can automate tasks like file conversions, backups, data exports, and more.

One common requirement is to automatically convert Excel files (.xlsx or .xls) stored in Google Drive into CSV files, especially if you want to:

  • Integrate with external systems

  • Use data for imports

  • Store lighter-weight formats

  • Schedule regular data extractions

In this detailed guide, you'll learn how to write a Google Apps Script that scans your Google Drive for Excel files, converts them into CSV format, and optionally saves them to a designated folder.

Why Convert Excel to CSV?

There are many reasons:

  • CSV is simpler, lightweight, and universally supported

  • Ideal for importing/exporting data to/from software or databases

  • Easier to read using scripts or parsers

  • Better for version control and comparisons

  • Used by APIs and integrations that don’t support Excel formats

What You’ll Need

  • A Google Drive account with Excel files uploaded

  • Basic knowledge of Google Apps Script

  • Permission to access and create files in your Drive

Step-by-Step: Convert Excel to CSV Using Apps Script

Step 1: Open Apps Script

  1. Visit https://script.google.com

  2. Click on New Project

  3. Give your project a name like: Excel to CSV Converter

Step 2: Paste the Script

Copy and paste the following script into the editor:

function convertExcelToCSV() {
  const sourceFolderId = 'YOUR_SOURCE_FOLDER_ID'; // Folder containing Excel files
  const destinationFolderId = 'YOUR_DEST_FOLDER_ID'; // Folder where CSVs will be saved

  const sourceFolder = DriveApp.getFolderById(sourceFolderId);
  const destFolder = DriveApp.getFolderById(destinationFolderId);

  const files = sourceFolder.getFilesByType(MimeType.MICROSOFT_EXCEL);

  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    const blob = file.getBlob();

    // Convert Excel to Google Sheets temporarily
    const tempFile = DriveApp.createFile(blob).setName(fileName);
    const spreadsheet = SpreadsheetApp.open(tempFile);

    const sheets = spreadsheet.getSheets();
    for (let i = 0; i < sheets.length; i++) {
      const sheet = sheets[i];
      const csvData = convertSheetToCsv(sheet);
      const csvFileName = `${fileName.replace(/\.[^/.]+$/, "")} - ${sheet.getName()}.csv`;

      const csvBlob = Utilities.newBlob(csvData, 'text/csv', csvFileName);
      destFolder.createFile(csvBlob);
    }

    // Clean up temporary file
    DriveApp.getFileById(spreadsheet.getId()).setTrashed(true);
  }

  Logger.log('Conversion complete.');
}

// Helper function to convert a Sheet to CSV string
function convertSheetToCsv(sheet) {
  const data = sheet.getDataRange().getValues();
  return data.map(row =>
    row.map(cell => `"${String(cell).replace(/"/g, '""')}"`).join(',')
  ).join('\r\n');
}

Step 3: Replace Folder IDs

Update the placeholders:

  • 'YOUR_SOURCE_FOLDER_ID': ID of the folder containing Excel files

  • 'YOUR_DEST_FOLDER_ID': ID of the folder where CSVs should be saved

You can find a folder's ID in the URL when you're inside that folder:

https://drive.google.com/drive/folders/XXXXXXXXXXXXXXXXXXXX

Just copy the XXXXXXXXXXXXXXXXXXXX part.

Step 4: Save and Run

  1. Click File > Save.

  2. Click the Run ▶️ button to execute convertExcelToCSV.

  3. The first time, it will ask for authorization. Grant the required permissions.

  4. Once the script finishes, your destination folder will contain the converted CSV files.

Script Explanation

Let’s break down the script:

1. Access Source and Destination Folders

const sourceFolder = DriveApp.getFolderById(sourceFolderId);
const destFolder = DriveApp.getFolderById(destinationFolderId);

This gets a handle on the folders where Excel files are located and where CSVs will be stored.

2. Get All Excel Files

const files = sourceFolder.getFilesByType(MimeType.MICROSOFT_EXCEL);

Only fetches .xlsx or .xls files. (You can also use getFiles() to include all file types.)

3. Convert Excel to Google Sheets Temporarily

const tempFile = DriveApp.createFile(blob).setName(fileName);
const spreadsheet = SpreadsheetApp.open(tempFile);

This step is crucial: Google Apps Script cannot directly parse .xlsx, so we convert it into a temporary Google Sheets file first.

4. Extract Each Sheet as a CSV

for (let i = 0; i < sheets.length; i++) {
  const csvData = convertSheetToCsv(sheet);
}

Each worksheet inside the Excel file becomes its own CSV.

5. Convert Data to CSV Format

row.map(cell => `"${String(cell).replace(/"/g, '""')}"`).join(',')

This line ensures:

  • Each cell is enclosed in double quotes

  • Embedded quotes are escaped by doubling them

  • Values are comma-separated

6. Save the CSV File to Destination Folder

const csvBlob = Utilities.newBlob(csvData, 'text/csv', csvFileName);
destFolder.createFile(csvBlob);

Creates a new file in the target folder.

7. Cleanup

DriveApp.getFileById(spreadsheet.getId()).setTrashed(true);

Deletes the temporary Google Sheets file created during conversion.

Optional Enhancements

You can modify or extend the script to:

  • Skip already converted files (check existing filenames)

  • Convert only the first sheet

  • Add a timestamp to filenames

  • Send a report email when done

  • Schedule automatic conversions using time-based triggers

Schedule Auto-Conversion with Triggers

  1. Go to the Apps Script project

  2. Click the clock icon (Triggers)

  3. Add a new trigger

    • Function: convertExcelToCSV

    • Event: Time-driven

    • Frequency: Daily, Weekly, or Hourly

This way, any Excel file added to the source folder will be automatically converted.

Troubleshooting Tips

  • Authorization Errors: Run the script manually once to trigger authorization prompt.

  • No Files Converted?: Ensure the folder IDs are correct and files are .xls or .xlsx.

  • Special Characters or Formatting: CSV is plain-text only. Advanced formatting like colors or formulas will not be retained.

  • Multiple Sheets?: This script creates a CSV file for each sheet in the Excel file.

Use Cases

  • Automated Data Pipeline: Convert and load Excel data into a database

  • Migration: Standardize Excel uploads as CSV for integration

  • Data Entry Automation: Teams upload Excel files and CSVs are generated for processing

  • API Integration: Use CSV outputs in webhooks or integrations

  • Team Collaboration: Keep Excel formatting private but share plain CSV for developers or analysts

Conclusion

With Google Apps Script, you can automate the conversion of Excel files to CSV format — directly in Google Drive — without any manual intervention. This solution saves time, ensures consistency, and integrates well with other tools or systems that require CSV input.

By setting up this script and optionally scheduling it with triggers, you can streamline Excel data processing and eliminate repetitive work entirely.

Whether you're building a report automation system, importing data to a CRM, or simply reducing manual CSV exports — this solution is simple, scalable, and extremely powerful.