How to Convert XLSX to Sheets in Google Drive API v3

Question

I want to convert xlsx files automatically to Google spreadsheets when I upload them with my code to Google Drive. However, while the conversion works successfully for csv files, I get:

<HttpError 400 when requesting https://www.googleapis.com/upload/drive/v3/files?uploadType=resumable&alt=json returned "Bad Request">

when trying to upload xlsx.

Here is my code:

def upload_service(filepath, name="", description="", fileID="", parentID=""):
    """ Uses a Resource (service) object to upload a file to drive. """

    if service == "": authenticate_service()

    if name == "":
        name = str(os.path.basename(filepath).split(os.extsep)[0])   # Get from filepath

    extension = str(os.path.basename(filepath).split(os.extsep)[1]).lower()

    if extension == "csv":                  # CSV
        mime_type = "text/csv"
    elif extension in ["xls", "xlsx"]:      # EXCEL
        mime_type = "application/ms-excel"
    else:
        return

    media_body = MediaFileUpload(filepath, mimetype=mime_type, resumable=True)

    if parentID == "":
        meta = dict(name=name, mimeType="application/vnd.google-apps.spreadsheet", description=description)
    else:
        meta = dict(name=name, mimeType="application/vnd.google-apps.spreadsheet", description=description, parents=[parentID])

    if fileID == "":   # CREATE 
        upload = service.files().create(
                                    body=meta,
                                    media_body=media_body).execute()
    else:   # REPLACE
        upload = service.files().update(
                                body=meta,
                                media_body=media_body,
                                fileId=fileID).execute()

    print ("\nFINISHED UPLOADING")

How can I do this in v3? It's very clear how to do it in v2, but not in the updated API.

Answer 1

In APIv3, you need to specify a very specific MIME Type for the conversion to occur.

At https://developers.google.com/drive/v3/web/manage-uploads#importing_to_google_docs_types_wzxhzdk8wzxhzdk9, you'll notice the statement "The supported conversions are available dynamically in the About resource's importFormats array". Get the importFormats list using either

GET https://www.googleapis.com/drive/v3/about?fields=importFormats&key={YOUR_API_KEY}

or by going to https://developers.google.com/drive/v3/reference/about/get#try-it and entering importFormats

You'll notice in the response:

"application/vnd.ms-excel": [
   "application/vnd.google-apps.spreadsheet"
]

In your code, use:

elif extension in ["xls", "xlsx"]:      # EXCEL
    mime_type = "application/vnd.ms-excel"

(notice the additional vnd.)and it should work well!

Answer 2

Based on the Official Google Documentation, You received 400: Bad Request meaning that required field or parameter has not been provided, the value supplied is invalid or the combination of provided fields is invalid. This error can be thrown when trying to add a parent that would create a cycle in the directory graph.

When encountering this error, the suggested action is to use exponential backoff. it is a standard error handling strategy for network applications in which the client periodically retries a failed request over an increasing amount of time.

You can use the Official Google Docs for your reference, There is a parameter convert convert=true, to convert file to the corresponding Google Docs format (Default:false).

You also need to use the Python client library, you can use that library to support uploading files.

Found this Stack Overflow ticket, check the solution offered by community: python + google drive: upload xlsx, convert to google sheet, get sharable link

Answer 3

def uploadExcel(excelFileName):
   file_metadata = {'name': excelFileName, 'parents': [folderId], 'mimeType': 'application/vnd.google-apps.spreadsheet'}
   media = MediaFileUpload(excelFileName, mimetype='application/vnd.ms-excel', resumable=True)
   file = service.files().create(body=file_metadata, media_body=media, fields='id').execute()

Answer 4

The logic to have is: We want to create a spreadsheet from an excel format.

So we code exactly with this logic (example for C#):

Google.Apis.Drive.v3.Data.File fileMetadata = new Google.Apis.Drive.v3.Data.File();
            fileMetadata.Name = System.IO.Path.GetFileName(file_being_uploaded);
            fileMetadata.Description = "File created via Google Drive API C#";
            fileMetadata.MimeType = "application/vnd.google-apps.spreadsheet";
            fileMetadata.Parents = new List<string> { _parent };    // if you want to organize in some folder       

            // File content.
            byte[] byteArray = System.IO.File.ReadAllBytes(file_being_uploaded);
            System.IO.MemoryStream stream = new System.IO.MemoryStream(byteArray);
            try
            {
                FilesResource.CreateMediaUpload request = _service.Item1.Files.Create(fileMetadata, stream, GetMimeType(file_being_uploaded));
(...)

    // gets us the Excel Mime
    private static string GetMimeType(string fileName)
    {
        string mimeType = "application/unknown";
        string ext = System.IO.Path.GetExtension(fileName).ToLower();
        Microsoft.Win32.RegistryKey regKey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(ext);
        if (regKey != null && regKey.GetValue("Content Type") != null)
            mimeType = regKey.GetValue("Content Type").ToString();
        return mimeType;
    }