Jak połączyć Google Search Console z Arkuszami Google - zapytania o dane SEO za pomocą SQL

Połącz Search Console z Arkuszami Google

Pobierz dane z Konsola wyszukiwania do Arkusze Google

Przenieś aktywne wiersze z konta Search Console do Arkuszy Google i odświeżaj je w dowolnym momencie. Bez przesyłania plików CSV, bez Apps Script, bez kopiowania i wklejania. Za pierwszym razem zajmie to około dziesięciu minut, a plan darmowy to obejmuje.

Około 10 minut Nie potrzeba kodowania Poświadczenia pozostają z Tobą Dowolna przeglądarka, dowolne urządzenie

Co z tego wyjdzie?

Arkusz Google pokazujący na żywo wyniki zapytania SQL w Twoim koncie Search Console, z przyciskiem, który pobiera najnowsze wiersze po każdej zmianie danych. Na przykład:

Sprzedaż Zapisano na Dysku

PlikRedagowaćPoglądWstawFormatDaneNarzędziaRozszerzeniaPomoc

	A	B	C	D	E
1	customer_id	imię	nazwisko	kraj	całkowity
2	1042	Maria	Álvarez	USA	1,284.50
3	1043	Daniel	Okafor	USA	612.00
4	1047	Priya	Nair	USA	2,090.75
5	1051	Tomasz	Góra	USA	338.20
6	1056	Lucía	Ferreira	USA	1,455.00
7	1060	Kenji	Sato	USA	874.10
8

+ klienci Arkusz1 Na żywo z Search Console

Google Search Console przechowuje Twoje dane za pośrednictwem swojego interfejsu API. Przeniesienie ich do Arkuszy Google zazwyczaj oznacza eksport pliku CSV z pulpitu Search Console za każdym razem, gdy potrzebujesz nowych danych. Query Streams pomija ten problem: mały program pomocniczy o nazwie Agent uruchamia zapytanie w miejscu, w którym znajdują się Twoje dane, a dodatek do Arkuszy Google pyta go o żądane wiersze. Tylko wyniki przesyłane są szyfrowanym łączem, a Arkusze Google mogą być dostępne w dowolnym miejscu: w dowolnej przeglądarce na dowolnym urządzeniu.

Czego będziesz potrzebować

Konto Query Streams

Plan darmowy wystarczy do tego poradnika. Zarejestruj się na my.querystreams.com.

Agent na dowolnym komputerze stale włączonym

Mały program, który działa w tle i wywołuje Search Console, gdy Arkusze Google o to proszą. Wymaga jedynie dostępu do internetu.

Logowanie do Search Console

Zalogujesz się do Search Console raz i zezwolisz na dostęp do odczytu. W strumieniach zapytań nie wpisuje się żadnych haseł.

Podłącz w pięciu krokach

1. Utwórz swoje konto

   Przejdź do my.querystreams.com i zarejestruj się. Wybierz Bezpłatny plan. To prawdziwy plan, a nie wersja próbna, obejmujący połączenie konta Search Console i uruchamianie zapytań z Arkuszy Google.

2. Zainstaluj agenta na dowolnym komputerze stale włączonym

   Zaloguj się, otwórz Pobieraći zainstaluj Agenta na dowolnym komputerze lub serwerze z dostępem do internetu, który jest stale aktywny. Zajmie to minutę i od tego momentu uruchomi się automatycznie.

   Dlaczego mały program, a nie sterownik? Agent to pojedyncza instalacja dla systemów Windows, Mac lub Linux (około 85 MB w systemie Windows, nieco ponad 100 MB w systemach Mac i Linux), która działa cicho w tle. Nawiązuje jedno szyfrowane połączenie wychodzące do strumieni zapytań, tak jak robi to przeglądarka, więc nie otwierasz portu zapory sieciowej ani nie konfigurujesz sieci VPN. Twoje klucze pozostają na tym komputerze, a wiersze danych są przesyłane do Ciebie, gdziekolwiek jesteś, przez internet, jeśli zajdzie taka potrzeba. Jedyne, co musisz zrobić, to zainstalować dodatek.

   Strona pobierania pokazuje Twój klucz dostępuWklej to do agenta, gdy o to poprosi i poczekaj, aż odznaka zostanie odczytana Połączono ze strumieniami zapytańTo jest jedyna rzecz, którą robisz na samym Agencie.

   Pełne opisy przejść, jeśli ich potrzebujesz: Okna, Prochowiec, Linux.

