Automatically convert Excel spreadsheets to Google Sheets using Apps Script

Microsoft Excel is the lingua franca of the business world! If you've worked for any business of any size, you've probably used Excel in some form or another.

Even if your school or company has moved to using Google Workspace and its cloud based products like Google Sheets, you may still receive Excel spreadsheets from your co-workers, students and others. Also, some software programs only let you export data to the Excel file format (.xls or .xlsx). Therefore, it is useful to know how to automatically convert Excel files to Google Sheets using Apps Script.

Once you convert the Excel file to Google Sheets, you may also want to import its contents into a master spreadsheet. For example, you might be a marketing manager and your marketing analyst might send you a daily report in Excel. To analyze trends over time, you decide to copy the data from these Excel files to a master Google Sheets spreadsheet. Every day, you copy the data for that day into a separate sheet in the master spreadsheet. This lets you track trends over time. This manual process can be tedious!

What if you could save the file into Google Drive from your email itself (Gmail supports this feature) and then import it into your master spreadsheet using Apps Script? In this tutorial, I will show you how to convert an Excel spreadsheet into a Google Sheets spreadsheet and then import data from it into a master spreadsheet.

Prerequisites

This tutorial assumes that you're familiar with:

• Google Sheets

• Basic coding concepts (If you're new to coding, please check out a series of tutorials that I've written on learning to code using Google Sheets and Apps Script.).

• Creating and running Apps Scripts.

• Authenticating and Authorizing Apps Scripts.

• Prompting the user for input using Apps Script.

• Triggers in Google Sheets (especially the onOpen simple trigger)

• Working with ranges in Google Sheets using Apps Script

• Importing CSV files into Google Sheets using Apps Script

5 steps to convert an Excel file in Google Drive to Google Sheets using Apps Script

• Step 1 — Create a Google Sheet to use as an interface to perform the conversion

• Step 2 — Use Apps Script to prompt the user for the Excel file's name

• Step 3 — Activate the Drive advanced service in Apps Script

• Step 4 — Use Apps Script to convert the Excel file to Google Sheets

• Step 5 — Import contents from the converted Google Sheets spreadsheet to your master spreadsheet

Step 1 — Create a Google Sheet to use as an interface to perform the conversion

Create a Google Sheets spreadsheet that will serve as the master spreadsheet where you want data from Excel to be imported. The Excel spreadsheet will be converted to a Google Sheets spreadsheet first and then data from a sheet that you specify will be imported into another (the master) Google Sheets spreadsheet.

Note

This tutorial assumes that your Excel file is in Google Drive. I've created an Excel file called student_grades_data.xlsx that contains a sheet called Data in it. This sheet contains the grades of 6 students. This is the data we want to import into Google Sheets.

Step 2 — Use Apps Script to prompt the user for the Excel file's name

Open the Apps Script editor and replace the code in it with the code below.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Import Excel file")
    .addItem("Import Excel file from Drive", "main")
    .addToUi();
}

function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
}

function toast(message) {
  SpreadsheetApp.getActive().toast(message);
}


function promptUser(message) {
  let ui = SpreadsheetApp.getUi();
  let response = ui.prompt(message);
  if(response != null && response.getSelectedButton() === ui.Button.OK) {
    return response.getResponseText();
  } else {
    return null;
  }
}

Try running the code from the Apps Script toolbar. When you do, you should see a prompt in your spreadsheet asking you to enter the name of the Excel file to convert and import.

If you click the [X] to close the prompt , you will see a toast notification informing you that you must enter a valid file name.

Once you enter the name of the Excel file, you'll be prompted to enter the name of the sheet in that file to import data from. In my Excel spreadsheet, since the student grades are in a sheet called "Data", I will enter "Data" as the value and click [Ok].

When you select [Ok] a toast notification will appear informing you that data is being imported from the Excel spreadsheet.

Step 3 — Activate the Drive advanced service in Apps Script

In order to convert files from one type to another we will need to use the advanced Drive service. A "service" is a term in programming for mechanisms to access certain capabilities programmatically. The "Drive service" lets you access certain capabilities of Google Drive from your code. Apps Script provides a basic version of the Drive service and an advanced version. The basic version is called DriveApp, is easier to use and is turned on by default. The advanced version is simply called Drive, lets you access advanced features and must be activated manually.

To activate the advanced Drive service, select the + next to Services on the sidebar menu in the code editor and select the Drive API.

Step 4 — Use Apps Script to convert the Excel file to Google Sheets

Once we enable the Drive API, we will use it to convert the Excel spreadsheet to a Google Sheets spreadsheet. To do this, we will follow the following steps:

• Get the Excel file from Google Drive using DriveApp.getFilesByName(fileName)

• See if the file exists. If it does, we get its contents using the getBlob() method.

• Then we create a copy by inserting a new file with the contents. We also specify a configuration asking to use a different file format (Google Sheets) when creating the copy.

• Finally, we return the ID of the newly created Google Sheets file.

Here is the code that implements the above logic:

