I am trying to download a Google sheet via a batch file. This works:
powershell -Command "Invoke-WebRequest https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?exportFormat=tsv -OutFile output.tsv"
When I specify which sheet/tab I want by adding &gid=1234, this breaks:
powershell -Command "Invoke-WebRequest https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?exportFormat=tsv&gid=1234 -OutFile output.tsv"
The error is:
The ampersand (&) character is not allowed. The & operator is reserved for future use; wrap an ampersand in double quotation marks ("&") to pass it as part of a string.
How do I wrap the ampersand in quotes without breaking the outer quotes for the Command parameter?
The URL embedded inside the "..." string passed to powershell -Command must be quoted too, because an unquoted & has special meaning to PowerShell too (though in Windows PowerShell it is currently only reserved for future use; in PowerShell Core it can be used post-positionally to run a command as a background job).
The simplest option is to use embedded '...' quoting, as suggested by Olaf, because ' chars. don't need escaping inside "...". '...' strings in PowerShell are literal strings, which is fine in this case, given that the URL contains no variable references.
powershell -Command "Invoke-WebRequest 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?exportFormat=tsv&gid=1234' -OutFile output.tsv"
If embedded "..." quoting is needed for string interpolation, use \" (sic) to escape the embedded (") chars. (note that inside PowerShell, you'd need to use `" or "" instead):
powershell -Command Invoke-WebRequest \"https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?exportFormat=tsv&gid=1234\" -OutFile output.tsv
Note:
To avoid problems with cmd.exe's up-front parsing, the outer "..." were omitted above, which still works, because PowerShell simply space-joins multiple arguments before interpreting the result as PowerShell source code.
In more complex cases you may prefer the use of outer "...", in which case use of \" for embedded " can situationally break (as it would in this case, due to &), so more elaborate workarounds are needed: "^"" (sic) with powershell.exe, and "" with pwsh.exe, the PowerShell (Core) CLI: