Introduction to Google App Script

Google Apps Script is a scripting language based on JavaScript which allows a way to easily connect third party applications and build addons for Google Products.

More than 200+ million users worldwide use Google and their products. A lot of Google products are focused primarily on productivity suite. Hence there is a constant need to connect them with other third party applications and develop addons for various use cases.

Businesses use Google products such as Sheets, Docs, Slides etc. for day to day activities and collaborate in realtime. Google App Script gives a polished and easy to use wrapper for automating processes and fetching data from other channels. This helps in creating a perfect ecosystem comprising of all different systems.

In this article we will look at a quick tutorial on how to create a simple App Script for Google Sheets which will fetch data from an API and populate in the spreadsheet.

Create New Google App Script Project

To start with, lets create a new Google App Script project. To do this create a new Sheet in Google Drive and give it an appropriate name. Open the sheet and click on Tools -> Script Editor.

A script editor window will open and new Google App Script project specific to the sheet will be created. Give the project an appropriate name for identification. You will notice the script editor is like a basic IDE where we will be writing our code. We will create a basic app script and hence only a single file is sufficient.

Your First Function

Lets write your first function. This is a simple function to call a REST API and get data. We are using a REST API tester from https://jsonplaceholder.typicode.com/users to get dummy data in JSON format. Add the below function in your script editor.

var API_URL = 'https://jsonplaceholder.typicode.com/users' //Test API URL from jsonplaceholder.typicode.com

function getData() {
    var response = UrlFetchApp.fetch(API_URL); //Fetch data from a given API URL
    var json = response.getContentText(); //Get data content responded from API URL. This is returned as text
    var data = JSON.parse(json); //Parse the text data as JSON
    Logger.log(data) //This will log the JSON response from API
    return data //Return JSON object recevied from API
}

To run the script select your function from the dropdown on the top and then press the play button.

google-app-script-run-function-codincafe (1)

Google script will determine what permissions will be required from the users profile in order to execute the function and present you with the Authorization window. Click on “Review Permissions” and allow the script to access your account. Don’t worry you are authorizing your own app script to access your account and nothing else.

After the permissions are granted the script will execute. Now press Ctrl + Enter (Cmd + Enter on Mac) to open the Logger window. This widow will show you all the data that has been fetched from the API

Well done! You have now successfully fetched data from a third party API into Google App Script.

Add Data to Sheet

Now, lets look at how you can auto populate data received from the API into the sheet.

The data received from the API is an array of JSON objects. Here is one of the sample JSON object

{
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "Sincere@april.biz",
    "address": {
      "street": "Kulas Light",
      "suite": "Apt. 556",
      "city": "Gwenborough",
      "zipcode": "92998-3874",
      "geo": {
        "lat": "-37.3159",
        "lng": "81.1496"
      }
    },
    "phone": "1-770-736-8031 x56442",
    "website": "hildegard.org",
    "company": {
      "name": "Romaguera-Crona",
      "catchPhrase": "Multi-layered client-server neural-net",
      "bs": "harness real-time e-markets"
    }
  }

Lets go ahead and add the name, email, phone and website properties from the object to our spreadsheet.

Paste the below function after getData and now run the populateSheet function.

function populateSheet() {
         var data = getData(); //Get JSON data from the above function
         var sheet = SpreadsheetApp.getActiveSheet();
         var lastRowCount = sheet.getLastRow(); //Get the last row
         var header = ['Name', 'Email', 'Phone', 'Website']; //These will be the column headers in the sheet
         sheet.clear(); // Clear the sheet if needed
         sheet.getRange(1, 1, 1, header.length).setValues([header]); //Add column header in first row
         /* Start adding data from API after column header */
         for (var i = 0; i < data.length; i++) {
         var row = [];
         var nextRow = sheet.getLastRow() + 1;
         row.push(data[i].name, data[i].email, data[i].phone, data[i].website) //Transpose data from JSON in an array
         sheet.getRange(nextRow, 1, 1, row.length).setValues([row]); //Set data to the
         }
  }

You will again be presented by the Authorization screen since some additional permission are required in the populateSheet function. If you get an error that this app is not authorized, then click on Proceed anyway and complete the steps and grant permissions.

Once the function is finished open the Spreadsheet from where you opened the script editor. Voila! Your data from the API has been populated in the spreadsheet.

You have successfully created a Google App Script which gets data from a third party API and populates to the sheet. Go crazy and start developing your addons. We will be posting more articles on some advanced development with app script. Stay tuned!

I am text block. Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Level: Intermediate

Technologies: JavaScript

post via Codincafe

Introduction to Google App Script