Google 검색 콘솔을 Google 스프레드시트에 연결하는 방법 - SQL로 SEO 데이터 쿼리하기

Google Sheets에 연결하기 · 검색 콘솔

다음에서 데이터를 가져오세요 검색 콘솔 ~ 안으로 Google 스프레드시트

Google Search Console 계정의 실시간 데이터를 Google Sheets로 가져와 언제든지 새로 고칠 수 있습니다. CSV 파일 업로드, Apps Script, 복사 붙여넣기 작업이 필요 없습니다. 처음 설정하는 데 약 10분 정도 소요되며, 무료 플랜으로 이용 가능합니다.

약 10분 코딩이 필요 없습니다 자격증명은 영구적으로 보관됩니다. 어떤 브라우저든, 어떤 기기든

당신이 최종적으로 얻게 될 것

Google Search Console 계정에 대한 SQL 쿼리 결과를 실시간으로 보여주는 Google Sheets 스프레드시트입니다. 데이터가 변경될 때마다 최신 행을 가져오는 버튼도 포함되어 있습니다. 예를 들어 다음과 같습니다.

영업 드라이브에 저장됨

파일편집하다보다끼워 넣다체재데이터도구확장 기능돕다

	A	B	C	D	E
1	customer_id	첫 번째 이름	성	국가	총
2	1042	마리아	알바레스	미국	1,284.50
3	1043	다니엘	오카포르	미국	612.00
4	1047	프리야	나이르	미국	2,090.75
5	1051	도마	산	미국	338.20
6	1056	루시아	페레이라	미국	1,455.00
7	1060	켄지	사토	미국	874.10
8

+ 고객 시트1 검색 콘솔에서 실시간으로 전해드립니다.

Google Search Console은 API를 통해 데이터를 관리합니다. Google Sheets로 데이터를 가져오려면 일반적으로 최신 데이터가 필요할 때마다 Search Console 대시보드에서 CSV 파일로 내보내야 합니다. 하지만 Query Streams를 사용하면 이 과정을 생략할 수 있습니다. Agent라는 작은 도우미 프로그램이 데이터가 저장된 위치에서 쿼리를 실행하고, Google Sheets 추가 기능이 필요한 행을 요청합니다. 암호화된 링크를 통해 결과만 전송되므로 Google Sheets는 어떤 기기의 어떤 브라우저에서든 어디서든 접속할 수 있습니다.

필요한 것

Query Streams 계정

이 가이드를 이용하시려면 무료 플랜으로도 충분합니다. 지금 가입하세요. my.querystreams.com.

항상 켜져 있는 컴퓨터의 에이전트

백그라운드에서 실행되는 작은 프로그램으로, Google Sheets에서 요청할 때 검색 콘솔을 호출합니다. 인터넷 연결만 있으면 됩니다.

검색 콘솔 로그인 정보

검색 콘솔에 한 번 로그인하고 읽기 권한을 허용하면 됩니다. 쿼리 스트림에는 비밀번호를 입력할 필요가 없습니다.

5단계로 연결하세요

1. 계정을 만드세요

   가다 my.querystreams.com 가입하세요. 선택하세요. 무료 이 플랜은 체험판이 아닌 정식 플랜이며, Google Search Console 계정 연결 및 Google Sheets에서 쿼리 실행 기능을 포함합니다.

2. 항상 켜져 있는 컴퓨터에 에이전트를 설치하십시오.

   로그인, 열기 다운로드인터넷에 접속할 수 있는 상시 가동 컴퓨터 또는 서버에 에이전트를 설치하십시오. 설치에는 약 1분 정도 소요되며, 이후에는 자동으로 시작됩니다.

   드라이버 대신 작은 프로그램을 사용하는 이유는 무엇인가요? 에이전트는 Windows, Mac 또는 Linux용으로 한 번만 설치하면 되며(Windows에서는 약 85MB, Mac 및 Linux에서는 100MB 조금 넘는 용량), 백그라운드에서 조용히 실행됩니다. 웹 브라우저와 마찬가지로 쿼리 스트림에 암호화된 연결을 한 번만 생성하므로 방화벽 포트를 열거나 VPN을 설정할 필요가 없습니다. 키는 해당 컴퓨터에 보관되며, 데이터는 필요한 경우 인터넷을 통해 사용자의 위치로 전송됩니다. 사용자가 해야 할 일은 추가 기능 설치뿐입니다.

   다운로드 페이지가 표시됩니다 액세스 키에이전트가 요청할 때 해당 코드를 붙여넣고 배지가 인식될 때까지 기다리세요. 쿼리 스트림에 연결됨에이전트 자체에서 할 수 있는 일은 그게 전부입니다.

   자세한 공략이 필요하시면 보여드리겠습니다. 윈도우, 스코틀랜드 사람, 리눅스.

