Search code examples
excelmicrosoft-graph-apionedrive

Microsoft Graph API unable to update Excel file

I have an Excel file (.xlsx), which when I have it already in my OneDrive, I can use a REST command like this, to modify it:

/v1.0/me/drive/root:/SpreadSheetName.xlsx:/workbook/worksheets/content_stats/tables('RawStats')/Rows

When I modify my code to first upload the file to OneDrive (rather than using the file that is already there), and I use the REST API, I get the error:

Open navigation properties are not supported on OpenTypes. Property name: 'tables'.

I have searched the web for this message, and cannot find anything related to what I am doing. The REST call for modifying the file which was just uploaded is nearly identical, although I do reference the file by ID instead, as that is what is returned by the upload API. This is the URL I use to modify the file which was just uploaded.

/v1.0/me/drive/items:/<RealExcelSpreadsheetID>:/workbook/worksheets/content_stats/tables('RawStats')/Rows

Both are doing a POST. Exact same file, only difference is it is being uploaded first, rather than already being in OneDrive. The file was definitely uploaded correctly, as when I go through the OneDrive web interface I do find it and can view it online. This is a business account.

It was uploaded as MIME type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.

It uses these scopes delegated via OAuth2:

  • User.Read
  • User.ReadWrite
  • Files.Read
  • Files.ReadWrite
  • Files.ReadWrite.All
  • Sites.ReadWrite.All

Using Node.js and JavaScript, although that should't matter.

Here is the code used to upload the file:

function copyTemplateInOneDrive(res, queryParam, officeAccessToken, callback) {
    var fs = require('fs');
    var excelExt = ".xlsx";
    var excelSpreadsheetFilenameStart = "stats";

    var uploadUrl = "https://graph.microsoft.com/v1.0/me/drive/root:/" +
        excelSpreadsheetFilename + dateNowFull() + excelExt + ":/content";
    var xlsxMimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    fs.readFile(excelSpreadsheetTemplateFilename, function read(error, fileContent) {
        var request = require('request');
        var options = {
            url: uploadUrl,
            headers: {
                'Accept': 'application/json',
                'Authorization': 'Bearer ' + officeAccessToken,
                'Content-Type': xlsxMimeType,
            },
            body: fileContent
        };
        request.put(options, function (error, response, body) {
            var result = JSON.parse(body);
            if ('id' in result) {
                console.log("Successfully uploaded template file to OneDrive");
                res.write("Successfully uploaded template file to OneDrive");

                var excelSpreadsheetID = result.id;
                excelSpreadsheetUrl = result.webUrl;
            } else {
                console.log("ERROR: unable to upload template file to OneDrive " + body);
                res.write("Error: unable to upload template file to OneDrive" + body);
                return;
            }
            callback(null, res, queryParam, officeAccessToken, excelSpreadsheetID);
        });
    });
}

It uses the async module from node.js (which makes use of callback). It also saves the ID returned, and later passes it into the call to the Microsoft Graph.

Solution

  • Your issue here is the URL you're calling with the id is using syntax expecting the file path (i.e. folder\filename.ext) rather than the id. This is why switching to the file name started working for you.

    There are two ways to address a file stored in OneDrive:

    1. drive/items/{item-id}
    2. /drive/root:/path/to/file (note the :)

    You correctly switched your URI from drive/root to drive/items but by leaving the : in place you are telling OneDrive to address the file by it's path rather than it's id. In other words, it's looking for a file named "{some-id}".

    For addressing a file by it's path, your URL is correct:

    /drive/root:/{file-path}:/workbook/worksheets/content_stats/tables('RawStats')/Rows

    For addressing a file by it's id however, you need to drop the ::

    /drive/items/{file-id}/workbook/worksheets/content_stats/tables('RawStats')/Rows

    You can read about how files are addressed in the documentation for DriveItem.