lookup in Excel

[Steve Tassinari]

Is there a way in DFFS to do a vlookup in an Excel file saved on SharePoint? I will have a file saved daily to SharePoint that will need to be the source for profile information when the user selects a specific option.

[Alexander Bautz]

Hi,
Sorry for the delay. I haven’t done this before, but tested the approach from this article: https://sharepoint.stackexchange.com/questions/141881/how-to-access-excel-cells-hosted-on-sharepoint-office-365-using-java-client

In my Office 365 dev site I was able to read the excel file with the following REST snippet. You should be able to add this to a custom function in your DFFS Custom JS:

var documentLibraryName = "Shared Documents";
var excelFileName = "Excel_REST.xlsx";
var excelTableName = "Table1";
jQuery.ajax({
    "url":_spPageContextInfo.webServerRelativeUrl + "/_vti_bin/ExcelRest.aspx/"+documentLibraryName+"/"+excelFileName+"/OData/"+excelTableName,
    "method": "GET",
    "headers": { "Accept": "application/json; odata=verbose" },
    "success": function (data) {
        console.log(data);
        // do your magic with custom code here
    },
    "error": function (err) {
        console.log(err);
    }
});

I have attached the example Excel file.

Hope you can use this example,
Alexander

Attachments:

1. Excel_REST.xlsx

[Author]

Posts