google search results in google sheets or excel

[SOLVED] How to Get Google search results in Sheets

Here are steps to get google search results in google sheets or excel. You can also purchase this google sheet with the complete set up from the link below for $1 from here.

Get Google search results in Sheets – Download

  1. Create a google sheet in your google drive.google search results in google sheets or excel
  2. Create a google custom search from here.
  3. Enable search the entire web.google search results in google sheets or excel
  4. Copy your google custom search engine id.google search results in google sheets or excel
  5. Get your google custom search api key from heregoogle search results in google sheets or excel
  6. Formulate your api key in this format.
    https://www.googleapis.com/customsearch/v1?key={YOUR_API_KEY}&cx={CUSTOM_SEARCH_ENGINE_ID}&q={KEYWORD}

    key is your API key, cx is your google custom search id.

  7. In your google sheets, go to tools-> script editor-> paste the following code->save the file as ImportJSON.gs
    function readRows() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();
    
      for (var i = 0; i <= numRows - 1; i++) {
        var row = values[i];
        Logger.log(row);
      }
    };
    
    
    function onOpen() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var entries = [{
        name : "Read Data",
        functionName : "readRows"
      }];
      sheet.addMenu("Script Center Menu", entries);
    };
    
    
    function ImportJSON(url, query, options) {
      return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
    }
    
    
    function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
      var jsondata = UrlFetchApp.fetch(url);
      var object   = JSON.parse(jsondata.getContentText());
      
      return parseJSONObject_(object, query, options, includeFunc, transformFunc);
    }
    
    
    function URLEncode(value) {
      return encodeURIComponent(value.toString());  
    }
    
    
    function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
      var headers = new Array();
      var data    = new Array();
      
      if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
        query = query.toString().split(",");
      }
      
      if (options) {
        options = options.toString().split(",");
      }
        
      parseData_(headers, data, "", 1, object, query, options, includeFunc);
      parseHeaders_(headers, data);
      transformData_(data, options, transformFunc);
      
      return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
    }
    
    
    function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
      var dataInserted = false;
      
      if (isObject_(value)) {
        for (key in value) {
          if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
            dataInserted = true; 
          }
        }
      } else if (Array.isArray(value) && isObjectArray_(value)) {
        for (var i = 0; i < value.length; i++) {
          if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
            dataInserted = true;
            rowIndex++;
          }
        }
      } else if (!includeFunc || includeFunc(query, path, options)) {
        
        if (Array.isArray(value)) {
          value = value.join(); 
        }
        
        
        if (!data[rowIndex]) {
          data[rowIndex] = new Array();
        }
        
        
        if (!headers[path] && headers[path] != 0) {
          headers[path] = Object.keys(headers).length;
        }
        
        
        data[rowIndex][headers[path]] = value;
        dataInserted = true;
      }
      
      return dataInserted;
    }
    
    
    function parseHeaders_(headers, data) {
      data[0] = new Array();
    
      for (key in headers) {
        data[0][headers[key]] = key;
      }
    }
    
    
    function transformData_(data, options, transformFunc) {
      for (var i = 0; i < data.length; i++) {
        for (var j = 0; j < data[i].length; j++) {
          transformFunc(data, i, j, options);
        }
      }
    }
    
    
    function isObject_(test) {
      return Object.prototype.toString.call(test) === '[object Object]';
    }
    
    
    function isObjectArray_(test) {
      for (var i = 0; i < test.length; i++) {
        if (isObject_(test[i])) {
          return true; 
        }
      }  
    
      return false;
    }
    
    
    function includeXPath_(query, path, options) {
      if (!query) {
        return true; 
      } else if (Array.isArray(query)) {
        for (var i = 0; i < query.length; i++) {
          if (applyXPathRule_(query[i], path, options)) {
            return true; 
          }
        }  
      } else {
        return applyXPathRule_(query, path, options);
      }
      
      return false; 
    };
    
    
    function applyXPathRule_(rule, path, options) {
      return path.indexOf(rule) == 0; 
    }
    
    
    function defaultTransform_(data, row, column, options) {
      if (!data[row][column]) {
        if (row < 2 || hasOption_(options, "noInherit")) {
          data[row][column] = "";
        } else {
          data[row][column] = data[row-1][column];
        }
      } 
    
      if (!hasOption_(options, "rawHeaders") && row == 0) {
        if (column == 0 && data[row].length > 1) {
          removeCommonPrefixes_(data, row);  
        }
        
        data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
      }
      
      if (!hasOption_(options, "noTruncate") && data[row][column]) {
        data[row][column] = data[row][column].toString().substr(0, 256);
      }
    
      if (hasOption_(options, "debugLocation")) {
        data[row][column] = "[" + row + "," + column + "]" + data[row][column];
      }
    }
    
    
    function removeCommonPrefixes_(data, row) {
      var matchIndex = data[row][0].length;
    
      for (var i = 1; i < data[row].length; i++) {
        matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
    
        if (matchIndex == 0) {
          return;
        }
      }
      
      for (var i = 0; i < data[row].length; i++) {
        data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
      }
    }
    
    
    function findEqualityEndpoint_(string1, string2, stopAt) {
      if (!string1 || !string2) {
        return -1; 
      }
      
      var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
      
      for (var i = 0; i < maxEndpoint; i++) {
        if (string1.charAt(i) != string2.charAt(i)) {
          return i;
        }
      }
      
      return maxEndpoint;
    }
      
    
    function toTitleCase_(text) {
      if (text == null) {
        return null;
      }
      
      return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
    }
    
    
    function hasOption_(options, option) {
      return options && options.indexOf(option) >= 0;
    }

     

  8. Enter the cx, key, search query (q), API URL in different cells.
  9. Use the concatenate function to join all these 4 variables to get your final API URL. For example
    =concatenate(D4,D2,"&cx=",D1,"&q=",D3)

     

  10. Use the ImportJSON function to import the google search results. for example to get the title of the search results
    =ImportJSON($D$5,"/items/title","noHeaders")

     

  11. Here are the search results in google sheets and also a link to the sheet where you can see the search in work.google search results in google sheets

 

