How to Connect Google Search Console Data to Google Sheets

If you own a website, then one of your goals will be to ensure its visibility in Google search results. Google is the go-to platform for people seeking answers, products, or services. So, how can you ensure that your website appears prominently in Google searches? This is where Google Search Console comes into play.

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. By integrating Google Search Console with Google Sheets, you can optimize your workflow, enhance collaboration with others, etc. In this blog post, we will guide you through the process of connecting Google Search Console with 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.

Various ways to connect Google Search Console to Google Sheets

There are several ways to connect Google Search Console to Google Sheets, each offering its own advantages and suitability for different user needs. Let’s explore the different methods:

• 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. 
• Third-party integration: When faced with repetitive tasks, using Google Apps Script can become time-consuming. While it is possible to automate certain aspects, setting up and maintaining complex scripts can be daunting. Third-party integrations provide the advantage of setting up automated exports from Google Search Console to Google Sheets without worrying about what happens under the hood. Coupler.io, Piwik Pro, and Two Minute Reports are some of the third-party solutions available. They let you define specific export parameters and schedule regular exports at desired intervals. This automation 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., allowing for seamless data updates and timely insights in your Google Sheets. This method is useful if you want to:
  • have a backup version of all your responses stored in a tabular format.
  • 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)
• Google 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.
• 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 

Now let’s look at a solution for automating exports from GSC to Google Sheets. We’ll use Coupler.io, an all-in-one platform for data analytics and automation that offers a code-free approach. 

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

• Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
• Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
• Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
• Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
• Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

Start 14-day free trial

With Coupler.io, you can extract your data from Google Search Console and import it to your destination (Google Sheets, Excel, BigQuery, or Looker Studio) on a schedule. This eliminates the need for manual data extraction and ensures that your data is always up to date. 

Google Search Console is currently a premium integration at Coupler.io, meaning that you can have a GSC – Google Sheets integration set up at no extra cost. Simply follow these steps:

• Sign up for a Coupler.io account if you don’t have one yet. A free 14-day trial is available, no credit card is required.
• Submit a request to the Coupler.io team, telling us what is it that you want to fetch from Google Search Console and providing all your requirements.
• In up to 14 days we’ll have you set up with a ready-to-use integration. We’ll also add 7 days to your trial so you can test out the integration and decide whether it’s something you were looking for.

Note: Premium integrations are available for users considering Starter, Squad, or Business plan. Consult the pricing for more details.

Once your data is loaded into your preferred destination, 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.

If you need to visualize your data, you can leverage Google Sheets’ native features or connect the app to Power BI. Additionally, you can use Coupler.io to directly integrate your data source into Looker Studio. 

Check also other Google Sheets integrations available with Coupler.io.

DIY integration with a JSON importer

Now, if you prefer a DIY approach, Coupler.io provides a JSON importer that allows you to connect to Google Search Console directly. This hands-on method gives you the flexibility to set up your own custom integration and import search analytics data. Here’s how to set it up.To get started:

• Sign up to Coupler.io 
• Select JSON Client as the source application and Google Sheets as the Destination. Click Proceed.

• Insert your JSON URL. Here’s an example of the JSON URL structure for the Search Console API: https://www.googleapis.com/webmasters/v3/sites/{siteUrl}/searchAnalytics/query?key={YOUR_API_KEY}&startDate={start_date}&endDate={end_date}&dimensions={dimensions}

In this URL, you need to replace:

• {siteUrl} with the URL of the website you want to retrieve data for
• {YOUR_API_KEY} with your actual API key obtained from the Google Cloud Console
• {start_date} with the desired start date in the format “YYYY-MM-DD”
• {end_date} with the desired end date in the format “YYYY-MM-DD”
• {dimensions} with the dimensions you want to retrieve data(query, page, device etc) for (comma-separated if multiple dimensions).

Here is an example: https://www.googleapis.com/webmasters/v3/sites/example.com/searchAnalytics/query?key=ABC123&startDate=2023-01-01&endDate=2023-05-31&dimensions=query,page,country,device

Note: You don’t actually need to provide any authorization to fetch data from Google Search Console all you need is your URL with the necessary parameters in it to fetch all your data.

Destination

• Select Google Sheets as the destination application. Connect your Google account – click Connect and log in to your account.

• Choose the spreadsheet and the sheet to export data to. Click Continue.

Schedule

• Set up an automatic data refresh based on how frequently you want to export data from the Google Search Console to your Google Sheets.

• When all the settings are configured, click Save and Run to successfully complete the data import from Google Search Console to Google Sheets.
• You can now click on View Results on the top-right of the page to see the imported data in Google Sheets.

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 is the best way to connect Google Search Console data to Google Sheets?

In our exploration of different methods, including manual exporting, coding with Google Apps Script, utilizing third-party tools like Coupler.io, and leveraging Google Add-ons, 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. 

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