Get News Articles for Any Topic in Google Sheets for Free

With this script you can input a keyword and get a list of news articles in any language, sorted by recency, relevancy or popularity. Plus, get Moz Domain Authority and Page Authority metrics for each URL. Limited to 100 requests per day. 🚀

Check out the GIF to see it in action 🎥

---

How to add the Script to Google Sheets

1. Copy the script below:

/**
 * Returns the news based on a given topic. 
 *
 * @param {"Bitcoin"} keyword - input the topic keyword.  
 * @param {true} intitle - [OPTIONAL] input true if you want to search in article titles only.
 * @param {"2021-06-01"} datefrom - [OPTIONAL] input a date you want to start the search from (limited to one month old).
 * @param {"2021-06-13"} dateto - [OPTIONAL] input a date for the newest article allowed.
 * @param {"en"} language - [OPTIONAL] input the 2-letter ISO-639-1 code. Default set to 'en'. 
 * @param {"publishedAt"} sortby - [OPTIONAL] input how how you would like the news to be sorted. 'relevancy', 'popularity' or 'publishedAt'(default). 
 * @customfunction
 */


function news(keyword, exactmatch, intitle, datefrom, dateto, language, sortby) {


	const moz = (urls) => {

		// Moz API details
		const accessID = '';
		const secret = '';
		const authHeader = 'Basic ' + Utilities.base64Encode(accessID + ':' + secret);

		const requestBody = {
			'targets': urls
		};

		const options = {
			'method': 'POST',
			'muteHttpExceptions': true,
			'headers': {
				'Authorization': authHeader
			},
			'payload': JSON.stringify(requestBody)
		};

		const response = UrlFetchApp.fetch("https://lsapi.seomoz.com/v2/url_metrics", options);

		const json = JSON.parse(response.getContentText());

		const results = json.results;

		let rows = [],
			data;

    // loop through API response and push DA and PA to array 
		for (i = 0; i < results.length; i++) {
			data = results[i];
			let pa = !data.page_authority ? 0 : data.page_authority;
			rows.push([data.domain_authority, pa]);
		}

		return rows;

	}


	// add API key from https://newsapi.org/
	const apiKey = '';

	// set default values
	intitle = intitle || false;
	exactmatch = exactmatch || false;
	datefrom = datefrom || false;
	dateto = dateto || false;
	language = language || 'en';
	sortby = sortby || 'publishedAt';

	// surround with quotes if exact match
	keyword = exactmatch ? `"${keyword}"` : keyword;
	keyword = encodeURIComponent(keyword);

	// if title is true, search in article heading
	const q = intitle ? `qInTitle=${keyword}` : `q=${keyword}`;

	// if from or to values are false, fetch using default date values
	const newsFetch = !datefrom || !dateto ? `https://newsapi.org/v2/everything?${q}&apiKey=${apiKey}&language=${language}&sortby=${sortby}` : `https://newsapi.org/v2/everything?${q}&apiKey=${apiKey}&from=${datefrom}&to=${dateto}&language=${language}&sortby=${sortby}`;


	const options = {
		'method': 'GET',
		'contentType': 'application/json',
	};

	const response = UrlFetchApp.fetch(newsFetch);
	const json = response.getContentText();
	const articles = JSON.parse(json).articles;


	let rows = [];
	let urlRows = [];
  let data;
  
  // loop through news response and details to an array
	for (i = 0; i < articles.length; i++) {
		data = articles[i];
		rows.push([data.source.name, data.title, data.description, data.author, data.url, data.publishedAt]);
		urlRows.push(data.url);
	}
  
  // if there is a response from Moz, add DA & PA to rows array
	try {
		let domainAuthority = moz(urlRows);

		for (i = 0; i < domainAuthority.length; i++) {

			rows[i].push(domainAuthority[i][0], domainAuthority[i][1]);
		}

		rows.unshift(["Publisher", "Title", "Description", "Author", "URL", "Date", "Domain Authority", "Page Authority"]);

	} catch (e) {

		rows.unshift(["Publisher", "Title", "Description", "Author", "URL", "Date"]);

	}

	return rows;
}

---

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. Sign up to Newsapi.org

Sign up to News API: https://newsapi.org/register to get an API Key. For personal projects, the API has a free plan, limited to 100 requests per day.

---

4. Add your News API Key

Replace line 59 in the App Script with your API key.

const apiKey = '';

---

5. Sign up to the Mozscape API (Optional)

If you want Domain Authority and Page Authority metrics for each news article, sign up to Mozscape API: https://moz.com/products/api/pricing to get access details (this is optional). You can get 2,500 rows per month, for free.

---

6. Add your Moz Access ID and Secret Key (Optional)

Replace line 20 and 21 in the App Script with your Access ID and Secret Key.

// Moz API details
const accessID = '';
const secret = '';

---

7. Add the formula to any cell in your sheet

=news("bitcoin")

There are multiple parameters you can use to customise the formula for your needs. The entire formula arguments are below:

=news(keyword, exactmatch, intitle, datefrom, dateto, language, sortby)

Each argument is described in more details below:

• keyword: the string you want to search for.
• exactmatch: (true / false). Whether you’d like to search for the exact match version of the keyword.
• intitle: (true / false). Whether you’d like to search for the keyword in article titles.
• datefrom: the date you want to start the search from (limited to one month old on free plan).
• dateto: the date for the newest article allowed.
• language: the 2-letter ISO-639-1 code. Default set to ‘en’.
• sortby: how you would like the news to be sorted:
  
  ‘relevancy’ = most relevant for your keyword.
  ‘popularity’ = most popular for your keyword.
  ‘publishedAt’ = most recently published for your keyword.

*if you want to search by dates, make sure to add dates for datefrom AND dateto, otherwise the articles will be ordered by the most recent as default.

---

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets