How to open an Excel workbook from Sharepoint folder, with credentials

[rich_russell]

I've got a Powershell script that successfully opens and reads an Excel workbook which is stored in a Sharepoint folder, BUT when I set it up to run under Task Scheduler it fails.   Note that when I run this script from a Powershell 7 command prompt, it works properly.   Note: when running from command prompt the script DID prompt me for authentication the first time I ran it, but not since.  

My code is, essentially:

$username = "email address removed for privacy reasons"

$password = "what3ver" | ConvertTo-SecureString -asPlainText -Force

$credential = New-Object System.Management.Automation.PSCredential($username, $password)
[void](Connect-PowerBIServiceAccount -Credential $credential);

$Excel = New-Object -ComObject Excel.Application

$TargetFile = "https://corpad.sharepoint.com/sites/Shared_Docs/Target_Workbook.xlsx"

$Workbook = $Excel.Workbooks.Open($TargetFile )

When launched from the Task Scheduler, the script hangs indefinitely on the .Open() call in the last line.  I assume that to mean it lacks credentials, and can't prompt for them??

Can I specify credentials on the .Open() call?  (If so, the Office documentation doesn't indicate so.)

Note that the email address removed for privacy reasons/what3ver credentials that I use in the Connect-PowerBIServiceAccount() call are valid for the workbook, as well.

Any help would be appreciated.

Notes:

- Running on a Windows Server (2016 standard)

- Office Professional Plus 2016

- Powershell version 7.3.7

Thank you in advance.

[#2]

Is the account not blocked/restricted by a Conditional Access policy? MFA?

[#3]

Not to my knowledge. And if it were, wouldn't that prohibit the script from successfully accessing the workbook when I run it from the PS command prompt?

Thanks for any insight--conditional access policies and MFA are not something I'm familiar with.

[#4]

"Note: when running from command prompt the script DID prompt me for authentication the first time I ran it, but not since." The Scheduled Task doesn't run as your account I presume?

[#5]

To see what's going on, you could use "Start-Transcript c:\temp\log.txt" as the first line in your script and Stop-Transcript as the last line. You will see all the script output in the log, and perhaps it will show you more information when running it as a Scheduled Task.

[#6]

I did that, actually (ironically, I had *literally* named the log file "c:\temp\log.txt" but left it out of my sample code in an effort to stick to the key point. That log shows that the script runs normally right up to the line $Workbook = $Excel.Workbooks.Open() ... and then hangs indefinitely.

Thank you for your suggestions and input!

[#7]

Good question--thanks. It runs as the server's admin account, and the deal is:
That's an admin account on that domain, while the credentials used in the script to connect to the Power BI service are for a service account in a different domain. (Sorry if that's unclear--it's definitely ugly, but out of my control.) Bottom line: I can't run the script under the email/what3ver credentials (which have authority to read to the Excel workbook) and I can't use the credentials under which the script runs to access the Excel workbook.

But your question highlights the ridiculousness of that position! So maybe I can find some way to resolve that, and this problem would go away.

[#8]

You can connect to PowerBI using an App Registration (https://learn.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/connect-powerbiserv...). But that's just getting the data, saving it to SharePoint site is something else, perhaps a ServicePrincipal would be better but don't have any experience in using that for this purpose (yet ;) )

[#9]

Any update?