BookmarkSubscribeRSS Feed
danielrbiber

New Contributor

Joined:

Dec 6, 2018

JSL obtaining access to excel file in sharepoint

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: JSL obtaining access to excel file in sharepoint

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

error.PNG

 

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

Jim
6 REPLIES
Highlighted
txnelson

Super User

Joined:

Jun 22, 2012

Re: JSL obtaining access to excel file in sharepoint

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

graphical1.PNG

 

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 );
Jim
danielrbiber

New Contributor

Joined:

Dec 6, 2018

Re: JSL obtaining access to excel file in sharepoint

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:

JSL issue.PNGhttps---xyzcomp.com-sites-SbD-Product-docs-Development%20Database-Feasibility

 

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

txnelson

Super User

Joined:

Jun 22, 2012

Re: JSL obtaining access to excel file in sharepoint

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

Jim
danielrbiber

New Contributor

Joined:

Dec 6, 2018

Re: JSL obtaining access to excel file in sharepoint

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.
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: JSL obtaining access to excel file in sharepoint

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

error.PNG

 

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

Jim
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

Re: JSL obtaining access to excel file in sharepoint

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