cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
hardner
hardner
Level V

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

Created: Jan 22, 2020 03:04 PM  |  Last Modified: Jun 9, 2023 4:26 PM (10592 views)

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?

0 Kudos
11 REPLIES 11
matteo_patelmo
matteo_patelmo
Level IV

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

Jul 28, 2020 10:51 AM (2488 views)  |  Posted in reply to message from hardner 07-28-2020

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

0 Kudos
hardner
hardner
Level V

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

Jan 27, 2020 05:37 PM (3295 views)  |  Posted in reply to message from hardner 01-22-2020

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!