How to extract data from an Excel file on Sharepoint?

[#1]

Hi all,

I have a script setup to open an Excel file, extract the required data, run some analysis, save the analysis as an interactive HTML (for access by a non JMP user), and close JMP down. It all works fine in my pilot script and as a non-coder I’m especially pleased to have figured it all out. The challenge now is that this must work between two completely separate companies. Both companies have access to a common Excel file via Sharepoint. I have been given access to the file on Sharepoint. After much ‘messing’ and then finding the Web() function, I am now able to get the Excel file to open on Sharepoint. I am however unable to extract the data from Excel to JMP for subsequent analysis

In my original script I simply used Open() to load the Excel content to the data table. Now that I’m staring at the Excel page in Sharepoint, I’m stuck. At this point I can’t see the wood for the trees. Any input on this is more than appreciated. If you have any JSL input, please ensure your its legible to a 10 7 year old. Here's the bit I'm stuck at:

Names Default To Here( 1 );
Web( "https://company.sharepoint.com/folders/file.xlsx);
dt = Open(
“Am I missing something here? Sharepoint is showing the Excel file, but it's not really OPEN as such”
               Worksheets( "Whole Milk" ),
               Use for all sheets( 1 ),
               Concatenate Worksheets( 0 ),
               Create Concatenation Column( 0 ),
               Worksheet Settings(
                              1,
                              Has Column Headers( 1 ),
                              Number of Rows in Headers( 1 ),
                              Headers Start on Row( 3 ),
                              Data Starts on Row( 4 ),
                              Data Starts on Column( 2 ),
                              Data Ends on Row( 0 ),
                              Data Ends on Column( 24 ),
                              Replicated Spanned Rows( 1 ),
                              Replicated Spanned Headers( 0 ),
                              Suppress Hidden Rows( 1 ),
                              Suppress Hidden Columns( 1 ),
                              Suppress Empty Columns( 1 ),
                              Treat as Hierarchy( 0 ),
                              Multiple Series Stack( 0 ),
                              Import Cell Colors( 0 ),
                              Limit Column Detect( 0 ),
                              Column Separator String( "-" )
               )
);

[#2 · Accepted solution]

Check out this post, I *think* you are going to run into the same problem.  At that time I don't think there was a perfect or fully automated solution but maybe @hardner found something since?

Open microsoft office 365 cloud-based excel from JMP/JSL? 

In short, open a web browser window first to authenticate and then open the file.

To get the URL:

• Within office 365/sharepoint/teams/etc, get a link to the file (which normally opens the file in a web browser)
• Remove everything after the ? in the link and replace it with download=1
• Save a copy of the file locally and open it in JMP using your preferred settings, then open and copy the 'source' script saved to that data table.
• Replace the file location with the url you just modified.

Your script would look something like this:

//Authenticate - this caches credentials for a while but not forever, would replace this with your sharepoint URL.
Web( "https://teams.microsoft.com", JMP window );

//once authenticated then open the file
dt = Open("https://company.sharepoint.com/:x:/r/sites/Site/Shared%20Documents/Folder/Subfolder/File%20name.xlsx?download=1",
	Worksheets( "Sheet 1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

[#3 · Accepted solution]

This is indeed the solution I'm using...

The open() itself will use some cached credentials but I'm not sure how long those last.

For me the added initial web() is an OK solution because I use it to open my own internal "company.sharepoint" location and our internal SSO works on that! I put a big (15 seconds) wait after the web() and if it doesn't open immediately with cached credentials it will still work by going through our internal sign on with my credentials without any interaction.  In this way I have various scripts running as scheduled jobs and they seem to be able to reliably open our internal excel sheets from sharepoint.

[#4]

@ih It's alive! It pulled in the data from Sharepoint to a data table. Many thanks for directing me to this solution. I had searched the Community posts for Sharepoint related queries, but not for Office 365. @hardner  Thanks for your suggestion to add the time delay to allow for the credential handshake. Makes all the difference & made my day!

I'll be back when I've (probable) issues saving to Sharepoint!