After that, I received explanations about the syntax of the Google Visualisation API and I was made to solve problems Inoue presented. To be honest, the full day of lectures was tough, but I was able to understand most of it, including things that weren’t likely to be used like pivot, offset, and format.
“So how does the QUERY function look like?”
As time was almost up, I asked a good question.
“Oh, I’m really sorry! I forgot to teach about the actual syntax of the QUERY function itself!!”
She certainly had forgot. Inoue quickly began to write down the syntax on the whiteboard.
=QUERY(Data, Query, [Headers])
“To give an actual example, it would be sometime like this.”
=QUERY(A1:C4, “select B, count(A), avg(C) group by B”, 1)
“Data is the cell range you want to refer to in the query. There are no problems in this example, but you should be careful about specifying whole columns in a format like A:C. It’s hard to notice, but there are still blank spaces. All rows will still be recognised in the database.”
“That shouldn’t be much of a problem, is there anything else I should be careful about?”
Name Gender Age
———- ———— ——-
Takahashi Male 27
Saito Male 43
Inoue Female 17
“Suppose this data is in A1:C4 of the sheet and there are many blank lines after the 5th row. If you wrote something like this.”
select * order by Name
“You would expect the names Inoue, Saito, Takahashi in that order from top to bottom, but in reality, it would look like this.”
Name Gender Age
———- ———— ——-
(Long Blank Space)
Inoue Female 17
Takahashi Male 27
Saito Male 43
“You can still specify data in a format like A:C, but in that case, the key is to know where to exclude blank lines.”
select * where Name <> ” order by Name
“I see, I understand now. By the way, in the example you gave, there are letters for the columns like A and B not ‘Name’ and ‘Gender’, can either one be used?”
“My bad, I wrote the names down for explanation purposes, you actually need to use the column name.”
“I see, so you can’t even use named ranges.”
“Since the query is treated as a string, named ranges are not possible. There’s nothing that can be done about that.”
“It’s a little awkward. It doesn’t work when columns shift due to addition or deletion of columns either, I would personally find it hard to use.”
“Oh! Actually, it’s fine to add and remove columns.”
“Do the columns in the formula update automatically like when specifying a range?”
“No, it’s a bit strange, but even if the columns are modified, the results that were already processed are maintained. The query itself doesn’t change. If you write the same query into a different cell, it won’t work anymore.”
That’s strange. Does it really work like that? However, even if columns can be modified, it still feels a little uncomfortable. Inoue began explaining the remaining ‘Headers’ parameter.
“I don’t think there’s much to day about the last [Headers] parameter. It just defines how many rows from the top are headers. Normally, it’s just one line and if it’s not specified or written as -1, it will default to 1. From my experience, most headers are just one line. Often it is not directly specified. That’s all for the general explanation. Do you understand?”
“Yes. It’s all good. But, to be honest, I feel it’s a rather strange function. You can’t use named ranges, it’s not very readable, and you have to memorise the queries. Wouldn’t it be better to use something like SUMIFS to fill each cell or use a pivot table instead?”
I thought Inoue would agree, but her response was much harsher than I expected.
“Takahashi-san, ease of use differs from person to person. You don’t think all of our users are like you, right? You’ve always been an Excel user and you’ve earned your qualifications and studied hard. You then also used it in your work at your company. Are assuming that everyone else is like that?”
“Eh?”
“Of course, there are many people like that, but it’s not just those people. With us, anyone with a Google account can use a spreadsheet, so a lot of people use it in a variety of ways. Some people use it to record the movies they watch. It’s not just people who came over from Excel either, there are many people who don’t have Excel on their computers.”
“That might be true, but…”
“To be more specific, can imagine someone who has never touched SUMIFS or pivot tables, but is very good at using the QUERY function? Saito-san said that our team will be in charge of engineering spreadsheets from now on and there will be many people like that. Because the QUERY function is easy to use and, as long as you know how to write the query, there are many things you can do with that single function. Unlike Takahashi-san, I already know SQL. Even if I don’t know named ranges, I know SQL.”
There was nothing I could say. That was right. I was completely refuted.
Inoue said a few last words as she left the training room.
“Please understand your users better. Otherwise, you will die, you know?”