GraphAPI | API to fetch Sharepoint file details

[Meet Mewada]

Hi Team,

I am storing a SharePoint URL in SNOW, and would like to fetch the file details using the same. Let me know if there is a way to achieve this either via REST or any other means.

So far, I followed below steps for connectivity:

1. Access to SharePoint to get the OAuth Bearer Token:

2. Successfully authenticated to SharePoint site i.e. https://graph.microsoft.com/v1.0/sites/mysharepoint.sharepoint.com:/sites/ABC/

The next step is to provide a query to fetch the file details using the share link where I am stuck. The Service Account which has been used, already has Read access to the site itself, and received below API Response. Refer below output.

{
    "error": {
        "code": "invalidRequest",
        "innerError": {
            "code": "badArgument"
        },
        "message": "The provided shares id is not valid."
    }
}

Refer below endpoint that we used to get the file details.

https://graph.microsoft.com/v1.0/shares/ENCODED_URL_ABC  

Note:

ENCODED_URL_ABC  was generated as per the source Sharepoint Client API: Get file name from sharing link - Microsoft Q&A

@Community Alums 

Regards,

Meet M

[Ratnakar7]

Hi @Meet Mewada ,

This error typically occurs when the shares ID used in the API request is incorrect or invalid.

Here are a few steps you can take to troubleshoot and resolve the issue:

1. Ensure that the shares ID (ENCODED_URL_ABC) used in the API request is correct and properly encoded. Double-check the format and structure of the shares ID to ensure it matches the expected format for SharePoint file URLs.

2. Verify that the Service Account used to authenticate to SharePoint has the necessary permissions to access the file details. Ensure that the account has at least read access to the SharePoint site and the specific file you're trying to access.

3. Check the endpoint URL (https://graph.microsoft.com/v1.0/shares/ENCODED_URL_ABC) to ensure it is formatted correctly and matches the syntax expected by the Microsoft Graph API. Make sure there are no typos or errors in the URL.

4. Consider testing the API request using a tool like Postman or cURL to isolate the issue and verify if the problem persists. This can help identify if the issue is specific to your integration code or if it's a broader issue with the SharePoint API or permissions.

5. Review the Microsoft Graph API documentation and any relevant SharePoint documentation to ensure you're using the correct API endpoints and parameters for fetching file details from SharePoint. The documentation may provide additional guidance or examples that can help troubleshoot the issue.

Thanks,

Ratnakar

[Community Alums · Accepted solution]

Hi @Meet Mewada 

Please find the below solution-

First thing to understand is the encoding the URL, below is the code to generate the same:

var shareLink = "https://tenant.sharepoint.com/:u:/s/Site/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx--Q?e=6TY7g0";
var base64Value = GlideStringUtil.base64Encode(shareLink);
var encodedUrl = "u!" + base64Value.split('=').join('').split('/').join('_').split('+').join('-');
gs.print(encodedUrl);

Result:
u!aHR0cHM6Ly9iY2djbG91ZC5zaGFyZXBvaW50LmNvbxxxxxxxxxxxxxxxxxxxxxxxwSWlZSF9VUkR5TWFAtLVE_ZT02VFk3ZzA

One you have the encodedurl use the below endpoint to get the file info:

Endpoint:

https://graph.microsoft.com/v1.0/shares/u!aHR0cHM6Ly9iY2djbG91ZC5zaGFyZXBvaW50LmNvbxxxxxxxxxxxxxxxxxxxxxxxwSWlZSF9VUkR5TWFAtLVE_ZT02VFk3ZzA/driveItem

Note: /driveItem - it is used to get all the info of the file i.e., Name, modification date, created date, webUrl, etc.,

Below is the sample response-

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(driveItem)/$entity",
    "@microsoft.graph.downloadUrl": "https://tenant.sharepoint.com/sites/Site/_layouts/15/download.aspx?UniqueId=xxxxxxxxxxxxxxxxxxxxxxx&Translate=false&tempauth=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&ApiVersion=2.0",
    "@microsoft.graph.Decorator": "decorator has been deprecated. Refer to folder.decorator",
    "createdBy": {
        "user": {
            "email": "xxxxxxxxxx",
            "displayName": "xxxxxxxxxx"
        }
    },
    "createdDateTime": "2024-03-22T00:03:11Z",
    "eTag": "\"{xxxxxxxxxx},8\"",
    "id": "xxxxxxxxxx",
    "lastModifiedBy": {
        "user": {
            "email": "xxxxxxxxxx",
            "displayName": "xxxxxxxxxx"
        }
    },
    "lastModifiedDateTime": "2024-03-22T00:02:47Z",
    "name": "abc.pdf",
    "parentReference": {
        "driveType": "documentLibrary",
        "driveId": "b!xxxxxxxxxx",
        "id": "xxxxxxxxxx",
        "name": "xxxxxxxxxx",
        "path": "/drives/b!xxxxxxxxxx/root:/xxxxxxxxxx",
        "siteId": "xxxxxxxxxx"
    },
    "webUrl": "https://tenant.sharepoint.com/sites/Site/xxxxxxxxxx/abc.pdf",
    "cTag": "\"c:{xxxxxxxxxx},2\"",
    "file": {
        "hashes": {
            "quickXorHash": "xxxxxxxxxx="
        },
        "mimeType": "application/octet-stream"
    },
    "fileSystemInfo": {
        "createdDateTime": "2024-03-22T00:03:11Z",
        "lastModifiedDateTime": "2024-03-22T00:02:47Z"
    },
    "shared": {
        "scope": "users"
    },
    "size": xxxxxxxxxx
}

Hope it helps in resolving your issue.

If my response has resolved your query, please consider giving it a thumbs up ‌‌ and marking it as the correct answer‌‌!

Thanks & Regards,

Sanjay Kumar Gupta

[Meet Mewada]

Thanks a lot Sanjay! This article was really helpful to understand how SPO links can be configured in SNOW.

[John Martin2]

Thank you for your inquiry about retrieving SharePoint file details using a stored URL in ServiceNow.

While ServiceNow's native capabilities and REST APIs can be used to interact with SharePoint to some extent, DTech Apps DocIntegrator offers a much more robust and streamlined solution, especially for complex scenarios like fetching and displaying file metadata.

Here's how DocIntegrator addresses your need:

Challenges with Native ServiceNow/REST:

• Complexity of REST API Calls:
  • Using SharePoint's REST API directly from ServiceNow often involves complex scripting to handle authentication, construct the correct API calls, parse the responses, and manage potential errors.
  • This can require significant development effort and ongoing maintenance.
• Authentication:
  • Managing authentication between ServiceNow and SharePoint (especially with different authentication methods) can be tricky and may require careful security considerations.
• Data Mapping:
  • Extracting and mapping the specific file details you need (e.g., file name, size, modification date, author) from the SharePoint API response can require further scripting and data manipulation.
• Performance:
  • Repeatedly making REST API calls to SharePoint to fetch file details for multiple records can impact performance, especially if you need to display this information in real-time.

DocIntegrator's Solution:

DTech Apps DocIntegrator simplifies this process significantly by providing a pre-built integration between ServiceNow and SharePoint:

• Simplified Configuration:
  • DocIntegrator allows you to configure the connection to SharePoint and define how file details are retrieved without extensive coding.
  • It provides a user-friendly interface to map SharePoint file properties to ServiceNow fields.
• Automated Data Retrieval:
  • Once configured, DocIntegrator can automatically fetch and display the desired file details within ServiceNow forms or lists, using the stored SharePoint URL.
  • This can happen in real-time or be triggered by specific events within ServiceNow workflows.
• Enhanced Performance:
  • DocIntegrator is designed to optimize data retrieval from SharePoint, minimizing performance impact on your ServiceNow instance.
• Security and Control:
  • DocIntegrator respects SharePoint's existing security and permissions, ensuring that users only see the file details they are authorized to access.

Specific Use Cases and Examples:

• Document Management:
  • If you have a ServiceNow record that stores a SharePoint URL to a contract, DocIntegrator can automatically fetch and display the contract's file size, modification date, and author within the ServiceNow record.
• Project Management:
  • In a ServiceNow PPM project, you could store URLs to project documents in SharePoint. DocIntegrator can then retrieve and show the version history or status of those documents.
• Knowledge Management:
  • If your knowledge articles in ServiceNow link to supporting documents in SharePoint, DocIntegrator can display relevant file information (e.g., last modified date) to ensure users have the latest version.

In summary:

While you could achieve this using ServiceNow's REST API, DTech Apps DocIntegrator offers a much more efficient, reliable, and user-friendly solution for fetching and displaying SharePoint file details from stored URLs. It simplifies the integration, automates data retrieval, and ensures security and performance.

I'd be happy to schedule a brief call to demonstrate how DocIntegrator can be configured to meet your specific requirements.