43

The following Google Doc gets exported to CSV and is then downloaded automatically using a standard Web Browser: https://docs.google.com/spreadsheets/d/1wDD_Xm8IQYuYNefv9cOJ_7afTLImHgYA05pfN3qY63E/export?format=csv

I'm trying to download this file using Powershell without success. I tried using Invoke-Webrequest, Start-BitsTransfer and using a webrequest object but no luck there.

EDITED: changed url.

JustAGuy
  • 3,773
  • 8
  • 33
  • 49
  • I stranded here when looking for a way to download from https://docs.google.com/file/d/0B78A_rsP6RDSVjBTa1ZUSXBGYzA/edit - the accepted answer helped me to find https://drive.google.com/uc?id=0B78A_rsP6RDSVjBTa1ZUSXBGYzA&export=download - I know that this is not for this excact question but hope it could be an equally huge help to somebody else landing here. – Cadoiz Nov 13 '20 at 05:21
  • It's good practice when asking for help with something that's not working for you to say what you're looking for (e.g. a file.csv at c:\) and what you're getting instead (e.g. "Error 404") – duct_tape_coder Mar 03 '21 at 21:56

2 Answers2

82

Invoke-WebRequest comes with a parameter to store its result in a file: -OutFile

Invoke-WebRequest URL -OutFile c:\file.ext

If you need authorization before you can send a request like this:

Invoke-WebRequest URL /* whatever is neccesary to login */ -SessionVariable MySession
Invoke-WebRequest URL -WebSession $MySession

To determine the layout of the form where the login happens, you can use Invoke-WebRequests return object. It'll collect information about forms and fields on the HTML (might be Windows only). Mileage of logging in may vary with things like Two-Factor-Auth active or not. Probably you can create some secret link to your file which does not need Auth or possibly google allows you to create a private access token of some sort, which can be send aus Authorization-Header alongside your request.

TGlatzer
  • 4,805
  • 1
  • 20
  • 40
  • Works fine here .. CSV with No headers and one row with three test-entries – TGlatzer Jul 07 '18 at 18:29
  • 2
    @JustAGuy: It works with (at least) Windows PowerShell v5.1 / PowerShell Core v6.1.0-preview.3. If it doesn't work for you, please add details about your environment to your question. – mklement0 Jul 07 '18 at 18:50
  • 1
    @JustAGuy This does work here also, you may not have rights to write to `C:\file.ext` try `Invoke-WebRequest 'https://docs.google.com/spreadsheets/d/1soyIpcac2hOb-5AdImm1LNCFs9jFiiyMhuqCayfpxSw/export?format=csv' -OutFile '$($Env:temp)\test.csv'` to store the file in the temp folder. –  Jul 07 '18 at 18:53
  • I have major 5 minor 1 installed. How can I tell if it's 5.1? On my station it ends up as core html and not the actual file. – JustAGuy Jul 07 '18 at 18:59
  • Have you quoted the URL? Perhaps there is a problem? $PSVersionTable tells you the Versions – TGlatzer Jul 07 '18 at 19:00
  • My apologies. I've put in the correct URL. This seems to work only with a new one for some reason. Try with this new one and tell me what you get. – JustAGuy Jul 07 '18 at 19:07
  • Well - that is pretty simple: You get an authorization page - the link is protected and OutFile does as asked and saves the file (which is an html page containing some login) – TGlatzer Jul 07 '18 at 19:20
  • You wouldnt happen to know how I can perform a login by a chance, right? Maybe grab a cookie from the browser? – JustAGuy Jul 07 '18 at 19:47
  • You have to analyze the Page - Invoke-WebRequest can help (it reads forms and form fields) - I think the Docs from MS have a example for that. – TGlatzer Jul 07 '18 at 19:54
1

TLDR answers for how to download a file with PowerShell*

Method 1, by default synchronous**

Invoke-WebRequest $url -OutFile $path_to_file

Method 2, by default synchronous**

(New-Object System.Net.WebClient).DownloadFile($url, $path_to_file)

Method 3, asynchronous and may not be immediate, uses BITS service

Import-Module BitsTransfer
Start-BitsTransfer -Source $url -Destination $path_to_file

Notes:

*: This answer is for those that google for "how to download a file with PowerShell".

**: Read the help pages if you want asynchronous downloading

ndemou
  • 3,315
  • 1
  • 25
  • 29
  • I would guess that you were downvoted for not 'solving' the problem the user had. They indicated they had already tried the three methods you've suggested. Sounds like they were getting a login.html instead of a file.csv. They didn't specify that issue in their original question which they should have. However, it also seems like you didn't carefully read their question details and the other answer conversation before answering an already answered question. I've upvoted as your answer is helpful in similar contexts (but not this user's specific one). – duct_tape_coder Mar 03 '21 at 22:03
  • Thanks for the thorough response duct_tape_coder! Your time is greatly appreciated. Indeed my answer was more towards the Google reader that searched for "how to download with powershell" than to the original author. There's a conflict here and I can understand the POV for downvoting. I also noticed that it is already answered but I love terse (TLDR) answers so I often add them to help others with similar taste. – ndemou Mar 04 '21 at 05:51