Coupler.io Blog

How to Connect Google Search Console Data to Google Sheets

  • Dmytro Kyryliuk
    • How to connect Google search console data to Google Sheets

    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: 

    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.

    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:

    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!

    Try Coupler.io for free

    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.

    Get the free dashboard template

    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:

    Get the free dashboard template

    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:

    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.

    Once everything looks good, you can configure the Google Apps Script to automate data import. To do this:

    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:

    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:

    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:

    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
    Exit mobile version