Let’s Use Spreadsheet Query Function (1) Outline and Purpose

クエリイメージ Spreadsheet

Target Readers

  • who are interested in SQL, but who are troublesome to prepare the environment and data from scratch
  • who are extracting data with vlookup, but who feel the limit
  • who want to analyze data
  • Even non-programmers who want to use queries
  • who are asking queries delicious? (meaning that the query is delicious)

Query function: Outline and purpose of serialization

Personally, I love SQL and I use MySQL a lot, but MySQL causes various errors when setting up the environment (or rather, I returned the environment from the last 8 system to 5.7 system). However, it took a long time to rebuild.).

Of course, those who are used to it will be able to do it without causing an error. .. And even if an error occurs, you can change the access right with the linux command, change the file owner, touch the new file. .. .. It’s a bit difficult for non-professionals to do.

So, who would like to use a query, but who are not as good as creating an SQL environment, the spreadsheet query function is suitable for a little practice and work environment. So, I will introduce it.

Please note that the spreadsheet itself plays the role of database creation and table creation that you do with actual SQL, so please forgive me that you can not study. Also, I can’t study access rights or user registration (mysql, mysql db, etc.).

However, it is a practice to write a normal query. Of course, it’s not suitable for writing complex query statements, but select/group/order_by/where/like/start with… Google is the place where various query grammars are supported. ..

Let’s take a quick look at what you can do with the Query function

Let’s say you have the following data (this is taken from google’s document)

spreadsheet datas

You can search and aggregate this under various conditions.

results of query functions

Various data types can be extracted and aggregated on one sheet. If you want to examine the data of another spreadsheet as well, you can handle it by pulling each sheet data with the importrange function.

There are many things I can do, I will try the query function as a part of studying queries by dividing it into several times.

Query function (query function) series planned (planned)

  1. Outline and purpose ← this time
  2. Structure of query function
  3. Basic SELECT clause (get all, get only specific columns)
  4. Extraction basics Try using WHERE clause (numerical value match, size, date match, size etc.)
  5. Try using the GROUP BY clause
  6. Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Sorting basics ORDER BY clause
  8. Required if you do SQL Try a subquery
  9. There are line breaks in the data. contains/starts with helps when you can’t handle like


SQL can’t really study how to build inserts or tables themselves, or data linkage (one like OneToOneField/ForeignKey in django), but it’s non-professional. Assuming that the DB is not something to create, but to use it, I think studying SQL using the Query function is very effective for the first step.

I do not know if I can do my best in the series, but occasionally I will do my best little by little for articles that may be useful for people other than python

Series: Let’s Use Spreadsheet Query Function

While not studying SQL, I will become accustomed to using the spreadsheet query function. We will study from the simple usage of select statement, including the difference from SQL.

  1. Let’s Use Spreadsheet Query Function (1) Outline and Purpose
  2. Let’s Use Spreadsheet Query Function (2) Structure of query function
  3. Let’s Use Spreadsheet Query Function (3) SELECT Clause
  4. Let’s Use Spreadsheet Query Function (4) WHERE Clause
  5. Let’s Use Spreadsheet Query Function (5) Group by
  6. Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策