topic Re: Open microsoft office 365 cloud-based excel from JMP/JSL? in Discussions

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

hardner — Fri, 09 Jun 2023 23:26:03 GMT

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?

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

ih — Thu, 23 Jan 2020 14:58:21 GMT

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!

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

hardner — Fri, 24 Jan 2020 14:54:00 GMT

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?

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

txnelson — Fri, 24 Jan 2020 15:31:27 GMT

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.

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

ih — Fri, 26 Feb 2021 18:12:26 GMT

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.

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

hardner — Mon, 27 Jan 2020 15:27:33 GMT

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.

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

hardner — Mon, 27 Jan 2020 20:53:42 GMT

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!

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

hardner — Mon, 27 Jan 2020 22:37:37 GMT

OK, my big problem was pointed out by a colleague... my link to the file didn't look like the nice clean one ih showed (I could see that but he had an example from our system that did). I did get links to the file different ways (including just the URL showing in the browser and from a share button with option "copy link") and they all worked when pasted into Chrome or Microsoft Edge browsers but instead of ending like...

Folder/Subfolder/File%20name.xlsx

they all had extra stuff like this...

%7B21C93788-01B3-4508-8EC9-5451FBB8AEB6%7D

so even when I stripped it down to the "?" after the filename I didn't have the apparently right type of URL for this to work.

Then it may be necessary to do the second part ih suggested about logging in via Web() to input credentials in JMPs particular browser but I'm not sure because I had already done that earlier (so not sure if it would have worked with this URL based on credentials saved by other browsers or not). Now that I have a nicer version of the URL it just opens from JSL nicely. Does not need ?download=1 and so far is not only remembering the credentials for the session, I've rebooted 3 times and it can just do this from the get go each time without requesting credentials...

Open("https://company.sharepoint.com/:x:/r/sites/Site/Shared%20Documents/Folder/Subfolder/File%20name.xlsx");

My only issue now that since I'm not explicitly sending credentials I'm not sure what might make it forget them but so far it seems good at remembering them.

Thanks!

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

matteo_patelmo — Tue, 28 Jul 2020 12:02:56 GMT

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

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

hardner — Tue, 28 Jul 2020 14:28:10 GMT

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

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

matteo_patelmo — Tue, 28 Jul 2020 14:51:02 GMT

Thanks, but unfortunately it seems not to be working with the O365 URL (both as-is and with download=1 after ?).

I get the message: could not resolve host mycompany.sharepoint.com

It is strange because the same kind of URL works just fine with .jmp data table and open().

Matteo

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

johnmoore — Fri, 26 Feb 2021 17:13:07 GMT

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;