今回は、スプレッドシートとAppスクリプトを使用して、Google Search Console APIからパフォーマンスレポートのRAWデータを取得する方法をご紹介します。
こんな悩みをお持ちの方に良いかと思います。
- 日付 / 国 / クエリ / デバイスなど、管理画面からダウンロードできない組み合わせで実績を抽出したい
- 配布されているスプレッドシートのアドオンでは抽出が出来ないデータを見たい。特に過去へ遡ったデータ。(私の環境ではできませんでした)
- データベースを部門で扱えないのでスプレッドシートにサクッとデータを抽出したい
- 分析ツールに読み込ませる為にcsv出力が必要
機能一覧
- 指定した期間の実績を抽出
- 日次バックアップ
- 抽出したデータを指定したGoogle Driveフォルダにcsv保存
使用するツール・環境
- Google スプレッドシート
- Google App Script
- Google Cloud Platform
それでは、作り方に入っていきます!
作り方
スプレッドシートの用意
こちらのURLからスプレッドシートを開き、ご自身のGoogle Driveにコピーを保存してください。ファイル > コピーの作成
Google Search Console RAW Data Export
Google App Scriptの準備
今回はGoogle App Scriptを活用します。
コピーしたスプレッドシートを開き、スクリプトエディタを立ち上げます。ツール > スクリプトエディタ
![Screen-shot-of-launching-script-editor](https://naoshige.com/wp-content/uploads/2020/06/Screen-shot-of-launching-script-editor-1024x724.png)
プロジェクトのプロパティから“スクリプトID”をメモしておきます。ファイル > プロジェクトのプロパティ
![Script Property Menu](https://naoshige.com/wp-content/uploads/2020/06/PJ-Property-Menu.png)
Google Cloud Platform プロジェクトの設定
Google Cloud Platformにて、OAuth2.0を使った認証情報を作成します。
App ScriptのOAuth2.0のライブラリは既に追加されている状態です。
まずは、使用するGoogle Cloud Platformのプロジェクトとスクリプトを紐付けます。
Google Cloud Platformを開き、任意のプロジェクトを選択もしくは作成し、”プロジェクト番号“をメモします。
![GCP PJ Number](https://naoshige.com/wp-content/uploads/2020/06/GCP-PJ-Number-1.png)
スクリプトとGoogle Cloud Platformを紐付けます。
スクリプトエディタに戻り、Cloud Platform プロジェクト メニューを開きます。
先ほどメモをした”プロジェクト番号“を入力し、”プロジェクトを設定“を選択。リソース > Cloud Platform プロジェクト
![GCP PJ and Script Binding](https://naoshige.com/wp-content/uploads/2020/06/Screen-shot-of-google-cloud-platform-project-setting-1024x607.png)
Search Console API の設定
Search Console APIの設定に入ります。Google Cloud PlatformのAPIライブラリを開きます。APIとサービス > ライブラリ
![GCP API and Service](https://naoshige.com/wp-content/uploads/2020/06/GCP-API-and-Service-896x1024.png)
検索窓にて”Google Search Console API“を検索します。
![Search Google Search Console API](https://i1.wp.com/naoshige.com/wp-content/uploads/2020/06/Search-Google-Search-Console-API.png?fit=1024%2C424&ssl=1)
“有効にする”を選択してAPIの使用を開始します。
![Apply GSC API](https://naoshige.com/wp-content/uploads/2020/06/Apply-GSC-API-1024x593.png)
認証情報の作成
Search Console APIを有効にすると、以下の画面が表示されたかと思います。ページトップに表示されている、”認証情報を作成”を選択します。
*表示されていない場合は、メニュー > APIとサービス > 認証情報
を選択。
![Start creating OAuth client](https://naoshige.com/wp-content/uploads/2020/06/Start-creating-Oauth-client-1024x517.png)
“必要な認証情報の種類を調べる“内の”クライアント ID“を選択してください。
![OAuth Client menu](https://i1.wp.com/naoshige.com/wp-content/uploads/2020/06/Adding-Authentication-information-in-pj.png?fit=1024%2C939&ssl=1)
以下の内容で設定をし、”作成”を実行します。
ここで、メモしておいた”スクリプトID”を”認証済みのリダイレクトURI”内に書き換えます。
- アプリケーションの種類 : ウェブアプリケーション
- 名前:Search Console (*任意の名前を設定してください。)
- 承認済みのリダイレクト URI : https://script.google.com/macros/d/
{スクリプトID}
/usercallback
![Create OAuth Client ID](https://i1.wp.com/naoshige.com/wp-content/uploads/2020/06/Oauth-Client-Setting.png?fit=1024%2C782&ssl=1)
完了ポップアップが表示されます。
ここで表示される“クライアント ID”と”シークレットキー”をメモしてください。
※これらのキーは他人に共有しないように保管してください。
![Complete OAuth Client ID](https://i0.wp.com/naoshige.com/wp-content/uploads/2020/06/Oauth-Client-Complete.png?fit=1024%2C530&ssl=1)
スクリプトに戻り、スクリプト名”Variable.gs”を書き換えます。
先ほどOAuth認証情報発行でメモした”クライアント ID”と”シークレットキー”を書き換え、保存。
var CLIENT_ID = 'YOUR_CLIENT_ID';
var CLIENT_SECRET = 'YOUR_CLIENT_SECRET';
![Variables Setting](https://i2.wp.com/naoshige.com/wp-content/uploads/2020/06/Screen-shot-of-revising-client-id-and-client-secret.png?fit=1024%2C358&ssl=1)
以上で、認証情報の作成が完了しました。
いざ、実行!の前に・・・
スプレッドシートに戻り、使用可能なSearch Consoleのプロパティ一覧を取得します。Search Console > List Account Sites
を実行します。
![Initial run at List Account Sites menu](https://naoshige.com/wp-content/uploads/2020/06/Run-22List-Account-Sites22-job-.png)
初回実行時、このような認証画面が表示されます。
ご自身のGoogleアカウントが表示されていることを確認して、アカウントを選択。
![account select popup](https://i0.wp.com/naoshige.com/wp-content/uploads/2020/06/Select-Google-Account.png?fit=1024%2C721&ssl=1)
このようなポップアップが表示されます。
URL全文(画像では隠している箇所も含めて)コピーしてOKでポップアップを閉じた後、別のタブでコピーしたURLを開きます。
![callback url popup](https://i1.wp.com/naoshige.com/wp-content/uploads/2020/06/callback-url.png?fit=1024%2C478&ssl=1)
先ほどと同様に認証画面が表示されるので、アカウントを選択します。
![OAuth second permission](https://i2.wp.com/naoshige.com/wp-content/uploads/2020/06/Oauth-second-time.png?fit=1024%2C962&ssl=1)
“(任意のPJ名)への権限付与”を求められるので、”許可”を選択
![OAuth second permission confirm](https://naoshige.com/wp-content/uploads/2020/06/Oauth-second-permission-confirm.png)
最後にもう一度確認が入ります!笑
こちらも”許可”を選択してください。
![OAuth second permission re-confirm](https://i2.wp.com/naoshige.com/wp-content/uploads/2020/06/Oauth-second-permission-re-confirm.png?fit=762%2C1024&ssl=1)
これで初回認証の完了です!
テスト実行
これで全ての設定が完了し、晴れてスクリプトを実行できる様になりました!
再度、List Account Sitesを実行します。
アカウントにアクセス権が付与されているプロパティーの一覧が表示されることを確認し、設定完了となります。Search Console API> List Account Sites
![Account List Sites](https://i0.wp.com/naoshige.com/wp-content/uploads/2020/06/Account-List-Sites.png?fit=1024%2C450&ssl=1)
使い方
長くなってしまったため、使い方は次の記事で紹介します。では!
参考にしたサイト
- NETPEAK Blog|Connecting Search Console API to Google Spreadsheets
https://netpeak.net/blog/connecting-search-console-api-to-google-spreadsheets/ - Github|An OAuth2 library for Google Apps Script.
https://github.com/gsuitedevs/apps-script-oauth2