Google Apps Script for converting Excel (.xls or .xlsx) file to Google Spreadsheet. Drive API must be enabled in your script's Advanced Google Services and in Developers Console for the script to work (see https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services for details).

convertExcel2Sheets

/**
 * Convert Excel file to Sheets
 * @param {Blob} excelFile The Excel file blob data; Required
 * @param {String} filename File name on uploading drive; Required
 * @param {Array} arrParents Array of folder ids to put converted file in; Optional, will default to Drive root folder
 * @return {Spreadsheet} Converted Google Spreadsheet instance
 **/
function convertExcel2Sheets(excelFile, filename, arrParents) {
  
  var parents  = arrParents || []; // check if optional arrParents argument was provided, default to empty array if not
  if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not
  
  // Parameters for Drive API Simple Upload request (see https://developers.google.com/drive/web/manage-uploads#simple)
  var uploadParams = {
    method:'post',
    contentType: 'application/vnd.ms-excel', // works for both .xls and .xlsx files
    contentLength: excelFile.getBytes().length,
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    payload: excelFile.getBytes()
  };
  
  // Upload file to Drive root folder and convert to Sheets
  var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);
    
  // Parse upload&convert response data (need this to be able to get id of converted sheet)
  var fileDataResponse = JSON.parse(uploadResponse.getContentText());

  // Create payload (body) data for updating converted file's name and parent folder(s)
  var payloadData = {
    title: filename, 
    parents: []
  };
  if ( parents.length ) { // Add provided parent folder(s) id(s) to payloadData, if any
    for ( var i=0; i<parents.length; i++ ) {
      try {
        var folder = DriveApp.getFolderById(parents[i]); // check that this folder id exists in drive and user can write to it
        payloadData.parents.push({id: parents[i]});
      }
      catch(e){} // fail silently if no such folder id exists in Drive
    }
  }
  // Parameters for Drive API File Update request (see https://developers.google.com/drive/v2/reference/files/update)
  var updateParams = {
    method:'put',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    contentType: 'application/json',
    payload: JSON.stringify(payloadData)
  };
  
  // Update metadata (filename and parent folder(s)) of converted sheet
  UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);
  
  return SpreadsheetApp.openById(fileDataResponse.id);
}

/**
 * Sample use of convertExcel2Sheets() for testing
 **/
 function testConvertExcel2Sheets() {
  var xlsId = "0B9**************OFE"; // ID of Excel file to convert
  var xlsFile = DriveApp.getFileById(xlsId); // File instance of Excel file
  var xlsBlob = xlsFile.getBlob(); // Blob source of Excel file for conversion
  var xlsFilename = xlsFile.getName(); // File name to give to converted file; defaults to same as source file
  var destFolders = []; // array of IDs of Drive folders to put converted file in; empty array = root folder
  var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);
  Logger.log(ss.getId());
}

This code has worked for me. Thanks a lot.
While the code works for XLS which are self contained ( cells dont refer to other XLS files ), it doesnt work for XLS which have cells
with links to another XLS ( and that XLS is not available to you, like in the attached image ). XLS itself shows the value but warns about the missing reference file.

The converted sheet has REF! as the cell values where there is a reference in the XLS.

Is there a way to read the values in the XLS rather than the referred values

about the isArray - here is a working line for it:

if ( !Array.isArray(parents) ) parents = []; // make sure parents is an array, reset to empty array if not

@PFreeman008 : I have the same problem like you. Did you find out the solution?

Thanks for that David, been pounding my head against the wall over it! Should have checked here first.

Is it possible to automate this script? I'm over my head here, but need a way to take uploaded xlsm files and have them be converted to sheets files in "bulk". I've figured out how to get this script to work, but it appears to be set up for an individual file and not to loop thru a folder and convert what it finds there.

alguien finalmente sabe como funciona?

Relevant alternative solution: https://ctrlq.org/code/20500-convert-microsoft-excel-xlsx-to-google-spreadsheet

Thank you very much for this post.

I have a question. Could you please share feedback if you have time.

I have a googleform that collects excel file from the users and uploads to the google drive. As I understand, I should transfer from excel file to spreadsheet to open and get the data. So I use “Drive.Files.insert()” to convert the file (as you mentioned), which requires additional approvals for the script. I enabled the Google Drive API under Resources > Advanced Google Services on google script and linked this google script to a google cloud project. Google Drive API is also enabled on google cloud with “OAuth 2.0 Client IDs “. But my script still cannot pass the google login process and only returns a word file with a google login page.

the script:
var Blob01 = UrlFetchApp.fetch(thelink).getBlob();
var fileInfo = { title: “test1”, mimeType: MimeType.GOOGLE_SHEETS, “parents”: [{‘id’: FolderId}], };
Drive.Files.insert(fileInfo, Blob01, {convert: true});

OAuth settings: Scopes for Google APIs >> ../auth/drive and ../auth/drive.file credentials: OAuth client ID >> Web application google drive API has been enabled and credentials are added as “Google Drive API” & “web browser” & “User data”

If the xls file is hosted on another website as a link, what would I need to modify to skip the google drive part?

Hi....I am getting below error:

Exception: Request failed for https://www.googleapis.com returned code 403. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "insufficientPermissions", "message": "Insufficient Permission: Request ... (use muteHttpExceptions option to examine full response) (line 23, file "Code")

Hi....I am getting below error:

Exception: Request failed for https://www.googleapis.com returned code 403. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "insufficientPermissions", "message": "Insufficient Permission: Request ... (use muteHttpExceptions option to examine full response) (line 23, file "Code")

I am getting the same error. Anyone know what I'm missing?

Relevant alternative solution: https://ctrlq.org/code/20500-convert-microsoft-excel-xlsx-to-google-spreadsheet

motins solution was perfect for me and worked great.

This is a really useful script, but for those who are more versed in scripts. I tried for a long time to explain to my colleague how to use it, but it was hard for him, so I found an easier way to convert, and here it is https://blog.coupler.io/convert-excel-to-google-sheets/

Hello. I'm self-learning google scripts so please bear with me. I have a great need for this script. Basically, I have a Google sheet that I am manually importing three separate Microsoft Excel files into in order to be used by another google sheet that uses this imported data as a set of data using import ranges to generate various data break downs and charts based on this import data set. Since the three files I'm importing are large (I can't put all of them in on Google Sheets because it says I need more lines, but I can't), I have to use this method. I know this is a lot of data, but that's what I'm dealing with, and it needs to be overwritten every four hours. https://www.mirajwala.com/