Search code examples
powershellbatch-filegoogle-sheetsquoting

How do I include ampersands in a powershell command in a batch file?

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?

Solution

  • 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: