Get files properties (on premises) not giving identifier - SharePoint (On-premises) data gateway

[gbdavid2]

Hi, 

I need to get all the files from a specific SharePoint document library I have in my on-premises SharePoint 2016 server. I will need to get all the files, and then for each file I will have some logic to fill the columns. This is what I do:

1. Manually trigger a flow

2. Get files (properties only)

3. Apply for each

   3a. Get file metatada (I use the Identifier property from step 2).

   3b. Other operations using file metadata etc...

When I tested this behaviour initially connecting to a test SharePoint online system, I got the data with no issues, and I got something like this in step 2:

{
  "value": [
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "4",
      "ID": 4,
      "Modified": "2018-10-30T13:58:28Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.f|membership|david@somedomain.com",
        "DisplayName": "David",
        "Email": "david@somedomain.com",
        "Picture": "https://somedomain.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=david@somedomain.com",
        "Department": "",
        "JobTitle": ""
      },
      "Editor#Claims": "i:0#.f|membership|david@somedomain.com",
      "Created": "2018-10-30T13:58:28Z",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.f|membership|david@somedomain.com",
        "DisplayName": "David",
        "Email": "david@somedomain.com",
        "Picture": "https://somedomain.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=david@somedomain.com",
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.f|membership|david@somedomain.com",
      "{Identifier}": "Shared%2bDocuments%252fbcs",
      "{IsFolder}": true,
      "{Link}": "https://somedomain.sharepoint.com/Shared%20Documents/bcs",
      "{Name}": "bcs",
      "{FilenameWithExtension}": "bcs",
      "{Path}": "Shared Documents/"
    },
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "5",
      "ID": 5,
      "Modified": "2018-10-30T13:58:28Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.f|membership|david@somedomain.com",
        "DisplayName": "David",
        "Email": "david@somedomain.com",
        "Picture": "https://somedomain.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=david@somedomain.com",
        "Department": "",
        "JobTitle": ""
      },
      "Editor#Claims": "i:0#.f|membership|david@somedomain.com",
      "Created": "2018-10-30T13:58:28Z",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.f|membership|david@somedomain.com",
        "DisplayName": "David",
        "Email": "david@somedomain.com",
        "Picture": "https://somedomain.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=david@somedomain.com",
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.f|membership|david@somedomain.com",
      "{Identifier}": "Shared%2bDocuments%252fbcs%252fbbb",
      "{IsFolder}": true,
      "{Link}": "https://somedomain.sharepoint.com/Shared%20Documents/bcs/bbb",
      "{Name}": "bbb",
      "{FilenameWithExtension}": "bbb",
      "{Path}": "Shared Documents/bcs/"
    },
    {
      "@odata.etag": "\"10\"",
      "ItemInternalId": "1",
      "ID": 1,
      "Modified": "2018-10-31T09:51:27Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.f|membership|david@somedomain.com",
        "DisplayName": "David",
        "Email": "david@somedomain.com",
        "Picture": "https://somedomain.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=david@somedomain.com",
        "Department": "",
        "JobTitle": ""
      },
      "Editor#Claims": "i:0#.f|membership|david@somedomain.com",
      "Code": "ASB-111",
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Approved"
      },
      "Status#Id": 0,
      "Description0": "My Analysis 1",
      "Created": "2018-10-29T22:14:36Z",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.f|membership|david@somedomain.com",
        "DisplayName": "David",
        "Email": "david@somedomain.com",
        "Picture": "https://somedomain.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=david@somedomain.com",
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.f|membership|david@somedomain.com",
      "{Identifier}": "Shared%2bDocuments%252fanalysis1.pdf",
      "{IsFolder}": false,
      "{Link}": "https://somedomain.sharepoint.com/Shared%20Documents/analysis1.pdf",
      "{Name}": "analysis1",
      "{FilenameWithExtension}": "analysis1.pdf",
      "{Path}": "Shared Documents/"
    }, 

Now, when I create the same Flow and try the functionality connecting to my On-premises SharePoint 2016, I get an output similar to this body:

{
  "@odata.context": "https://sharepointonline-je.azconn-je.p.azurewebsites.net/$metadata#items",
  "value": [
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "5",
      "Bulletin_x0020_Number": null,
      "Issue_x0020_Date": null,
      "Title": null,
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": -1,
        "Value": null
      },
      "Status#Id": -1,
      "Created": "2018-11-06T15:28:02Z",
      "SharedWithUsers": null,
      "ID": 5,
      "Modified": "2018-11-06T15:28:02Z",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.w|mydomain\\myuser",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Editor#Claims": "i:0#.w|mydomain\\myuser"
    },
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "3",
      "Bulletin_x0020_Number": null,
      "Issue_x0020_Date": null,
      "Title": null,
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Approved"
      },
      "Status#Id": 0,
      "Created": "2018-11-06T15:27:21Z",
      "SharedWithUsers": null,
      "ID": 3,
      "Modified": "2018-11-06T15:27:21Z",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.w|mydomain\\myuser",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Editor#Claims": "i:0#.w|mydomain\\myuser"
    },
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "4",
      "Bulletin_x0020_Number": null,
      "Issue_x0020_Date": null,
      "Title": null,
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Approved"
      },
      "Status#Id": 0,
      "Created": "2018-11-06T15:27:22Z",
      "SharedWithUsers": null,
      "ID": 4,
      "Modified": "2018-11-06T15:27:22Z",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.w|mydomain\\myuser",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Editor#Claims": "i:0#.w|mydomain\\myuser"
    },
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "6",
      "Bulletin_x0020_Number": null,
      "Issue_x0020_Date": null,
      "Title": null,
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Approved"
      },
      "Status#Id": 0,
      "Created": "2018-11-06T15:28:24Z",
      "SharedWithUsers": null,
      "ID": 6,
      "Modified": "2018-11-06T15:28:24Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Editor#Claims": "i:0#.w|mydomain\\myuser",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.w|mydomain\\myuser"
    },
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "7",
      "Bulletin_x0020_Number": null,
      "Issue_x0020_Date": null,
      "Title": null,
      "Created": "2018-11-06T15:28:24Z",
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Approved"
      },
      "Status#Id": 0,
      "SharedWithUsers": null,
      "ID": 7,
      "Modified": "2018-11-06T15:28:24Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Editor#Claims": "i:0#.w|mydomain\\myuser",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.w|mydomain\\myuser"
    },
    {
      "@odata.etag": "\"1\"",
      "ItemInternalId": "8",
      "Bulletin_x0020_Number": null,
      "Issue_x0020_Date": null,
      "Title": null,
      "Created": "2018-11-06T15:28:25Z",
      "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Approved"
      },
      "Status#Id": 0,
      "SharedWithUsers": null,
      "ID": 8,
      "Modified": "2018-11-06T15:28:25Z",
      "Editor": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Editor#Claims": "i:0#.w|mydomain\\myuser",
      "Author": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        "Claims": "i:0#.w|mydomain\\myuser",
        "DisplayName": "User, My",
        "Email": "",
        "Picture": null,
        "Department": null,
        "JobTitle": null
      },
      "Author#Claims": "i:0#.w|mydomain\\myuser"
    }
  ]
}

Notice that the output of Get files (properties only) connecting to the On-premises SharePoint is not retrieving any Identifier. I won't be able to further do any operations in step 3 because when I'm at each item of the for each, I can't find the specific file that I'm looking for (I need the Identifier, and all I have is the ID which doesn't really match to anything). Do you know why this is happening? Any ideas of an alternative to get the metadata of all the files in a SharePoint document library in On-premises using Flow?

[v-yuazh-msft]

Hi @gbdavid2,

Have you take a try to add a "Get file properties" action before the "Get file metadata" action?

Please take a try with screenshot below:

Best regards,

Alice

[gbdavid2]

Hi Alice,

Yes, I tried that, which does work fine in SharePoint online, but not connecting to the On-Premises gateway. I feel like the On-Premises gateway is more restricted? But if so, it seems impossible to locate an item in SharePoint becuase we do need the identifier (or even the path or file name, which is also not available).

I tried a different approach to make a trigger for when the user creates the file in the SharePoint library. This one does give me the File identifier, but it's an action that doesn't trigger if I create a file in a sub-folder of the library, and I need the actions applied to all the files in all the subfolders too (and this is why I was using the Get files action which does give me all the files).

On a second thought... is it possible that there is a setting that is hidding the File identifier at the on-premises server side? if so, can something be changed in the site / library settings to expose it??