How to get google autocomplete inside google sheets or excel

14 thoughts on “[SOLVED] How to Get Google search results in Sheets”

  1. Hi,

    This is a very valuable piece of information. Thank you for it. However I am not able to implement it. Initially I got ‘Loading’ message for very long which I thought would be because there are too many results and second time I tried then I am getting reference error.

    To give more details, I am basically trying to extract details like Name, Phone, Number and address from Google search or from specific website such as https://www.grotal.com into my excel/google sheet when I enter a query such as Karate Classes in Navi Mumbai. Can you please help me out here?

  2. Thanks, your instructions are clear and solution works great. What I am still trying to figure out is where (in importJSON.gs) do I limit the number of results to 1?

  3. Hi, thanx for info. To make it work for me I had to substitute “,” for “;” in all formulas. It works now, but I get only 10 results. Is there a way to show 100 lines of results? Thanx for answer.

    1. Muthali Ganesh

      I currently dont know an answer to this. I think google gives a max of 10 results for every query. Try using the num function

  4. Hi, This is a great article and is very helpful.

    I just wanted to ask, Do I need to create new API key for every site I am making? if not. I am not sure why it is giving me an error 400.

    {
    “error”: {
    “errors”: [
    {
    “domain”: “usageLimits”,
    “reason”: “keyInvalid”,
    “message”: “Bad Request”
    }
    ],
    “code”: 400,
    “message”: “Bad Request”
    }
    }

    1. Muthali Ganesh

      Can you please share the API link you have formulated? Maybe you have exhausted your 100 searches limit per day

  5. Hi,

    Thanks for this. This is really helpful. Would you by chance know a way to search through multiple queries at the same time using this function of google sheets? Rather than displaying 10 results for 1 query, is there a way to display 1 result for 10 queries?

  6. I already set to num=1 and num=10 everything ok but when I set bigger than 10 it turn to error. Do you have any way to set limit to 100?

  7. I a trying to use multiple queries for the same keyword search to get more than 10 results. What would be the code look like within your example? How do I code for google to start results with page 1 and go to next pages for results? Thank you

  8. Hi – does this work for excel too or just google docs? If it does work for excel – what do I need to different?

Leave a Comment

Your email address will not be published. Required fields are marked *

Select your currency
USD United States (US) dollar