lookup in Excel

Forums Requests lookup in Excel

This topic contains 1 reply, has 2 voices, and was last updated by  Alexander Bautz 2 months, 1 week ago.

  • Author
    Posts
  • #17710

    Steve Tassinari
    Participant

    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.

  • #17740

    Alexander Bautz
    Keymaster

    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

You must be logged in to reply to this topic.