How to scrape google search results inside google sheets/ excel

Here are steps to get google search results in google sheets or excel

1. Create a google sheet in your google drive.
2. Create a google custom search from here.
3. Enable search the entire web.
4. Copy your google custom search engine id.
5. Get your google custom search api key from here
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.

If you felt that this post has saved you some money, consider making a contribution at paypal.me/muthali

How to get google autocomplete inside google sheets or excel