Converting .xlsx files to Google Sheets in C#

December 12, 2020

In my previous post I looked at handling hyperlinks when importing Google spreadsheets via the C# SDK. Today, I continue the GSuite theme with a short piece on importing Excel workbooks into the Google Sheets format via the C# SDK. (Since these posts are so closely related, I decided to post them together.)

A client working in an industry where a lot of data is shared via spreadsheet asked me to build a process to automate importing this stuff into a database. Given Google Sheets now supports editing xlsx files directly, the client keeps most of them on Google Drive. However, these xlsx files cannot be processed via the API. In order to save the client the trouble of converting them all, I decided to implement a process to temporarily import an Excel workbook into Sheets. Again, this process isn’t well-documented, but is reasonably straightforward.

The following function takes an instance of the DriveService and the Drive id of the uploaded xlsx file, and returns the id of the newly-created spreadsheet. (It could easily be adapted to take a stream instead.)

async Task<string> XlxsToGoogleSheets(DriveService service, string fileId, string newFileName)
{
    // Write xlsx file in Drive to a memory stream
    await using var stream = new MemoryStream();
    var excelDownload = await service.Files.Get(fileId).DownloadAsync(stream);

    const int pollInterval = 500; // ms
    const int timeout = 30000;

    var elapsed = 0;
    while (excelDownload.Status != DownloadStatus.Completed && excelDownload.Status != DownloadStatus.Failed && elapsed < timeout)
    {
        Thread.Sleep(TimeSpan.FromMilliseconds(pollInterval));
        elapsed += pollInterval;
    }

    if (excelDownload.Status != DownloadStatus.Completed)
        throw excelDownload.Exception ?? new TimeoutException();

    // Create temporary Google spreadsheet
    var sheetsFile = new File
    {
        Name = newFileName, // when testing this, I found the new name needed an .xlsx extension, but was unable to determine why
        MimeType = "application/vnd.google-apps.spreadsheet"
    };

    var request = service.Files.Create(sheetsFile, stream, sheetsFile.MimeType);
    var uploadStatus = await request.UploadAsync();

    elapsed = 0;
    while (uploadStatus.Status != UploadStatus.Completed && uploadStatus.Status != UploadStatus.Failed && elapsed < timeout)
    {
        Thread.Sleep(TimeSpan.FromMilliseconds(pollInterval));
        uploadStatus = request.GetProgress();
        elapsed += pollInterval;
    }

    // Check status and error on failure
    if (uploadStatus.Status != UploadStatus.Completed)
        throw uploadStatus.Exception ?? new TimeoutException();

    return request.ResponseBody.Id;
}

The SDK is a bit clunky, especially the need for polling the download/upload, so I’ve encapsulated it in a method away from the rest of the code. In my application, I delete the Google spreadsheet after processing the data. The original xlsx file is of course untouched.

In my next post, I’ll be talking about how I set up this blog, and why I opted for static content over a traditional CMS.