3. Skieruj go na swoje konto w Search Console

   Wróć do przeglądarki i otwórz Agenci danych (pod Dane (w górnym menu). Twój agent jest tam wymieniony. Kliknij Dodaj złącze i wybierz Google Search Console. 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.

   Zalecony

   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

   Zanim zaczniesz

   • 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	Dostęp	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	Nic	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

   Klucz JSON konta usługi

   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.

   Adres URL właściwości witryny

   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….

   Nazwa połączenia

   Jakąkolwiek nazwę, którą później rozpoznasz, np. Baza danych sprzedaży.

   Klucz JSON konta usługi

   Wklej zawartość pliku klucza dla konta usługi Google, które zostało dodane jako użytkownik w Twojej usłudze Search Console.

   Adres URL właściwości witryny

   Twoja witryna wygląda dokładnie tak, jak wyświetla ją Search Console, np. sc-domain:example.com lub https://example.com/.

   Kliknij Połączenie testowe. Kiedy to czyta Połączony, kliknij ZapiszPołączenie zostanie nawiązane z Twoim agentem w ciągu kilku sekund.

   Otwarte Agenci Danych

   Test nieudany? Usually a missing permission, or a credential from the wrong Search Console account. The walkthrough above lists the common problems and their fixes.

4. Dodawanie strumieni zapytań do Arkuszy Google

   Otwórz dowolną Arkusz Google i przejdź do Rozszerzenia → Dodatki → Pobierz dodatki, wyszukaj Strumienie zapytańi kliknij Zainstaluj. Lub zainstaluj go bezpośrednio z Strumienie zapytań w sklepie Google WorkspaceGoogle poprosi Cię o zezwolenie na instalację dodatku, jest to normalne.

   Następnie jest on dostępny w każdym otwartym Arkuszu Google: ikona strumieni zapytań w panelu bocznym po prawej stronie, również wymieniona pod RozszerzeniaNie ma osobnego logowania. Rozpoznaje konto Google, którego już używasz, więc adres e-mail musi być adresem przypisanym do Twojego konta Query Streams.

5. Przyciągnij swój pierwszy stolik

   Kliknij ikonę Strumienie zapytań w panelu bocznym po prawej stronie arkusza (lub znajdź ją pod Rozszerzenia). Wybierać Streamer zapytań i wybierz połączenie, które właśnie utworzyłeś.

   Wpisz zapytanie, które chcesz uruchomić. To zapytanie pobiera kilka kolumn z gsc.search_analytics tabela:

   Przykład · SQL

   WYBIERZ datę, zapytanie, kliknięcia, wyświetlenia Z gsc.search_analytics ZAMÓW WEDŁUG kliknięć OPIS LIMIT 100;

   Pod Arkusz docelowy wybierz zakładkę, na której mają znaleźć się wiersze, a następnie kliknij Uruchom zapytanieWiersze trafiają na tę kartę w ciągu sekundy lub dwóch. Kliknij ponownie „Uruchom zapytanie” za każdym razem, gdy dane ulegną zmianie, a karta zostanie zaktualizowana.

   Nie znasz SQL? Zamiast tego utwórz zapytanie w portalu Query Streams. Kreator zapytań Wyświetla tabele i kolumny, dzięki czemu możesz je wskazać i kliknąć. W planach biznesowych możesz również zapytać Novę w prostym języku angielskim („pokaż mi klientów w USA”), a ona napisze za Ciebie kod SQL. Zapisz zapytanie i będzie ono dostępne jednym kliknięciem w portalu oraz na liście zapisanych zapytań dodatku od wersji Personal Core w górę.

Dobre następne ruchy

• Zapisz zapytanie w portalu, dzięki czemu możesz go ponownie uruchomić jednym kliknięciem z Arkuszy Google lub Excela.
• Dodaj filtr np. zakres dat, dzięki czemu na to samo zapytanie można odpowiedzieć „w tym miesiącu” bez konieczności edycji.
• Udostępnij wyniki z kolegą. Widzą wiersze, a nie twoje dane uwierzytelniające.

Pytania, które zadają ludzie

Powiązane przewodniki

• Konsola wyszukiwania Google do programu Microsoft Excel
• Stripe do Arkuszy Google
• HubSpot do Arkuszy Google
• Shopify do Arkuszy Google
• Power Query dla Arkuszy Google
• Wszystkie przewodniki