As for "automating the entire process", the query will mostly be different for each spreadsheet so that can't be automated. And the filling up will be a one-time process for each spreadsheet, so automating it does not benefit much.

Using Google Spreadsheet API has crossed my mind to avoid the round-trip of copy-paste to TSV. Don't know how much of a hassle it would be though, anybody tried before? It would really be nice if I can just run a script on the terminal, enter the address/URL of the Google Sheet, the query, and voila, the columns will be filled by the script. No copying and pasting required.