구글시트 - 쿼리4 (Google Query + Importrange (사용법 및 오류해결))

 

구글시트 - 쿼리1 (Google Query Basic 1 :: Select, Group by, Order by)

구글시트 - 쿼리2 (Google Query Basic 2 :: Limit /Label /Format / Pivot )

구글시트 - 쿼리3 (Google Query : WHERE + contains/ starts with/ends with /matches (Regex))

구글시트 - 쿼리4 (Google Query + Importrange (사용법 및 오류해결))

 

---

Google Query + Importrange

구글시트에서 사용 할 수 있는 =Importrange 와 =Query를 합치면 정말 다양한 작업을 할 수 있습니다.

먼저 =Importrange함수에 대해 알아보자.

IMPORTRANGE 함수 (고객센터)

: 다른 스프레드시트에서 데이터 가져오기, 문자 그대로 다른 파일에 있는 데이터를 현재 파일에 불러올 수 있는 강력한 함수이다.

구문

=IMPORTRANGE(스프레드시트_URL, 범위_문자열)

=IMPORTRANGE("1Zm5GRAs7DJTwTPESw7f0AZM0x10-fz0MqNCKvt70d1g","RAW!A:G")

예제파일1 : 예제파일 2의 값을 IMPORTRANGE를 사용해서 불러올 수 있습니다. 예제파일 2가 수정되면 자동으로 반영이 돼서 업무에 활용하면 참 좋습니다.

 

 

당연히 데이터를 가져올 파일에 최소 권한은 있어야 합니다. (꼭 편집 권한이 아니라도 가능. 보기권한이여 가능함.) | 처음 데이터를 가져오면 액세스 허용이 필요

• 📌 Pro Tip : 스프레드시트_URL URL은 전체를 가져와도 되지만 아래 스크린샷 처럼 spreadsheet ID만 가져와도 됩니다.(Sheet ID 만 가져오는 게 깔끔해서 선호하는 편입니다.)

 

• 범위_문자열 : 시트 이름(선택사항) + 가져올 셀의 범위

// 범위_문자열은 ""로 감싸야 하면, 시트의 이름 ! + 범위 A~원하는 열까지 

"RAW!A:G" : 전체 열을 가져 오는 것이며 
"RAW!A1:G10" : A1부터 G10 까지 범위를 지정해서 가져오는 것

⚠️ Importrange 사용 시 만날 수 있는 에러 해결법

1. 스프레드시트에 대한 액세스 권한이 없습니다. (You don't have permission to access that sheet.)
   → 위에서 이미 설명드린 것처럼 최소 보기 권한 정도는 있어야 ; Importrange로 불러올 수 있습니다. 소유자에게 액세스 요청을 해주세요.
2. 결과가 너무 큽니다. (Result too large)
   → 구글링 - 2023년 최근에도 “15,000셀 이상이 넘어가면 해당 에러가 발생할 수 있다.”라고 합니다. 이게 1만 5천 셀이 넘는다고 100% 발생하는 게 아니란 걸 (아래 왼쪽) 스크린숏을 통해 알 수 있습니다. 하지만, 일어날 확률이 꽤 높기 때문에 15,000셀을 피해 주시는 게 좋습니다.

 

📌 Pro Tip : 다만, 나는 무조건 15,000셀 이상을 불러와서 사용해야 한다면? Bracket - wikipedia

{ }	(중) 괄호	Braces / curly brackets

{} (중) 괄호 +;(세미콜론) → 두 범위의 테이블을 아래(세로)로 쌓는다.

여기서 주의할 점은 ; 대신에 , 을 사용하면 → 옆으로(가로)로 쌓인다. 이 또한 활용할 경우가 있으니 참고 바랍니다.

={IMPORTRANGE("1Zm5GRAs7DJTwTPESw7f0AZM0x10-fz0MqNCKvt70d1g","RAW!A1:G100");IMPORTRANGE("1Zm5GRAs7DJTwTPESw7f0AZM0x10-fz0MqNCKvt70d1g","RAW!A101:G300")}

1. 배열 결과는 A10에서 데이터를 덮어쓰기 때문에 스프레드시트에서 펼쳐지지 않습니다. (Array result was not expanded because it would overwrite data in #(Cell Number))

→ 오류 메시지에 정답이 있습니다. Importrange는 다른 파일의 데이터를 단순하게 보여주는 것이, 그 상태에서 수정을 할 수는 없습니다. (수정을 원한다면 원본 데이터 수정 필요). 해당 문제의 해결 방법은 A10의 데이터를 Delete키로 지워주시면 해결됩니다.

1. 내부 에러 (Import Range Internal Error)

→ 구글링 - 정확한 원인 파악은 안 되지만, importrange의 과도한 사용이나 구글서버가 불안한 날이면 어김없이 찾아왔던 것 같습니다. 해결방법은 하루 이틀 기다리거나, Importrange 범위를 약간 수정했을 때 문제가 해결되는 경우도 있긴 했습니다. (이건 답이 없습니다.)

---

[Query + Importrange 활용 예시]

예제파일 1

**주의 사항 : 동일한 스프레드시트에서 =query를 사용할 때(A, B, C 등 열을 가져 왔음)와 다르게 =Importrange를 사용한 경우에는 A,B,C 형식이 아닌 **Col1,Col2,Col3** 형식으로 사용해야 에러가 나지 않습니다.

=QUERY(IMPORTRANGE("1Zm5GRAs7DJTwTPESw7f0AZM0x10-fz0MqNCKvt70d1g","RAW!A:G"),"SELECT Col2,SUM(Col7) WHERE Col7>=20 GROUP BY Col2 Order by SUM(Col7) desc Label SUM(Col7) '정원(합계)'",1)