Skip to main content

Hi,

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 and also the @microsoft.graph.downloadUrl. I use HTTPClient source in Streamsets and use the below as Resource URL. Though it doesn't show any error, I could not get the latest file that has been modified.

Please could you help me to know how to get the @microsoft.graph.downloadUrl and the latest file that has been modified using httpclient? 

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": "firstname.lastname@vvv.com",
                    "id": "48e4-48e4-48e4-48e4-48e4",
                    "displayName": "Name1"
                }
            },
            "lastModifiedBy": {
                "user": {
                    "email": "firstname.lastname@vvv.com",
                    "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": "firstname.lastname@vvv.com",
                    "id": "48e4-48e4-48e4-48e4-48e4",
                    "displayName": "Name1"
                }
            },
            "lastModifiedBy": {
                "user": {
                    "email": "firstname.lastname@vvv.com",
                    "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"
            }
        }
    ]
}

 

@anand_rajan the HTTP client origin expect a resource URL, method and headers(optional)

Therefore the resource URL you are configuring needs to the URL that’ll return the data you are expecting. I’d suggest you to try this out with a REST client and get a working set of resource URL/method/headers etc after which it’ll be easier to replicate the same setup using the HTTP client origin. 

Reply


Terms & Conditions