function convertExcelToGoogleSheets(fileName) {
  let files = DriveApp.getFilesByName(fileName);
  let excelFile = null;
  if(files.hasNext())
    excelFile = files.next();
  else
    return null;
  let blob = excelFile.getBlob();
  let config = {
    title: "[Google Sheets] " + excelFile.getName(),
    parents: [{id: excelFile.getParents().next().getId()}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  let spreadsheet = Drive.Files.insert(config, blob);
  return spreadsheet.id;
}

Then we use the above function in the main() function to convert the Excel spreadsheet. We display a toast notification to let the user know the ID of the converted Google Sheets spreadsheet.

let spreadsheetId = convertExcelToGoogleSheets(fileName);
toast(`Google Sheets File Id: ${spreadsheetId}`);

Here is the modified main() function.

function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
  let spreadsheetId = convertExcelToGoogleSheets(fileName);
  toast(`Google Sheets File Id: ${spreadsheetId}`);
}

We are not done yet. This converted spreadsheet has some data that we now need to import into our master spreadsheet. That is the next and final step.

Step 5 — Import contents from the converted Google Sheets spreadsheet to your master spreadsheet

In this final step, we will import data from a sheet in the converted spreadsheet that the user specified to the master spreadsheet.

We will create a function called importDataFromSpreadsheet() that accepts two parameters as input: (1) the ID of a Google Sheets spreadsheet and (2) the name of a sheet in it that contains data we want to import.

function importDataFromSpreadsheet(spreadsheetId, sheetName) {
  let spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  let currentSpreadsheet = SpreadsheetApp.getActive();
  let newSheet = currentSpreadsheet.insertSheet();
  let dataToImport = spreadsheet.getSheetByName(sheetName).getDataRange();
  let range = newSheet.getRange(1,1,dataToImport.getNumRows(), dataToImport.getNumColumns());
  range.setValues(dataToImport.getValues());
  return newSheet.getName();
}

Then we need to modify the main() function to import data from the newly created Google Sheets spreadsheet into a new sheet in your master spreadsheet.

let importedSheetName = importDataFromSpreadsheet(spreadsheetId, sheetName);
  toast(`Successfully imported data from ${sheetName} in ${fileName} to ${importedSheetName}`);

Here is the code for the main() function after making these changes:

function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
  let spreadsheetId = convertExcelToGoogleSheets(fileName);
  let importedSheetName = importDataFromSpreadsheet(spreadsheetId, sheetName);
  toast(`Successfully imported data from ${sheetName} in ${fileName} to ${importedSheetName}`);
}

That's it, we're done creating an automated way to convert Excel files to Google Sheets and then import data from it into a master Google Sheets spreadsheet. Now, test your code by trying to import a sheet from an Excel file in Google Drive.

Full code

For your convenience I've provided the full code below.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Import Excel file")
    .addItem("Import Excel file from Drive", "main")
    .addToUi();
}

function main() {
  let fileName = promptUser("Enter the name of the Excel file to import:");
  if(fileName === null) {
    toast("Please enter a valid filename.");
    return;
  }
  let sheetName = promptUser(`Enter the name of the sheet in ${fileName} to import:`);
  if(sheetName === null) {
    toast("Please enter a valid sheet.");
    return;
  }
  toast(`Importing ${sheetName} from ${fileName} ...`);
  let spreadsheetId = convertExcelToGoogleSheets(fileName);
  let importedSheetName = importDataFromSpreadsheet(spreadsheetId, sheetName);
  toast(`Successfully imported data from ${sheetName} in ${fileName} to ${importedSheetName}`);
}

function toast(message) {
  SpreadsheetApp.getActive().toast(message);
}

function promptUser(message) {
  let ui = SpreadsheetApp.getUi();
  let response = ui.prompt(message);
  if(response != null && response.getSelectedButton() === ui.Button.OK) {
    return response.getResponseText();
  } else {
    return null;
  }
}

function convertExcelToGoogleSheets(fileName) {
  let files = DriveApp.getFilesByName(fileName);
  let excelFile = null;
  if(files.hasNext())
    excelFile = files.next();
  else
    return null;
  let blob = excelFile.getBlob();
  let config = {
    title: "[Google Sheets] " + excelFile.getName(),
    parents: [{id: excelFile.getParents().next().getId()}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  let spreadsheet = Drive.Files.insert(config, blob);
  return spreadsheet.id;
}

function importDataFromSpreadsheet(spreadsheetId, sheetName) {
  let spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  let currentSpreadsheet = SpreadsheetApp.getActive();
  let newSheet = currentSpreadsheet.insertSheet();
  let dataToImport = spreadsheet.getSheetByName(sheetName).getDataRange();
  let range = newSheet.getRange(1,1,dataToImport.getNumRows(), dataToImport.getNumColumns());
  range.setValues(dataToImport.getValues());
  return newSheet.getName();
}

Conclusion

In this tutorial I showed you how to automatically convert Microsoft Excel files into Google Sheets using Apps Script. Post conversion, the data from the newly created Google Sheets spreadsheet can also be automatically imported into a master spreadsheet. Hopefully this tutorial helps you automate manual and repetitive workflows to save you time.

Thanks for reading!