Connect Google Sheets with 3rd Party API Using Apps Script

Apps Script, as defined by Google is a rapid application development platform that makes it fast and easy to create business applications that integrate with G Suite. Basically it allows you to write codes for Google's services like Gmail, Google Drive, Sheets, Calendar, etc to automate your workflow or interact with external services. The code is written in JavaScript and you can execute it from the browser which runs on Google's server. So you don't need to install anything extra.

If you want to skip everything and head over to the code, it's on github.

Introduction

In this tutorial, we will write scripts using Apps Script to fetch data from an external API and populate it in a Google Sheet. You will need 2 things: a gmail account and an external API. We will be using the API provided by Datanews. Datanews provides news in JSON format via their API and has a free plan. Head over to their site, sign up for the service and get your free API.

Create a new google sheet and give it a name as you like. Then go to Tools > Script Editor from the menu. An editor will open in a new tab, give the project a name in the top left.

It's time to start writing some code. Let's start with Hello World.

function myFunction() {
  var text = "Hello World";
  Logger.log(text);
}

Logger.log is equivalent to console.log from normal JavaScript. Save the project and run the script from the menu. To view the logs, goto View > Logs.

Connect API

Moving on, we will fetch some data from the API. The documentation is pretty straightforward. I will be using the headlines endpoint to fetch news headlines. Let's fetch news that contains the word "travel" in it. You API url will look like this

http://api.datanews.io/v1/headlines?q=travel&apiKey=your-api-key

Clear all the codes and replace it with the following. Initialize the API_KEY variable with your API Key.

function myFunction() {
  var API_KEY = "your api key";
  var url = "http://api.datanews.io/v1/headlines?q=travel&apiKey=" + API_KEY;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var results = data.hits;

  results.forEach(function (result) {
    Logger.log(result.title);
  });
}

UrlFetchApp.fetch is used to send requests to the API url. The response is a JSON string which is converted to object. Then we are looping through the hits array which contains news objects and finally printing the headlines.

When you run the code for the first time, you will be asked to review some permissions. Don't worry about the warning and allow all the required permissions.

Once the script finishes execution, check the logs to see the headlines being printed.

Let's make a few changes in our code to write the data into google sheets. We will create a nested object containing the news and define a range of rows and columns where the data is written. Modify your code so it looks like this.

function myFunction() {
  var API_KEY = "your api key";
  var url = "http://api.datanews.io/v1/headlines?q=travel&apiKey=" + API_KEY;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var results = data.hits;

  var sheet = SpreadsheetApp.getActiveSheet();

  var header = ["Title", "Description", "URL", "Published Date"]
  var items = [header];

  results.forEach(function (result) {
      items.push([result.title, result.description, result.url, result.pubDate]);
  });
  sheet.getRange(1,1,items.length,items[0].length).setValues(items);
}

SpreadsheetApp.getActiveSheet() gives us the current active spreadsheet. Then a list of headers is initialized which will be the first row in our sheet. Inside the loop, we are adding a list of title, description, url and published date to our nested array.

Finally sheet.getRange() allows us to select the rows and columns to fill in the data. The first 2 parameters are the rows and columns to start with, hence 1. The third parameter is the number of rows to select which is the number of news items in our items array. and the last parameter defines the number of columns which is equal to the size of each array inside our nested array.

Run the script, allow any permissions if required and on finishing the execution you will see the data printed on the sheet.

This is how you can connect with any API and populate data into google sheet.

Triggers

We can also add triggers to run the script at a certain time like a cron job or in response to an event like on opening or editing the spreadsheet.

To add trigger, goto Edit > Current project's triggers. On the bottom right, click on Add Trigger.

A menu will appear where you can configure when to run the script. To run it at a specific time or periodically, under Select event source select Time-driven and configure the settings as you want. You can run the script every hour, day, week or at a specific time as you want. If you have multiple functions, you can also select which function to run.

Add Menu

With apps script, you can also add a menu in your google sheet that will run a certain function. To add menu, goto script editor and right above myFunction add the following code.

function addMenu() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Datanews API')
      .addItem('Get News','myFunction')
      .addToUi();
}

What this function will do is add a menu in your google sheet through which you can run the script. Select addMenu function and run it once to create the menu.

After running the function, head over to your google sheet and you will see a new menu "Datanews API" under which you will have another sub menu "Get news".

To run the script, simply press on Get News. Similarly you can add multiple menus for different functions.

Conclusion

Apps Script is a handy tool that allows you to automate various things within Google's App. You can interact with Gmail, Google Drive, Calendar and a lot more using plain JavaScript code without installing anything locally. I hope this tutorial is useful for you to get started with apps script. You can do all kind of fancy stuffs with it.

Liked this article? Let me know what you think in the comments. You can find me on Twitter.