google autocomplete in sheets or excel

How to get google autocomplete inside google sheets or excel

Did you know that you can get google autocomplete or google suggestions inside google sheets? This is achieved by using the google autocomplete API.

The Google autocomplete API is of the format

http://suggestqueries.google.com/complete/search?output=firefox&hl=en&q=search_query

Here are steps to get google autocomplete inside google sheet

  1. Open google sheets.
  2. Have a cell where you will be entering the search query.
  3. Go to Tools-> script editor and paste the below code and save it 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;
    }

     

  4. Formulate the google autocomplete api using the concatenate function and use the IMPORTJSON function to get search results into your sheets. For example, if my search query is entered in B2 cell, then my formula will look like this.
    =importjson("http://suggestqueries.google.com/complete/search?output=firefox&hl=en&q="&B2&" ")

     

  5. Now you will get the search results in a single cell. (for example in B5 cell.
  6. Use the transpose, array formula and split function to get these search results in different cells.
    =TRANSPOSE(arrayformula(to_text(SPLIT(B5,","))))

    google autocomplete in sheets or excel
    google autocomplete in sheets or excel

Select your currency
USD United States (US) dollar