Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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).
/**
* 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());
}
@Xerrva

This comment has been minimized.

Copy link

Xerrva commented May 3, 2015

How would I get this to update/overwrite the same file instead of adding new? I want to keep the ID of the new file the same, so that I can reference it in sheets and it will always have the updated data from this script. Thanks!

@yzoldan

This comment has been minimized.

Copy link

yzoldan commented May 21, 2015

I'm also looking for a solution like that. A possible workaround may be to always create a file with a unique name and have the sheet you want to reference from other sheets to automatically retrieve the data from the newly created sheet with a unique name.

@robpetrie

This comment has been minimized.

Copy link

robpetrie commented Oct 20, 2015

Here is that solution.
1.Configuration in Drive must be to standard convert excel to drive sheet in case of upolaoding files.
2. go to the folder with the excel files on Google Drive.
3. Select NEW/ upload files
4. Browse to SYNC FOLDER DRIVE and select the same files for upload
5. Now you have a folder in Drive with both type of files and you can decide to delete what you want.
6 remember: Gsheet has no memory counting for your account, Excel has.

Greetings, Rob Petrie

@tlucido

This comment has been minimized.

Copy link

tlucido commented Oct 20, 2015

Which elements do I actually type in? I'm confused as to which parts are code, and which are notes.

(Sorry - This is my first time creating a function for sheets.)

@boomjynx

This comment has been minimized.

Copy link

boomjynx commented Jan 14, 2016

I'm having trouble getting the output files to get to a particular directory. I've got everything else running thank you.

Can you explain what I need to put into the [ ] please?

Thanks

@archleo

This comment has been minimized.

Copy link

archleo commented Jan 27, 2016

Can you help me with the exact usage of this function? I got a "FORMULA PARSE ERROR"

@BobertinoW

This comment has been minimized.

Copy link

BobertinoW commented May 11, 2016

I looked through the API documentation and found that you can update the same file as long as you put the file id in the URL. You also need to change the method from POST to PUT.
https://www.googleapis.com/upload/drive/v2/files/Uhj_289182JjdkUEJ_1gh?uploadType=media

convert=true is also deprecated so you don't need to include that.

I've probably missed something important, but this was just to get it to work for me. I am by no means a hardcore coder.

@andrewroberts

This comment has been minimized.

Copy link

andrewroberts commented Jun 14, 2016

I couldn't get the file to move by listing the parents in the payload, but using the query string worked:

  var response = UrlFetchApp
    .fetch(
      'https://www.googleapis.com/drive/v2/files/' + TEST_SHEET_ID_ + 
        '?addParents=' + RESULTS_FOLDER_ID_ + 
        '&removeParents=' + parentFolderId,
      updateParams);
@cafless

This comment has been minimized.

Copy link

cafless commented Aug 10, 2016

Greeting - first time posting and am self teaching google scripts so please bare with - I have a great need to use this script but need a little help. Story is I have a Google sheet that I am manually importing 3 separate Microsoft Excel files into to be used by another google sheet that uses this imported data as data set using import range to generate various data break downs and charts. The 3 files being imported are quite large and I am forced to use this method as I cannot put all in on google sheet (it stops me says I need more lines but cannot) I know is a ton of date but that is what I am dealing with, this also needs to happen about every 4 hours and overwrite the files previously imported. File names never change and are always in the same file location. So to the point I think this is the script I need but I'm lost on how to point it to the 3 different excel files located on a share drive that are needing imported into a single google sheet - any help would be greatly appreciated. Thanks in advance - (BTW I believe I have the Drive API ON and ready for this just need to shake out the details in the script)

@davidcon

This comment has been minimized.

Copy link

davidcon commented Aug 15, 2016

I have enabled Google Drive API but despite this the line 23 report an error. Furthermore, "https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true" reports "Method Not Allowed".
Any one can help me?
Tnks

@clemt

This comment has been minimized.

Copy link

clemt commented Jan 4, 2017

Hi All,
Simple question but....
Where do I find the excel file ID?
// ID of Excel file to convert

When working with google sheets files it's easy to find in the url but here I'm lost?

Thanks

@vivekpant87

This comment has been minimized.

Copy link

vivekpant87 commented Jan 6, 2017

Hi

i have mentioned the destination folder id in the variable destFolders still my file is getting saved in root drive .
is there something needs to be edited in the API script ? ( some thing to be changed in between line 34 and 37 ) ???

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 = ['0B2IBO64RQ9WEFDEUTWFCZVhTQ1pk']; // array of IDs of Drive folders to put converted file in; empty array = root folder
var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);

@kharismawans

This comment has been minimized.

Copy link

kharismawans commented Jan 25, 2017

Hi

i have mentioned the destination folder id in the variable destFolders still my file is getting saved in root drive .
is there something needs to be edited in the API script ? ( some thing to be changed in between line 34 and 37 ) ???

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 = ['0B2IBO64RQ9WEFDEUTWFCZVhTQ1pk']; // array of IDs of Drive folders to put converted file in; empty array = root folder
var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);

I HAVE THE SAME EXACTLY QUESTION LIKE THE GUY ABOVE ME, please can u help us ? beceause the file always keep copying in the root folder of my GoogleDrive

thank you for your big help

@alymurph

This comment has been minimized.

Copy link

alymurph commented Feb 8, 2017

@clemt If you go to Share the the Excel file, you will find the id in the URL for the link.

@rcanu

This comment has been minimized.

Copy link

rcanu commented Mar 2, 2017

This is an awesome resource! Works well on my end! Thanks for sharing!

@DavidTWNo1

This comment has been minimized.

Copy link

DavidTWNo1 commented Jun 27, 2017

@vivekpant87
I remove below line from original code since isArray doesn't work in google script. I don't know why....
// if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not
then put the ID string like you did, it success to export spreadsheet into your assigned folder.
var destFolders = ['Folder ID String']; // array of IDs of Drive folders to put converted file in; empty array = root folder
I hope it helps.

@PFreeman008

This comment has been minimized.

Copy link

PFreeman008 commented Aug 29, 2017

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.

@benupham

This comment has been minimized.

Copy link

benupham commented Oct 16, 2017

Any one else getting errors on pinging UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);?

Nvm...a zip file slipped in there I think.

@chasrini

This comment has been minimized.

Copy link

chasrini commented Dec 30, 2017

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.

xls_ref

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

@mooose

This comment has been minimized.

Copy link

mooose commented Apr 25, 2018

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

@votuduc

This comment has been minimized.

Copy link

votuduc commented Jun 29, 2018

@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.

@encotronic

This comment has been minimized.

Copy link

encotronic commented Aug 3, 2019

alguien finalmente sabe como funciona?

@motin

This comment has been minimized.

Copy link

motin commented Jan 5, 2020

@emirkulusoy

This comment has been minimized.

Copy link

emirkulusoy commented Apr 9, 2020

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”

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.