How to call via Powershell a servicenow URL that exports a servicenow table to an excel file ?

[jmclej]

Hello,

So far I was using the following working script to automatically export a servicenow table :

$pathToChrome = ‘C:\Program Files\Google\Chrome\Application\chrome.exe’

$tempFolder = ‘–user-data-dir=’ + $env:Temp + ‘\temp_chrome_sc_request’

$startmode = ‘–default’$startPage = ‘https://instancelambda.service-now.com/nav_to.do?uri=%2Fsc_request_list.do%3FXLSX%26sysparm_<sysparmTypeOrField=value>%26sysparm_fields=reassignment_count,location,number,opened_at,short_description,requested_for,priority,request_state,u_business_service,business_service,calendar_duration,sla_due,made_sla,u_other_service,assignment_group,assigned_to,sys_updated_on,sys_updated_by,contact_type,closed_by,sys_created_on,sys_created_by’

$downloadpath = ‘C:\Users' + $env:USERNAME +’\Downloads'

$app=Start-Process -FilePath $pathToChrome -ArgumentList $tempFolder, $startmode, $startPage -PassThru -WindowStyle Minimized

But a GPO, that I can't modify, has just been applied which sets to YES the parameter “Ask where to save each file before downloading” in chrome (and Edge too). My script now triggers then a popup to show up, which needs manual action, and therefore I cannot use this script anymore in a Task scheduler.

After some research, I found different ways with Powershell to download a file, but I cannot make it work with this specific servicenow URL. Indeed, I don't have access to servicenow API so I need to go with the above export URL.

When I try to use Start-BitsTransfer, it creates a 0 kb-size file and when I try to use Invoke-WebRequest, I get a 33kb-size file when the file I should get is like 12Mb.
If you pay attention to my URL, it is not directly a link to a file, it is a link in servicenow that, when called, generates a file and downloads it. Servicenow uses SSO authentication method.
Do you have an idea of which additional paremeter I should use ?
I have tried adding -UseDefaultCredentials to Invoke-WebRequest but I get the same result (33kb-size file).
I have tried adding $Cred = Get-Credential and -Credential $Cred to Start-BitsTransfer but I also get the same result (0kb-size file). Get-Credential also generates a popup to enter my credential, which I don't want to do, I want to the script to use the actual credential of the user running the script.
Thanks for your help

[DrewW]

Its just simplified and it tends to work better for what you are doing.  This is default functionality that the system supports.  Also the PowerShell example I posted is just to help you along and you should be able to use a different URL.  You should be able to use PowerShell and the URL I suggested with &EXCEL at the end as a standard web call and then just stream the file to disk.

[ricker]

jmclej,

This feels like one of the instances where you can do something but should you?  Why? What is the requirement?  I bet if you worked with your ServiceNow team they'll have a better solution.  Is there a reason you can't have a report(s) emailed to you with the excel sheet(s)?

[jmclej]

I need to have it in a file server and not an email. My powershell, once it has downloaded the excel file, sends the file automatically somewhere on the network where it will be read by a PowerBI report.

[DrewW]

Have you talked with your ServiceNow admin's about setting up a PowerBI integration?  I know it supports it in the Cloud.

[jmclej]

Hello, they gave me access to an API, which I can then use on Power BI. Can you point me to a more direct ServiceNow Power BI connector as you were talking about it please ?