Google autosuggested questions for any keyword, across any language

by Andrew Charlton

With this script you can input your target keyword, country, and language and it will return Google autosuggested question keywords related to your target keyword. 🚀

Check out the GIF to see it in action 🎥

Get the Google Sheets Template
Learn more below 👇

How to add the Script to Google Sheets

1. Copy the script below: 

/**
* Get Google auto suggested questions for any keyword, across any language. 
*
* @param {"London"} keyword input the keyword(s).
* @param {"UK"} country input the country.
* @param {"en"} language input the language.
* @return auto suggested questions from Google Search.
* @customfunction
*/


function keywordQuestions(keyword, country, language) {


  // translate language from english to input language
  const translate = (question, language) => {
    try {
      return LanguageApp.translate(question, 'en', language);
    } catch (e) {
      throw new Error("Invalid language");
    }
  }

  const questions = ["what", "who", "where", "when", "why", "which", "how", "can", "are", "will"];

  let keywords = [];

  // loop through questions and fetch Google predicted queries for keyword
  for (i = 0; i < questions.length; i++) {

    let translateQuestions = (language !== 'en') ? translate(questions[i], language) : questions[i];

    const response = UrlFetchApp.fetch("http://suggestqueries.google.com/complete/search?&output=toolbar&hl=en&q=" + translateQuestions + "+" + keyword + "&gl=" + country);

    const xml = response.getContentText();
    const doc = XmlService.parse(xml);
    const suggestions = doc.getRootElement().getChildren('CompleteSuggestion');

    // loop through output xml and push Google keyword predictions to array
    for (j = 0; j < suggestions.length; j++) {
      const suggestion = suggestions[j];
      const data = suggestion.getChild('suggestion').getAttribute('data').getValue();
      keywords.push([translateQuestions, data]);
    }

  }
  keywords.unshift(["Question", "Search Query"]);

  return keywords;

}

2. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Script editor from the Tools menu.

Paste the script and save it.

3. Add the formula to any cell in your sheet

=keywordQuestions("London", "UK", "en")

You can replace “London” with any keyword(s), replace “UK” with any country (ISO Country Code), and replace “en” with any language (ISO Language Code).

*Because the script translates interrogative words from english to whichever language you input, it’s not always a perfect translation*.

Thanks for stopping by 👋

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓

Questions? Get in touch with me on social or comment below 👇

More Scripts

Submit your response

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

5 Responses on this post

  3. Hi Andrew, thanks for this tutorial first of all, very cool all these scripts for google sheets. Quick question, im trying this out for the Dutch language, some of the questions its translating properly into dutch, but others, for example WHO, it doesnt recognise the Dutch word for it so it returns WHO and the relevant English questions for that particular keyword. Would I need to directly put the Dutch equivalent for the 5 Ws in the script perhaps? Cheers!

    Reply

    1. No problem at all! Yeah the Google translation for questions is often quirky for some languages. You’d be better adding the dutch equivalents into the array in the script. I’ll be making a second version of this script soon to make the translation a lot better.

      Reply