Append your keywords with all locations, from any country

With this script you can input your target keyword and any country, and it will return your target keyword appended by all of the locations from the country, in seconds. 🚀

Check out the GIF to see it in action 🎥


How to add the Script to Google Sheets

1. Copy the script below:

/**
  * Append all locations to a keyword, for any country. 
  *
  * @param {"Hotels in"} keyword input the keyword(s).
  * @param {"United Kingdom"} country input the full country name.
  * @customfunction
  */
 
 
function localKeywords(keyword, country) {
 
 
  // Format country input with Proper Capitalisation   
  const properCase = (country) => {
    return country.replace(
      /\w\S*/g,
      (str) => {
        return str.charAt(0).toUpperCase() + str.substr(1).toLowerCase();
      }
    );
  }
 
  country = properCase(country);
 
  // Fetch countries and locations JSON
  const response = UrlFetchApp.fetch("https://raw.githubusercontent.com/ToniCifre/all-countries-and-cities-json/master/countries.json");
 
  const json = JSON.parse(response.getContentText());
  const results = json[country];
 
  // Return error if country has been input incorrectly
  if (!results) {
 
    return 'Error. Add a valid country';
 
  } else {
 
    let arr = [];
 
    // Loop through locations for country and prepend keyword
    results.forEach((location, i) => arr[i] = [keyword + " " + location]);
 
    return arr;
 
  }
}

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

=localKeywords("Hotels in", "United Kingdom")

You can replace “Hotels in” with any keyword(s) and replace “United Kingdom” with any country. Make sure to add the full country name, “United Kingdom instead of “UK”, for example.


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 *

2 Responses on this post

  1. how can i get the list of correct country name for google this formula =localKeywords(“Hotels in”, “United Kingdom”)

    1. There isn’t a country list right now, just make sure to input the full name of the country. Eg. “United Kingdom”, not “UK”.