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.
To run the script select your function from the dropdown on the top and then press the play button.
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
"name": "Leanne Graham",
"street": "Kulas Light",
"suite": "Apt. 556",
"phone": "1-770-736-8031 x56442",
"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.
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!