sqanything (AlaSQL Chrome Extension)

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 selecting "View History".

When you select 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 selecting "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 select "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 order in which they appear on the web page (for example, the 5th table on the page would be "851nb3-5"). When you open a web page under your Schema, you can see all the tables on that page. When you open 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". You can copy and paste the example query 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 select that block), you can select 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". Here, 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 select "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 select "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 (for example "test" so each Sheet will be called "test_" + the table alias).

When you have tables with misaligned headers and columns, try using {headers:false}. For example, in this url: https://en.wikipedia.org/wiki/Victory_Bowl

There are fewer headers than columns. Setting the headers = false, results in gathering the correct data:

If you wanted to compare the number of acquisitions Google and Yahoo have made over time, you could pull up the Wikipedia pages on Google’s (or technically Alphabet’s) acquisitions and Yahoo’s acquisitions. Then, you could run the query below after replacing "#yahoo-table-alias" and "#google-table-alias" with their respective table aliases under "Schema" in the extension.

You have to do some string manipulation to extract the year from the acquisition date columns. If you run that query after replacing the table aliases, and then send the data to Google Sheets to chart, you should get a chart that looks something like below. From the chart, you can see that Yahoo’s acquisitions have slowed down in the last few years; the company was acquired themselves by Verizon in 2017. Alphabet completed a total of 34 acquisitions in 2014, including DeepMind and Nest.

You can query any table in Notion that is in an open tab and is publicly accessible. For example, say you had a table in Notion where you kept track of your favorite movies, like the one below.

You could simply query this table by opening that Notion page in one of your tabs—it should show up under your Schema. If the table does not appear, try selecting "Refresh Schema". To query the table, simply replace its table alias where it says "#replace-notion-table-alias" in the following query:

Say you wanted to join the table above with the list of Best Picture Nominated movies to see which of your favorite movies have been nominated for Best Picture. If you opened up the Wikipedia page for Best Picture Nominees, you could join them with your favorite movie table by writing the following query and replacing "#best-picture-noms-table-" with the table aliases for the Best Picture Wikipedia page that shows up under "Schema":

The tables in that page are separated by decade so you must use "UNION ALL" to combine all of them into a single table. If you run that query with the example Favorite Movies notion page above, you should see the result that Citizen Kane, Moonlight, Casablanca, Apocalypse Now, and Lost in Translation were both in the favorite movies table and nominated for Best Picture.

If you wanted to quickly find out which cities had a World Series and a Super Bowl victory in the same year, you could open up the Wikipedia list of Super Bowl winners, the list of World Series winners, and the list of major North American cities (to help extract the city from the team names). Then simply replace the "#sb-table-alias","#ws-table-alias",and "#city-table-alias" in the query below with the corresponding table aliases that show up under "Schema" after you open those web pages. To clean up this query, it helps to replace "New York" with "New York City" and "New England" with "Boston" for consistency between the tables.

You should see the result that three different cities had both a World Series victory and a Super Bowl victory in the same year: New York (Mets and Jets) in 1969, Boston (Red Sox and Patriots) in 2004, and Pittsburgh (Pirates and Steelers) in 1979.

Say you had a desire to find out which movies Joaquin Phoenix and Amy Adams both acted in. You could open up the Wikipedia pages for Joaquin Phoenix filmography and the list of Amy Adams performances. Then replace "#jp-table-alias" and "#aa-table-alias" in the query below with the corresponding table aliases that show up under "Schema" after you open those Wikipedia pages.

Since those tables have offset columns for Year and Title, one solution is to check the "Year" column for digits, and if it doesn’t have any then use "Year" instead of "Title" as the title. The reason this happens is that the "Year" column can span multiple rows, so the first row for each year moves all the columns over one. If you run that query, you should get the result that they were both in the movies Her and The Master.

You can also easily manipulate or aggregate data in Wikipedia tables and then send the data to Google Sheets to chart. As an example, you could aggregate the number of category 4 hurricanes per year from the Category 4 Hurricane Pacific Wikipedia page and send it to Google Sheets to analyze to see if the hurricanes are becoming more or less common. In the query below, replace "#cat-4-hurricane-table" with the corresponding table alias under "Schema" after you open that page.

You can send the results to Sheets by entering the Spreadsheet ID and Sheet name under "Destination". After you send that query to Sheets and chart the data, you should see a chart that looks like this: