Skip to content

Instantly share code, notes, and snippets.

@azadisaryev
Last active March 9, 2024 18:23
Show Gist options
  • Save azadisaryev/ab57e95096203edc2741 to your computer and use it in GitHub Desktop.
Save azadisaryev/ab57e95096203edc2741 to your computer and use it in GitHub Desktop.
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());
}
@ayyanali-36
Copy link

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/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment