Streamsets - Microsoft graph api - Resource url to pick the latest file from the sharepoint document library

Question

I have a sharepoint site that has csv files in the document library pages. I need to extract the latest modified file for each run based on the last run datetime.

So for example, my last run datetime is '2023-07-20T22:50:10Z', then I need to get the file that has been modified after that time along with the @microsoft.graph.downloadUrl.

I use HTTPClient source in Streamsets and use the below as Resource URL. Though it doesnot show any error, I could not get the latest file that has been modified. Please could you help me to get the @microsoft.graph.downloadUrl and the latest file that has been modified using httpclient and microsoft graph api?

Resource URL:

https://graph.microsoft.com/v1.0/sites/company.sharepoint.com,<id>/drives/{driveid}/root/children?select=name,fileSystemInfo,@microsoft.graph.downloadUrl&jql=${str:urlEncode(str:concat(str:concat("fileSystemInfo/lastModifiedDateTime ge'",'2023-07-20T22:50:10Z'),"'"), 'UTF8')}

In the below example, the lastModifiedDateTime": "2023-07-21T00:00:00Z should be filtered and I should get the SAMPLEFILE_2023-07-20 16-10.csv to process.

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('company.sharepoint.com%248e4-48e4-48e4-48e4-48e4')/drives('driveid')/root/children",
    "value": [
        {
            "@microsoft.graph.downloadUrl": "https://samplesite.sharepoint.com/sites/example/layout/15download.aspx?UniqueId=LKAJSDKAJDSLKADOkjklasdjslasdjlsadgasdhakldnkaldnaldihfalfhalksfuegfkaskjhdkladjalkdgasdkajhdkandalkdjaldjaldiuacbskhdaslkdjag2E&ApiVersion=2.0",
            "createdDateTime": "2023-07-20T22:25:00Z",
            "eTag": "\"{ASDASDJASDKAKSDASDASKDASDJ},2\"",
            "id": "ASDASDJASDKAKSDASDASKDASDJ",
            "lastModifiedDateTime": "2023-07-20T22:48:09Z",
            "name": "SAMPLEFILE_2023-07-18 16-10.csv",
            "webUrl": "https://samplesite.sharepoint.com/sites/example/layout/15Doc.aspx?sourcedoc=%123456-09982727HH&file=SAMPLEFILE_2023-07-18%2016-10.csv&action=default&mobileredirect=true",
            "cTag": "\"c:{ASDASDJASDKAKSDASDASKDASDJ},1\"",
            "size": 6985,
            "createdBy": {
                "user": {
                    "email": "[email protected]",
                    "id": "48e4-48e4-48e4-48e4-48e4",
                    "displayName": "Name1"
                }
            },
            "lastModifiedBy": {
                "user": {
                    "email": "[email protected]",
                    "id": "48e4-48e4-48e4-48e4-48e4",
                    "displayName": "Name1"
                }
            },
            "parentReference": {
                "driveType": "documentLibrary",
                "driveId": "sasdajeojejfldjf;ldsoq13793403;sadal;sk;alsdkasSsaKSdjaosdjoasdj",
                "id": "ASDASDJASDKAKSDASDASKDASDJ",
                "path": "/drives/sasdajeojejfldjf;ldsoq13793403;sadal;sk;alsdkasSsaKSdjaosdjoasdj/root:",
                "siteId": "asdadsadad"
            },
            "file": {
                "mimeType": "application/vnd.ms-excel",
                "hashes": {
                    "quickXorHash": "asdadsadad"
                }
            },
            "fileSystemInfo": {
                "createdDateTime": "2023-07-20T22:25:00Z",
                "lastModifiedDateTime": "2023-07-20T22:48:09Z"
            },
            "shared": {
                "scope": "users"
            }
        },
        {
            "@microsoft.graph.downloadUrl": "https://samplesite.sharepoint.com/sites/example/layout/15download.aspx?UniqueId=LKAJSDKAJDSLKADOkjklasdjslasdjlsadgasdhakldnkaldnaldihfalfhalksfuegfkaskjhdkladjalkdgasdkajhdkandalkdjaldjaldiuacbskhdaslkdjag2E&ApiVersion=2.0",
            "createdDateTime": "2023-07-21T00:00:00Z",
            "eTag": "\"{ASDASDJASDKAKSDASDASKDASDJ},2\"",
            "id": "WWASDASDJASDKAKSDASDASKDASDJ",
            "lastModifiedDateTime": "2023-07-21T00:00:00Z",
            "name": "SAMPLEFILE_2023-07-20 16-10.csv",
            "webUrl": "https://samplesite.sharepoint.com/sites/example/layout/15Doc.aspx?sourcedoc=%123456-09982727HH&file=SAMPLEFILE_2023-07-20%2016-10.csv&action=default&mobileredirect=true",
            "cTag": "\"c:{ASDASDJASDKAKSDASDASKDASDJ},1\"",
            "size": 6985,
            "createdBy": {
                "user": {
                    "email": "[email protected]",
                    "id": "48e4-48e4-48e4-48e4-48e4",
                    "displayName": "Name1"
                }
            },
            "lastModifiedBy": {
                "user": {
                    "email": "[email protected]",
                    "id": "48e4-48e4-48e4-48e4-48e4",
                    "displayName": "Name1"
                }
            },
            "parentReference": {
                "driveType": "documentLibrary",
                "driveId": "sasdajeojejfldjf;ldsoq13793403;sadal;sk;alsdkasSsaKSdjaosdjoasdj",
                "id": "ASDASDJASDKAKSDASDASKDASDJ",
                "path": "/drives/sasdajeojejfldjf;ldsoq13793403;sadal;sk;alsdkasSsaKSdjaosdjoasdj/root:",
                "siteId": "asdadsadad"
            },
            "file": {
                "mimeType": "application/vnd.ms-excel",
                "hashes": {
                    "quickXorHash": "asdadsadad"
                }
            },
            "fileSystemInfo": {
                "createdDateTime": "2023-07-21T00:00:00Z",
                "lastModifiedDateTime": "2023-07-21T00:00:00Z"
            },
            "shared": {
                "scope": "users"
            }
        }
    ]
}

Answer 1

I'm afraid that only name and webUrl support filtering:

https://github.com/microsoftgraph/microsoft-graph-docs/issues/17632

https://learn.microsoft.com/en-us/onedrive/developer/rest-api/concepts/filtering-results?view=odsp-graph-online#filterable-properties