3. 검색 콘솔 계정을 가리키도록 설정하세요.

   브라우저로 돌아가서 열기 데이터 에이전트 (아래에 데이터 상단 메뉴에서 담당 에이전트를 확인하세요. (상단 메뉴에 있습니다.) 커넥터 추가 그리고 선택하세요 Google 검색 콘솔. Need your Search Console credentials first? Open the walkthrough, then fill in:

   Create your Search Console credentials (about 15 minutes)

   Follow this with Google Search Console open in another tab. Query Streams keeps what you create here so your Agent can use it, and only ever reads from Google Search Console.

   Who can do this

   Search Console: Owner (verified or delegated) of the property, to add the service account as a user. Google Cloud: Owner of the project (you get Owner on any project you create), or the Create Service Accounts, Service Account Key Admin and Service Usage Admin roles on it.

   추천

   Google Cloud service account JSON key (a .json file of type service_account), with the service account added as a Restricted user on the Search Console property

   시작하기 전에

   • Your site already added and verified in Google Search Console. A brand-new property can take up to a week before Google has any data for it.
   • Owner access to that Search Console property. Only owners can add users. If you are a Full or Restricted user, you can do the Google Cloud part (steps 1 to 12) and ask an owner to do steps 13 to 17.
   • A Google account that can create a Google Cloud project, or an existing project you manage. If your company’s Google Cloud organization was created on or after May 3, 2024, Google blocks key downloads by default, and your Google Cloud administrator may need to allow them (see Common problems).
   • No billing account. Google does not charge for the Search Console API.
   • Already set up Query Streams’ Google Analytics 4 connector with a service account? You can reuse it: enable the Search Console API in the same project (step 4), then go to step 13 with that service account’s email. The same JSON key file goes into both connectors.
   • The Query Streams wizard open in another tab (Data Agents → Add connector → Google Search Console) with your Agent online.

   Steps

   1. Open Manage resources in Google Cloud

      Sign in with your Google account, open the Manage resources page of the Google Cloud console and click Create project.

      Already have a Google Cloud project you want to use, such as the one you made for the Google Analytics 4 connector? Skip to step 3 and pick it there. The first time you open Google Cloud, Google may ask for your country and for you to accept its Terms of Service before it shows the page.

   2. Name and create the project

      In the New project window, type a Project name such as Query Streams Search Console. Leave the parent organization or folder box as Google fills it in. If No organization is offered, that is fine. Click Create.

      Project names are 4 to 30 characters: letters, numbers, spaces, hyphens, single quotes and exclamation points. You don’t need to pick a billing account.

   3. Select the project

      Click the project picker at the top of the page, next to the Google Cloud logo, and click your project’s name. Every Google Cloud step below happens in this project.

      The most common mistake is enabling the API in one project and creating the key in another. Check the project name at the top of the page before each Google Cloud step.

   4. Enable the Google Search Console API

      Open the Google Search Console API page in the API Library. Check that your project’s name shows at the top, then click Enable.

      You can also get there from APIs & Services → Library: search for Google Search Console API. Don’t pick Indexing API or Site Verification API, which Query Streams doesn’t use. If Google shows the API is already enabled, go on to step 5. This one API covers everything Query Streams reads: your property list, sitemaps, Performance data and URL Inspection.

   5. Open the Create service account page

      Open the Create service account page. From inside Google Cloud you can also open the Navigation menu (three lines, top left), choose IAM & Admin → Service Accounts and click + Create service account.

      Reusing the service account from the Google Analytics 4 connector? Don’t create a second one. Do step 4 in its project, then go to step 13 with its email.

   6. Name the service account

      In Service account name, type query-streams-gsc. Google fills in Service account ID from it. The description is optional. Click Create and continue.

      The ID can’t be changed later. It becomes the first part of the service account’s email address.

   7. Skip the project roles

      The next section offers roles on the project. Leave the role box empty and click Continue.

      Don’t give the service account Owner, Editor or Viewer here. Its access to your Search Console data is granted inside Search Console in step 16. A Google Cloud role does nothing in Search Console and only widens what the key can do in Google Cloud.

   8. Finish creating the service account

      Leave the last section (Service account users role and Service account admins role) empty and click Done. You are back on the Service accounts list.

   9. Copy the service account email

      In the list, find the new account. Its email looks like [email protected]. Copy it and keep it for step 16.

      Copy the whole address, through .iam.gserviceaccount.com. The same address is in the key file from step 12, on the client_email line. Google says a new service account can take a minute or more before it is usable.

   10. Open the Keys tab

       Click the service account’s email address, then click the Keys tab.

   11. Start a new key

       Click Add key, then choose Create new key.

       If a box titled Service account key creation is disabled appears, your organization blocks key downloads. See Common problems.

   12. Download the JSON key

       Choose JSON as the Key type and click Create. Your browser downloads a .json file, usually into your Downloads folder.

       This download happens once: you can’t download the same file again. If you lose it, create a new key and delete the old one. Don’t choose P12. Keep the file private. Don’t email it or put it in a shared folder, because anyone who has it can read this Search Console data. Query Streams stores it for your Agent and only ever reads with it.

   13. Open Search Console and pick the property

       Open Google Search Console, signed in as an owner of the site. In the property drop-down at the top left, pick the property you want Query Streams to read.

       If the site is listed twice, once as a bare name (example.com) and once as an address (https://www.example.com/), the bare name is the Domain property. It covers every subdomain and both http and https, so it is usually the one to pick.

   14. Open Users and permissions

       In the left menu, scroll down and click Settings. Then click Users and permissions.

       Only property owners see Users and permissions. If it isn’t there, you are a Full or Restricted user: send the email from step 9 to an owner of the property and ask them to do steps 13 to 17.

   15. Click Add user

       Click Add user at the top right of the Users and permissions page. The Add user box opens.

   16. Enter the service account email and choose Restricted

       Paste the service account email from step 9 into Email address. Open the Permission list and choose Restricted.

       Restricted is enough: Google lets Restricted users view every report, including Performance, and use URL Inspection. Full also works. Either way the Agent asks Google only for read-only access, so it can’t submit sitemaps, remove URLs or change settings.

   17. Add the service account

       Click Add. The service account now shows in the property’s user list with Restricted permission.

       If Search Console won’t accept the address (for example, it says the email doesn’t match a Google Account or wasn’t found), see Common problems. Google has had a known problem with newly created service accounts since April 2026.

   18. Note how the property is written

       Look at the property’s name in the property drop-down. A bare name like example.com is a Domain property: you will enter it as sc-domain:example.com. A full address like https://www.example.com/ is a URL-prefix property: you will enter it exactly as shown, with the same http or https, the same www or no www, and the slash at the end.

       Easier still: the Detect button in the Query Streams wizard lists the exact values for you. Typing https://example.com/ when the property is https://www.example.com/, or leaving off the last slash, points at a property the service account can’t read.

   Permissions to grant

   Resource	액세스	Used for
   Search Console user permission (Settings → Users and permissions)	Restricted (Full also works)	Every gsc table: search_analytics (Performance data: clicks, impressions, CTR and position by date, query, page, country and device), sites and sitemaps, all on by default, plus url_inspection (off by default, run on demand). Google lets Restricted users view all reports and use URL Inspection.
   Google Cloud API: Google Search Console API	Enabled	Test connection, the Detect button and every gsc table. Without it Google answers 403 to every call.
   OAuth scope https://www.googleapis.com/auth/webmasters.readonly	Requested by the Agent (nothing for you to set)	Every gsc table, Test connection and Detect. Google describes this scope as read-only access, so the Agent can’t submit sitemaps, remove URLs or change any setting.
   Google Cloud project role for the service account	없음	No table needs one. Search Console access comes only from the user permission above.
   Search Console Owner (the person doing the setup, not the service account)	Owner (verified or delegated)	No table. Only owners can add users to a property.

   Paste into Query Streams

   서비스 계정 JSON 키

   The whole .json file from step 12. Click Upload from this computer under the box and pick the file (or From a file on the agent… if the file is on the Agent computer). Or open the file in Notepad (TextEdit on a Mac), select all, copy, and paste it from the first { to the last }. When you edit the connector later, leave this box blank to keep the stored key, or put a whole new file in to replace it.

   사이트 속성 URL

   Click Detect next to the box once the key is in (your Agent must be online). The box becomes a list of every property the service account can see, such as sc-domain:example.com — siteRestrictedUser. Pick yours. Or type it as worked out in step 18: sc-domain:example.com for a Domain property, or the full address with the final slash, such as https://www.example.com/, for a URL-prefix property.

   Common problems

   Test connection fails with Invalid key type. Expected ‘service_account’.

   Why: The file is not a service account key. Usually it is an OAuth client file (named like client_secret_….json) or an API key from APIs & Services → Credentials.

   Fix: Create a JSON key on the service account (steps 10 to 12) and use that file. A service account key contains the line “type”: “service_account”.

   Test connection fails with Invalid JSON format, or with Missing or empty ‘private_key’ field in service account key.

   Why: Only part of the file was copied, or the text was changed while copying.

   Fix: Click Upload from this computer and pick the .json file instead of copying. Or open the file again in Notepad (TextEdit on a Mac), press Ctrl+A (Cmd+A on a Mac), copy, and paste with nothing before or after it.

   Test connection fails with Service account ‘…’ has no Search Console properties, or Detect says Nothing detected.

   Why: Search Console has not added the service account to any property yet. Steps 13 to 17 were skipped, done on a different site, or Search Console refused the email.

   Fix: Add the exact email from step 9 as a user on the property (steps 13 to 17). Check that it now shows in Users and permissions, then click Test again.

   Test connection fails with Service account ‘…’ lacks access. Add it to Search Console property., even though the service account is listed in Users and permissions. Detect may show Google’s own message: Google Search Console API has not been used in project … before or it is disabled.

   Why: The Google Search Console API is not enabled in the Google Cloud project the key belongs to (the project_id line in the key file). Google refuses those calls with 403, which the test reports as missing access.

   Fix: Select that project in Google Cloud, enable Google Search Console API (step 4), wait a few minutes, and click Test again.

   Test connection says Connected, but gsc.search_analytics stays empty and the Agent log shows Search Analytics API error: Forbidden.

   Why: Site Property URL doesn’t exactly match a property the service account was added to: sc-domain: versus https://, www versus no www, http versus https, or a missing final slash. The test only checks that the service account can see at least one property. The sync reads the one you typed.

   Fix: In Data Agents, open the connector and click Edit. Click Detect next to Site Property URL, pick the property from the list, click Test connection, then Save changes.

   Search Console won’t add the service account. It says the email doesn’t match a Google Account, or Failed to add user: email not found.

   Why: A known Google problem: since late April 2026, Search Console and Google Analytics sometimes don’t recognise newly created service accounts for a while. Developers have reported it into September 2026. A typo or a space in the address gives the same message.

   Fix: Paste the address again with no spaces. If it is still refused, wait a few hours and try again. A service account created earlier usually works: if you have one (for example, from the Google Analytics 4 connector), add its email instead and use its key file.

   Google Cloud shows Service account key creation is disabled when you click Create new key.

   Why: Your organization enforces the policy iam.disableServiceAccountKeyCreation. Google turns it on by default for organizations created on or after May 3, 2024.

   Fix: Ask your Google Cloud administrator (someone with the Organization Policy Administrator role) to exempt this project from the policy. Google documents a tag-based exemption for a single project. Then repeat steps 11 and 12.

   A connector that used to work stops syncing, and Test connection fails with Connection error: … invalid_grant … Invalid JWT Signature.

   Why: The key was deleted, disabled or expired in Google Cloud.

   Fix: Create a new key (steps 10 to 12). In Data Agents, open the connector, click Edit, put the new file into Service Account JSON Key, click Test connection, then Save changes. Delete the old key in Google Cloud.

   Test connection fails with an error that mentions invalid_grant and Invalid JWT: Token must be a short-lived token.

   Why: The Agent computer’s clock is several minutes off. Google rejects sign-ins from a computer whose time is wrong.

   Fix: Turn on automatic (internet) time on the Agent computer, then click Test again.

   Clicks and impressions in gsc.search_analytics add up to less than the Search Console Performance chart.

   Why: Google leaves rare (anonymized) queries out of every row-by-row answer, while the chart totals still count them. Google also returns at most 50,000 rows per day per search type for each property. A URL-prefix property also covers less of the site than the Domain property.

   Fix: Nothing is broken. Compare row sums with Search Console’s table rather than the chart total, and pick the Domain property if you want the whole site.

   The newest two or three days are missing from gsc.search_analytics.

   Why: Google’s data is typically ready 2 to 3 days after the fact. The Agent reads only Google’s final data, up to three days before today.

   Fix: Nothing to change. Those days arrive on later syncs. Each sync also re-reads the last five days, so late changes are picked up.

   You want Query Streams to stop reading this property.

   Why: Deleting the connector does not remove Google’s access. The service account stays a user and the key stays valid.

   Fix: In Search Console → Settings → Users and permissions, open the menu next to the service account and click Remove access. In Google Cloud, open the service account’s Keys tab and delete the key. Then delete the connector in Data Agents.

   Limits to know

   • History: Search Console keeps the last 16 months. The Agent’s first sync reads all 16 months; older days are not available from Google.
   • Freshness: Google’s data is typically ready after 2 to 3 days. The Agent reads final data only, up to three days before today, and re-reads the last five days on each sync.
   • Rows: Google returns at most 50,000 rows per day, per search type, per property, sorted by clicks. Rare (anonymized) queries never appear as rows, so row sums are lower than Search Console’s chart totals.
   • Search types: web, image, video, news and discover, each in the search_type column. Discover rows have no query or device. News rows have no query.
   • Quota: Search Analytics allows 1,200 queries per minute per property and per user, and 40,000 per minute (30,000,000 per day) per Google Cloud project, plus a load limit counted per 10 minutes and per day. Queries grouped by page and query over long date ranges cost the most load, so a very large site’s first sync can hit quota exceeded. The Agent paces itself to 60 requests per minute.
   • URL Inspection: 2,000 inspections per day and 600 per minute per property. That is why the url_inspection table is off by default and runs only on demand.
   • New properties: Google can take up to a week to produce any data for a newly added site.
   • Keys: up to 10 keys per service account. Keys never expire by default, unless your organization sets a maximum lifetime.
   • Users: a property can have at most 100 users who are not owners.
   • Cost: Google does not charge for the Search Console API.

   Steps verified 8 October 2026. Sources: developers.google.com/webmaster-tools/v1/how-tos/authorizing, developers.google.com/webmaster-tools/v1/prereqs, developers.google.com/webmaster-tools/v1/sites, developers.google.com/webmaster-tools/v1/searchanalytics/…, developers.google.com/webmaster-tools/v1/how-tos/all-your…, developers.google.com/webmaster-tools/limits, developers.google.com/webmaster-tools/pricing, developers.google.com/search/blog/2022/10/performance-dat…, developers.google.com/search/blog/2018/06/new-url-inspect…, developers.google.com/identity/protocols/oauth2/service-a…, docs.cloud.google.com/resource-manager/docs/creating-mana…, docs.cloud.google.com/resource-manager/docs/access-contro…, docs.cloud.google.com/iam/docs/service-accounts-create, docs.cloud.google.com/iam/docs/keys-create-delete, docs.cloud.google.com/iam/docs/troubleshoot-org-policies, support.google.com/googleapi/answer/6158841?hl=en, support.google.com/webmasters/answer/7687615?hl=en, support.google.com/webmasters/answer/34592?hl=en, support.google.com/webmasters/answer/96568?hl=en, support.google.com/analytics/answer/10737381?hl=en, docs.airbyte.com/integrations/sources/google-search-console, help.imaginaire.co.uk/books/seo-tool/page/grant-access-to…, victorious.com/blog/google-search-console-add-user, seotesting.com/google-search-console/domain-vs-url-prefix, discuss.google.dev/t/problem-with-new-service-accounts/36…, piunikaweb.com/2026/05/01/google-service-account-email-no…, sanj.dev/post/google-analytics-search-console-service-acc…, dev.to/morinaga/what-i-learned-debugging-gsc-url-inspecti….

   연결 이름

   나중에 알아볼 수 있는 이름들입니다. 예를 들어, 판매 데이터베이스 같은 것이죠.

   서비스 계정 JSON 키

   검색 콘솔 속성에 사용자로 추가된 Google 서비스 계정의 키 파일 내용을 붙여넣으세요.

   사이트 속성 URL

   검색 콘솔에 표시되는 사이트 주소(예: sc-domain:example.com 또는 https://example.com/)를 정확히 입력해 주세요.

   클릭 테스트 연결읽을 때 연결됨클릭 저장연결은 몇 초 내에 에이전트에 도달합니다.

   오픈 데이터 에이전트

   테스트에 실패했나요? Usually a missing permission, or a credential from the wrong Search Console account. The walkthrough above lists the common problems and their fixes.

4. Google Sheets에 쿼리 스트림 추가

   아무 구글 시트나 열고, 다음으로 이동하세요. 확장 프로그램 → 추가 기능 → 추가 기능 가져오기검색하세요 쿼리 스트림그리고 클릭하세요 설치하다또는 바로 설치하세요. Google Workspace Marketplace에서 Query Streams를 검색하세요.구글에서 추가 기능을 허용할지 묻는 메시지가 나타날 것입니다. 이는 정상적인 절차입니다.

   이후에는 여는 모든 Google Sheets에서 오른쪽 사이드 패널에 쿼리 스트림 아이콘이 표시되며, 해당 아이콘은 다음 항목 아래에도 나열됩니다. 확장 기능별도의 로그인 절차가 없습니다. 이미 사용 중인 Google 계정을 자동으로 인식하므로, Query Streams 계정에 등록된 이메일 주소와 동일한 이메일 주소를 사용해야 합니다.

5. 첫 번째 테이블을 가져오세요

   시트 오른쪽 측면 패널에서 "쿼리 스트림" 아이콘을 클릭하거나 (아래에서 찾을 수 있습니다) 확장 기능). 선택하다 쿼리 스트리머 그리고 방금 연결한 연결을 선택하세요.

   실행하려는 쿼리를 입력하세요. 이 쿼리는 테이블에서 몇 개의 열을 가져옵니다. gsc.search_analytics 테이블:

   예시 · SQL

   SELECT date, query, clicks, impressions FROM gsc.search_analytics ORDER BY clicks DESC LIMIT 100;

   아래에 목적지 정보 행이 표시될 탭을 선택한 다음 클릭하세요. 쿼리 실행행들이 해당 탭에 1~2초 안에 나타납니다. 데이터가 변경될 때마다 '쿼리 실행'을 다시 클릭하면 탭이 즉시 업데이트됩니다.

   SQL을 모르시나요? 쿼리 스트림 포털에서 쿼리를 작성하세요. 쿼리 빌더 테이블과 열을 표시해 주므로 마우스 클릭만으로 원하는 정보를 얻을 수 있습니다. 비즈니스 플랜에서는 Nova에 "미국 고객을 보여주세요"와 같이 간단한 영어로 요청하면 Nova가 SQL 쿼리를 자동으로 작성해 줍니다. 쿼리를 저장하면 포털에서 한 번의 클릭으로 바로 사용할 수 있으며, Personal Core 플랜 이상부터는 추가 기능의 저장된 쿼리 목록에서도 확인할 수 있습니다.

좋은 다음 행보입니다

• 쿼리를 저장하세요 포털에 저장되므로 Google Sheets 또는 Excel에서 한 번의 클릭으로 다시 실행할 수 있습니다.
• 필터를 추가하세요 날짜 범위를 지정할 수 있으므로 편집 없이 동일한 쿼리로 "이번 달"이라는 답변을 얻을 수 있습니다.
• 결과를 공유하세요 동료와 함께 있을 때, 그들은 당신의 자격 증명이 아니라 행 목록만 봅니다.

사람들이 묻는 질문

관련 가이드

• Google 검색 콘솔에서 Microsoft Excel로
• Stripe에서 Google Sheets로
• HubSpot에서 Google Sheets로
• Shopify에서 Google Sheets로
• Google 스프레드시트용 파워 쿼리
• 모든 가이드