topic JSL obtaining access to excel file in sharepoint in Discussions

JSL obtaining access to excel file in sharepoint

danielrbiber — Thu, 06 Dec 2018 19:34:54 GMT

I am looking to run a script routinely from an excel file we keep in sharepoint.

I have not been able to find much but due to the tight control of our IT department I cannot write anything other than JSL.

I am able to run the attached script after I have accessed this file using the graphical "open file" window in JMP. I think this works because sharepoint now knows that JMP is authorized to access the excel file. I had the work around idea to have JSL call the graphical open window, wait for the user to input the file they want, and execute. The hope is that the magical "Windows Security" window opens and they can enter their credentials.

Does anyone has experience with this? Is there JSL to open that "open file" GUI?

Re: JSL obtaining access to excel file in sharepoint

txnelson — Thu, 06 Dec 2018 19:56:13 GMT

Is this the "graphical 'open file'" window you are referring to?

If so, then the following JSL code is all you need to open a file

Names Default To Here( 1 );
file = Pick File(
	"Select JMP File",
	"$DOCUMENTS",
	{"Excel Files|xls;", "All Files|*"},
	1,
	0
);

dt = open( file );

Re: JSL obtaining access to excel file in sharepoint

danielrbiber — Fri, 07 Dec 2018 16:43:47 GMT

Thanks for providing that. "Pick File" is exactly what I am looking for. I do have a follow-up question about how this function works though.

I would really like to provide the user with the path and excel file in the open window. It seems though that JSL is not reading the punctuation correctly for the path.

For the attached script the path displayed in the window is:

https---xyzcomp.com-sites-SbD-Product-docs-Development%20Database-Feasibility

Why has JSL replaced all ":" and "/" with "-"? Is there a way to prevent this?

Re: JSL obtaining access to excel file in sharepoint

txnelson — Fri, 07 Dec 2018 17:26:49 GMT

Pick file only works when looking into an operating system file structure. The Open Window Object does not understand URLs.

Re: JSL obtaining access to excel file in sharepoint

danielrbiber — Fri, 07 Dec 2018 17:36:31 GMT

It seems fine when I actually paste in the path and file. For instance, if I past the following into the "file name" field it does work.
https://xyzcomp.com/sites/SbD/Product/docs/Development%20Database-Feasibility.xlsx

Are there JSL functions to pass the ":" and "/" without modifying them?
If "pick file" truly wont work with URLs, could we add this to the wish list? Should be simple to just prevent JSL from changing those specific characters.

Re: JSL obtaining access to excel file in sharepoint

txnelson — Fri, 07 Dec 2018 17:45:34 GMT

When I paste the string into the File Name area in the Open Window, you are correct, the "/" are not changed. However, when I attempt to open it, I get the following error

Yes, this item should be added to the JMP Wish List.

However, I assume, that JMP is just calling the window from the operating system, not a window that JMP has built.....so changing how it works may not be as easy and you suggest

Re: JSL obtaining access to excel file in sharepoint

stan_koprowski — Mon, 10 Dec 2018 22:50:36 GMT

Hi @danielrbiber,

I have not tried this yety but if you have JMP 14 you could probably use the JSL HTTP request to access the Excel file from sharepoint.

If you know the URL--

url: http://site url/_api/web/GetFileByServerRelativeUrl('/Folder Name/file name')/$value
method: GET
headers:
Authorization: "Bearer " + accessToken

Sharepoint http requests

See the following Discovery Summit presentation for additional details on how the new JSL only REST functions work

cheers,

Stan