Google Search Console provides users with reliable and accurate first-party data from Google, making it one of the most trustworthy platforms for SEO metrics. However, while it does offer built-in reporting features, these tools have limited functionality and are challenging to customize according to specific needs. That’s precisely why many SEO professionals resort to using spreadsheets to analyze their Google Search Console data. Let’s go through different methods of connecting Google Search Console to Google Sheets.
Various ways to connect Google Search Console to Google Sheets
Google Search Console integration by Coupler.io
Coupler.io lets you automate exports from Google Search Console to Google Sheets without worrying about what happens under the hood. You can define specific export parameters and schedule regular exports at desired intervals. The automated data flow saves time, reduces manual effort, and ensures a consistent and efficient export process. For instance, you can schedule reports to be exported every Tuesday at 6 p.m. This method is useful if you want to:
- Create a reporting spreadsheet/dashboard based on the responses, either in Sheets or in any other app you connect the spreadsheet to (e.g. Looker Studio)
- Have a backup version of all your responses stored in a tabular format.
Google Sheets add-ons
Google add-ons are extensions that enhance the functionality of Google Workspace applications, including Google Sheets. These add-ons can be easily installed and provide a convenient and user-friendly way to connect Google Search Console to Google Sheets.
Google Apps Script
Google Sheets has a built-in coding environment called Google Apps Script. You can use the Google Apps Script to write custom code to call the Google Search Console API, fetch the required data, and import it into Google Sheets. You can write code to automate this, ensuring your Sheets are regularly updated with the latest Search Console data.
However, it’s worth noting that using Apps Script may be challenging for non-technical users as it requires extensive knowledge of JavaScript and API development.
Manual export
Google Search Console provides an option to export data directly to Google Sheets. You can also export directly to Excel or download the data in CSV format, which can be uploaded to your desired location. However, manual exports rely on the user to consistently click on the export button feature every time reports need to be exported.
Automate Google Search Console data to Google Sheets without coding
Step 1. Extract data from Google Search Console
To set up a Google Search Console to Google Sheets integration with Coupler.io, click Proceed in the form below. We’ve already preselected the source and destination apps for you. After that, you’ll be prompted to create a Coupler.io account for free.
Check out how you can connect Google Search Console to BigQuery.
- Connect your Google account that is associated with the Search Console.
- Select the sites, the information of which you want to export, and the report type:
- Search results performance
- Search results performance by appearance
- Discover performance
- Google News performance
- URLs index performance
- Specify the start and end dates for your report
Optionally, you can select report dimensions and configure a few advanced settings: Search results type, Aggregate data by, and Data state.
Before you proceed to the next step, you have the option to add one more source to your Google Sheets connector. This allows you to consolidate data from multiple sources or multiple accounts into one destination. Once you’re ready with the first step, click Transform data.
Step 2. Transform data
At this step, Coupler.io allows you to preview the data to be loaded to Google Sheets. But not only preview. You can also transform the records to make them analysis-ready. What you can do is:
- Manage columns (hide, rename, change type)
- Filter and sort values
- Add new columns using the supported formulas
Step 3. Load data to Google Sheets and schedule refresh
Connect your Google account associated with Google Sheets and select a file and sheet where to load data. You can create a new sheet by typing a new name. Optionally, you can specify the cell range and change the import mode.
The last thing to do is enable the Automatic data refresh. This feature automates the data flow from Google Search Console to Google Sheets on a specified schedule. Toggle it on and configure the desired schedule. You can have your data refreshed automatically as frequently as every 15 minutes!
Once your GSC data is loaded into your preferred spreadsheet, you have the flexibility to perform various tasks. You can calculate custom metrics using formulas and functions, combine your Google search data with information from other sources, and conduct in-depth analysis. Coupler.io allows you to gather all your findings, insights, and custom metrics in tailored reports.
Ready-to-use Google Sheets templates for Google Search Console
Exploring data from the Search Console is mostly needed for reporting and analytics. With your GSC data in spreadsheets, you’ll need to organize it and turn it into a report. At the same time, Coupler.io offers ready-to-use dashboard templates that you start using right away.
SEO dashboard for Google Search Console
This dashboard template provides you with an overview of your website’s SEO performance. The key metrics like clicks, impressions, CTR, and average positions are visualized for quick insights. The SEO dashboard by Coupler.io allows you to monitor trends and identify which pages bring the most traffic.
The dashboard is a useful solution for SEO and marketing experts who want to keep their eyes on the ball of their website traffic performance. You’ll find insights to boost pages with high impressions but low clicks; reveal what channels are most successful, and many more. Try the dashboard template right away for free.
Technical SEO dashboard template based on Google Search Console
The data from GSC can be used in different ways including the diagnostics of the website’s technical health. Coupler.io offers a ready-to-use solution for that – a technical SEO dashboard in Google Sheets. It’s connected to Google Search Console and provides insights into the state of pages by coverage, indexing, fetch, and so on.
You can filter pages overview by the needed states to easily identify and address potential issues before they impact search visibility. On the dashboard, you can track the following:
- Total number of non-indexed pages
- Total number of 200 URLs
- Total number of 3xx URLs
- Total number of 4xx URLs
- Total number of indexed pages
If you need to visualize your data, you can leverage Google Sheets’ native features or connect Google Search Console to Looker Studio, Power BI, or other apps. Check also other Google Sheets integrations available with Coupler.io.
Connect Search Console API to Google Sheets via Apps Script
To connect the Google Search Console API to Google Sheets using Google Apps Script, follow these steps:
- Go to Google Sheets and open the sheet where you want to connect the Search Console API.
- Click on “Extensions” in the menu bar and select “Apps Script. This will open the Google Apps Script editor in a new tab.
- Go to Google Cloud console
- Click on API and Services on the left-side navigation and from the drop-down click on Library
- Search for Search console API and enable the API
- On the Credentials on the left sidebar, you can use an existing API key or create a new one (Create credentials > API key). You can restrict the key before using it in production by clicking Restrict key.
- In the Apps Script editor, copy and paste the following example code snippet provided. The code below retrieves search analytics data, including impressions, clicks, CTR, and average position, for the period of 2023-02-10 to 2023-05-23.
function fetchSearchConsoleData() {
var clientId = // YOUR CLIENT ID;
var clientSecret = // YOUR CLIENT SECRET;
var websiteProperty = // YOUR WEBSITE PROPERTY;
var auth = ScriptApp.getOAuthToken();
var service = getService(clientId, clientSecret);
if (service.hasAccess()) {
var url = "https://www.googleapis.com/webmasters/v3/sites/" + encodeURIComponent(websiteProperty) + "/searchAnalytics/query";
var payload = {
startDate: "2023-02-10",
endDate: "2023-05-23",
dimensions: ["date"],
searchType: "web"
};
var headers = {
Authorization: "Bearer " + service.getAccessToken()
};
var response = UrlFetchApp.fetch(url, {
method: "post",
payload: JSON.stringify(payload),
contentType: "application/json",
headers: headers
});
var data = JSON.parse(response.getContentText());
var rows = data.rows;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Clear existing data in the sheet
sheet.clearContents();
// Write headers
sheet.getRange(1, 1, 1, rows[0].keys.length).setValues([rows[0].keys]);
function fetchSearchConsoleData() {
var clientId = // YOUR CLIENT ID;
var clientSecret = // YOUR CLIENT SECRET;
var websiteProperty = // YOUR WEBSITE PROPERTY;
var auth = ScriptApp.getOAuthToken();
var service = getService(clientId, clientSecret);
if (service.hasAccess()) {
var url = "https://www.googleapis.com/webmasters/v3/sites/" + encodeURIComponent(websiteProperty) + "/searchAnalytics/query";
var payload = {
startDate: "2023-02-10",
endDate: "2023-05-23",
dimensions: ["date"],
searchType: "web"
};
var headers = {
Authorization: "Bearer " + service.getAccessToken()
};
var response = UrlFetchApp.fetch(url, {
method: "post",
payload: JSON.stringify(payload),
contentType: "application/json",
headers: headers
});
var data = JSON.parse(response.getContentText());
var rows = data.rows;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Clear existing data in the sheet
sheet.clearContents();
// Write headers
sheet.getRange(1, 1, 1, rows[0].keys.length).setValues([rows[0].keys]);
Note: Adjust the code as needed for your specific requirements, and replace the placeholder values such as clientId, clientSecret and websiteProperty with your actual values. Additionally, set the desired start and end dates for the data you want to retrieve.
- Next, save the script and click Run to execute the code.
- You may be prompted to authorize the permission to run the script. Click on Review permissions.
- On the Google authorization popup, select the respective Google account.
- On successful execution of the script, the required Google Search Console data will be successfully imported to the Google Sheet.
Once everything looks good, you can configure the Google Apps Script to automate data import. To do this:
- Click on Triggers
- Click Create a New Trigger.
- Now, choose the function in the apps script which you want to call regularly.
- Select event source and event type, and notification setting.
- Finally, click Save to create the Trigger
Now, Google Apps Script will run the script at your configured setting and auto-update your data in Google Sheets.
Using Google add-on to import data from Google Search Console to Google Sheets
By installing and configuring the add-on, you’ll be able to effortlessly import GSC data into Google Sheets, enabling you to analyze and explore the metrics that matter most to your website’s success. Let’s get right into it:
- Go to your Google Sheets and open your spreadsheet
- Install the GSC to Sheets add-on: From the menu, click on Add-ons and then select Get add-ons.
- In the Add-ons marketplace, search for Search Analytics for Sheets and click on the Install button to add the add-on to your Google Sheets.
- After installation, a popup will appear asking for authorization. Click on Continue and follow the prompts to grant necessary permissions to the add-on.
- Once the add-on is installed and authorized, go to the Add-ons menu and select Search Analytics for sheets > Open Sidebar to open the add-on sidebar.
- In the add-on sidebar, select the website or property you want to retrieve data for. Choose the desired site from the dropdown menu in the sidebar.
- Specify the date range, dimensions, metrics, etc, and click on the Retrieve Data button in the sidebar. The add-on will fetch the data from Google Search Console and populate it in the selected Google Sheet.
- If you want to regularly update the data in your Google Sheets, you can set up a schedule using the add-on’s settings. This will automatically fetch new data from Search Console at the specified intervals, either daily or monthly (runs every 3rd of the month).
Export data from a Google Search Console to Google Sheets manually
To manually export data from Google Search Console to Google Sheets, follow these steps:
- Log in to your Google Search Console account.
- Select the website property you want to export data from.
- From the overview page or the left sidebar, you’ll see the different report categories. Click on any of the report categories you want to export. Here we clicked on search results that are under the performance category.
- You can customize the date range and other parameters as per your requirements.
- Click on the Export button at the top of the page. You have 3 export options.
- Choose the Google Sheets option from the drop-down menu, and your data will be loaded for you in your Sheets. Alternatively, you can choose Download CSV and also upload to Google Sheets
What data can you pull from Google Search Console to Google Sheets
When connecting Google Search Console to Google Sheets, you can extract a wide range of data to analyze and utilize for improving your website’s performance. Here are some key data sets that you can pull from Google Search Console to Google Sheets:
- Search queries: From your Google Search Console, you can get information about the searches that led to clicks on your website. You can then use this information to determine which keywords are the most popular and tailor your content to those terms by analyzing statistics like impressions, clicks, CTR, and average position for each query.
- Pages: This data shows individual page performance. Each page’s visibility and user interaction may be evaluated with the use of available data like impressions, clicks, CTR, and average position. You may use this data to boost the effectiveness of your best pages and fix your worst ones.
- Countries and Devices: This data provides insights into the geographic distribution and devices utilized by users who access your website through Google search. By analyzing metrics such as impressions, clicks, CTR, and average position for each country and device category, you can optimize your SEO strategies and enhance user experiences based on specific regions or device preferences.
- Dates: This enables you to track your website’s performance across various time periods by retrieving data for specific dates or date ranges.
Errors: This data highlights indexing errors encountered by Google while analyzing your website that may hinder your website’s visibility or user experience. By promptly addressing these errors, you can ensure that search engines can properly access and index your web pages.
What is the best way to connect Google Search Console data to Google Sheets?
In our exploration of different methods, each option comes with its own set of benefits and limitations. While manual exporting and coding with Google Apps Script provide some level of control and customization, they can be time-consuming, prone to errors, and require technical expertise. Google Add-ons, on the other hand, provide a seamless integration experience within Google Sheets. However, they may not offer the same level of customization or flexibility in data retrieval and manipulation as third-party tools such as Coupler.io.
Coupler.io provides a more user-friendly approach, simplifying the data integration process and providing additional features and support. It is important to first consider your specific needs, technical expertise, and desired level of control to choose the method that best aligns with your objectives.
Automate data export from Google Search Console with Coupler.io
Get started for free
