google autocomplete in sheets or excel

[SOLVED] 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.

[SOLVED] How to get google autocomplete inside google sheets or excel 1

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 code and save it as IMPORTJSON.gs
  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