https://www.youtube.com/embed/U0-D6TCtPVU
sqanything is available here in the Chrome Web Store.
Data tables are all over the internet, especially Wikipedia. But HTML tables can be difficult to download or copy-and-paste cleanly. sqanything lets you query the HTML tables in all your open Chrome tabs with SQL. You can also query tables in Notion, CSV or Excel files, and tables in Google Sheets. You can send the results of your query to Google Sheets or just send all the tables on a web page to Google Sheets.
Each individual query, including the web pages that it queries from and the destination it sends to (if applicable), is called a "Block". When you launch the extension, you can see your past Blocks by clicking "View History". When you click on one of your blocks, it will automatically launch the web pages used in that query (including Wikipedia pages, CSV's, Notion Pages, public Google Spreadsheets, etc.), so you can run the same query and get the same results. You can create a new Block by clicking "New SQL Block" after you launch the extension.
When you start or launch a Block, you will see a tab for "Schema" underneath the query text. This shows you the active HTML tables currently in your Chrome tabs. If you don't see any tables, you can click "Refresh Schema" at the bottom.
Each web page in your Chrome tabs is assigned an "alias" such as "851nb3". All the tables on that web page are given the same alias, plus the number that they appear on the web page (e.g. the 5th table on the page would be "851nb3-5"). When you click on a web page under your Schema, you can see all the tables on that page. When you click on a specific table, you can see the number of columns, an example query, and the first 5 rows of that data table. The example query will look something like this "SELECT * FROM HTML("#t851nb3-5", {headers: true}) LIMIT 5", and you can copy and paste that into your query text in order to start querying from that table.
If you wish to remove a web page or data table from your Schema for a certain block (so that web page will no longer automatically launch when you click on that block), you can click on the trash can icon to the right of the web page or table.
Under the query text of a Block there is also a tab for "Destination". This is where you can enter a Spreadsheet ID and Sheet name (and optionally cell location) to send your query result to. The Spreadsheet ID can be found in the Google Sheet URL. When you click "Run" after entering your destination, your query result will be sent to that Sheet.
If you want to simply send all the tables on a web page (or web pages) to Google Sheets, you can click "Extract HTML tables to Sheets" after launching the extension. Here you can enter the same information as above, and also a prepend to the Sheet name (e.g. "test" so each Sheet will be called "test_" + that table alias).
This app uses AlaSQL under the hood. Their docs are great! Check them out for syntax questions.
SELECT
[ISO 3166 Country Code] as code,
Country as country,
Latitude as lat,
Longitude as long
FROM CSV("<https://cdn.rawgit.com/albertyw/avenews/master/old/data/average-latitude-longitude-countries.csv>",{headers:true})
WHERE Latitude BETWEEN 0 AND 25
ORDER BY Longitude DESC
SELECT * FROM CSV('<https://cdn.rawgit.com/albertyw/avenews/master/old/data/average-latitude-longitude-countries.csv>',{headers:true})
WHERE Latitude BETWEEN 0 AND 25
SELECT * FROM CSV('<https://drive.google.com/u/0/uc?id=1F-B8Z3YUizsOaBcdSPk-0lWVrC0mxYYs&export=download>',{headers:true})
SELECT * FROM CSV('<https://docs.google.com/spreadsheets/d/1zhfO8ANC-odz7IDMw065yHbQVPBjtrlo2ukysNlqLlo/gviz/tq?tqx=out:csv&sheet=Sheet1>',{headers:true})
SELECT sum(Wins) FROM CSV('<https://docs.google.com/spreadsheets/d/1zhfO8ANC-odz7IDMw065yHbQVPBjtrlo2ukysNlqLlo/gviz/tq?tqx=out:csv&sheet=Sheet1>',{headers:true})
SELECT * FROM CSV('<https://docs.google.com/spreadsheets/d/1zhfO8ANC-odz7IDMw065yHbQVPBjtrlo2ukysNlqLlo/gviz/tq?tqx=out:csv&gid=975578095>',{headers:true})