11

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?

mklement0
  • 382,024
  • 64
  • 607
  • 775
Sarah Northway
  • 1,029
  • 1
  • 14
  • 24

1 Answers1

17

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:

mklement0
  • 382,024
  • 64
  • 607
  • 775