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

[#1]

JMP recently got a built-in ability to open Google sheets.  Before that in scripts I was calling an R package called Googlesheets to open them.  Now my company is moving to office 365 so we'll have cloud-based shared excel sheets and I'm not sure how I'll be able to read from those.  Is anyone opening these directly in JMP/JSL?

[#2 · Accepted solution]

Looks like it takes a few steps to do this:

• 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:

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( "-" )
	)
);

Run that and hopefully you see the excel data.  Good luck!

[#3]

Thanks! This is helpful but not working for me.  I get a 403 error and I'm presuming that I need some way to pass credentials for this to work (although at the moment I only have a test environment for the o365 implementation the real corporate version will be password protected as well.  I can't just go there with browser without being logged in.).  Both the JMP built-in googlesheets solution and the R one I was using used OAuth for that.  

Is this working for you in a similar situation where you have to log in on your browser to get at the sheets yet JMP can just open if it knows the URL?

[#4]

I suggest that you take this issue to the JMP Support team. 

support@jmp.com

This has to be something they need to be able to support, if they currently do not support it.  And by going to the support team, the issue will be tracked until implemented.

[#5]

Thanks.... technical support actually contacted me based on this post (maybe related to your reply suggesting it's an issue for them).  How cool is that?!  Did not produce an immediate solution but may do and I appreciate it.  

But also it's because I suspect there may be solutions that are workarounds outside of JMP that I'm still interested to see whether there are other users in similar situation who have found anything -  like maybe a python package solution for talking to o365...

I'm looking for a fully automatic solution that deals with credentials.

[#6 · Accepted solution]

Try opening the file, or a parent directory, in a JMP web window first.  That should bring up the log in screen where you enter your credentials. Once you log in once in a JMP session those credentials should be passed with the subsequent open call.  There might be a way to automate that but I'm not sure how.

Web( "https://teams.microsoft.com", JMP window );

Since you mentioned getting this data in R, you could always call the R code to get the data directly from JMP.

I second txnelson's suggestion to open a JMP ticket for this; it shouldn't be so hard.

[#7]

Thanks! As noted above I am now working with technical support. I'm not sure whether my issues are very specific to our implementation (currently only a trial sandbox as we haven't switched to o365 yet) or are general issues about use of credentials via JMP. But here's what happens when I try your idea.. I can open a parent page like you show directly in the JMP Web window after getting a credentials page. It's the o365 home page looking just like it does in other browsers. But not only does that not allow Open() to work, I can't actually open a child excel sheet there. The parent page lists the sheets but if I try to type in the URL of a sheet (in the JMP Web window where I'm already logged in) I get a "sorry, you don't have access" page, while it works in Chrome or Microsoft Edge. And if I just interactively try to open the sheet from the parent page in the JMP Web window by clicking on it- it opens in Chrome instead!

[#8]

I did a flavor of this, but I put a web browser box inside a new window.  That gives me a display box that I can close.  

credentials = New Window( "quick open", Web Browser Box( "path to site" ) );
credentials<<close window;

[#9]

Hello,

I have a similar issue. The solution proposed here works fine with a .jmp file, but it does not work with a .jmpaddin file stored in O365 which I would like to open directly from a JSL script.  I get a "cannot open" error.  If I open a local .jmpaddin file, everything works fine.

Any suggestion?

thanks
Matteo

[#10]

This is not specific to o365 but to opening .jmpaddin from a URL as vs. a local path. At least I see the difference between local paths and other non-office URLs.

Here is a way to refresh an addin that's in a URL-accessible location  that I got from colleagues...using NewHTTPRequest() to get file, put in local spot and then open()