Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~

クエリイメージ Spreadsheet
この記事は約11分で読めます。
Advertisements

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 series planned (planned)

  1. Outline and purpose
  2. Syntax 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 ~← this time
  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

Query function:Let’s handle data

Remember the basics of the Query function? It was below.

syntax of QUERY function

QUERY(data, query, [header])

data    The range of cells to perform the query on(you can indicate other sheet data in same spreadsheet)
queryThe query to perform, written in Google Visualization API Query Language
this is main scope of this article( scope of studying)
headeroptional. The number of header rows at the top of data. If omitted or set to -1, the value is guessed based on the content of data.

If you have forgotten it, please review Part 2

Query function series no.2 syntax of query function

So far, We have mainly looked at the query part. Whether it’s a WHERE or a GroupBy, it was all part of the query. This time we will look at the data part.

Up until now, I was simply bringing data from the same sheet or another sheet in the same SS, but are there more cases of honestly separate sheets? Or maybe you want to use SS in combination with another SS.

If you are accustomed to SQL, it will be a join(inner join/outer join).

However, the Query function does not support the join function. There will be much less to do. .. .. I can’t use it. .. ..

However, I will also introduce a simple combining method supported by the query function.

Use importrange to bring data from other Spreadsheets

How to import the data is very simple, just put the importrange function in the data part.

When it is actually a function, it looks like this: So far, the red marker part was the data in the sheet or the same spreadsheet, but it will come from other sheets.

=query(importrange("sheet URL","data range"),"select *")

After execution, it will not work unless you grant the access right. Please note that this has the same specifications as import range.

iLet’s create a query for the data obtained by importrange

Now, assuming that you can get the data with importrange, the next is no different from using a simple importrange function unless the query is made.

Will the query change anything?

Let’s always try making a query like the one below.

=query(importrange("sheets URL","data range"),"select * where H is not null")

What? Is there an error?

Looking at the reason for the error, it is NO_COLUMN: H. It is the column specified by is not null in the query.

That’s right, in the case of importrange, the notation of the column specified in the query is different compared to the case where data is referenced in the same sheet.

So what does it do? Please try the following notation.

=query(importrange("sheets URL","data range"),"select * where Col8 is not null")

Yeah, the conclusion is that you will use Col.

The reason is that if you bring it in an import range, it’s simple and you don’t know which column it is in the data. Separately, if the data acquired in the import range is column C to column E, and the function itself is pasted in column A, it will be a problem no matter what data is said to be a column, right? I don’t know whether to talk based on the source or the source.

Precautions when using Col

The only caveat is that the data in the first column starts with Col1. Please note that it does not start from 0.

First column of data: Col1
Second column of data: Col2
Third column of data: Col3
The original data columns are counted up one by one below.

Let’s combine multiple data

Now, even if importrange can be used, the fact is that we are still searching from one piece of data.

After all, I would like to handle multiple data, so I will introduce a method for that. This also depends on how to handle the data area.

Coupling: Basically enclosed in { ; }

Basically, when two data are integrated and processed, it is possible to handle multiple data by enclosing them in {} (braces).

Combine multiple data from the same sheet

=query({A1:D5; A10:D14}, "select *")

It is possible to simply attach the data range with; (semicolon).

Combine multiple data from another sheet in the same spreadsheet as in the sheet

=query({A1:D5; query(data range)}, "select *")

When pulling data from another sheet, make it a query function and pull. Normal sheet designation should result in an error. Since this method is a query function, a query can also be set.

iSeparate sheet data using importrange + separate sheet data in the spreadsheet

=query({importrange("sheet URL","sheet_name!A1:H5");query(sheet_name!A1:H5)},"select *")

That’s the above combined skill.

Points to keep in mind when using queries

The combined data causes an error in the column (direct column name such as A, B, H) when writing a query, similar to the data obtained by importrange.

For the method to specify, it is necessary to use the Col described in the import range section. At first, I don’t think that I can understand the finished data, so I think it is easier to display all with “select *” without specifying and then specify the Col by counting the number of columns.

Summary

I’ve seen how to combine data and specify columns in Col when combined.

Joining data is a common method, and referencing or joining data from other sheets using importrange is used more frequently.

I think it’s a good part to get used to.

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 クエリ関数エラー対策

コメント