Apps Script is a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products. In this article we will see how user information can be fetched through Google API and display in Google Sheet using Apps Script.
What is a Apps Script ?
Apps Script is a service to automate business systems, host web applications, customize functions and much more. It is written in simple javaScript and html with various built-in libraries to interact with G-Suite applications.
Few most common usage of Apps Script are as below:
- Google Cloud: Interact with google cloud functions.
- Create Web UI form and get data consolidated in excel
- Google Calendar: Get and Manage Events.
- Gmail: Get and Manage Emails: Create and Send Email, Search Email
- Managing Google Drive, Google Slides and many more interactions with Google applications.
How to install Apps Script ?
Apps Script is provided by Google and executed on Google servers. It requires only Gmail account to access it. There is no additional installation required.
How to access Apps Script ?
Use link : https://script.google.com/home
Apps Script is bound to Google Sheets as an extension.
Apps Script interaction with Google API
Step 1: Create a bounded Apps Script project with Google Sheet.
- Open a new Google Sheet and Save it.
- From Menu bar, click on Extensions and Apps Script.
This will create a Apps Script project linked with Google Sheet.
Step 2: Define request parameters for Google API
As part of this article, I have considered getting user Information of logged-in user though Google API.
Google API for userInfo : https://www.googleapis.com/oauth2/v2/userinfo
- Create a new script file : UserInfo.gs
- Create a function as getUserInfo() and define request parameters:
method: Define http method as per defined API.
contentType: Define format of the request body
muteHttpExceptions: If true, fetch does not throw exception and instead return HttpResponse in case of failure.
headers: pass Access Token as Authorization parameter
What is ScriptApp ?
ScriptApp is a defined class to control authorization and triggers.
ScriptApp.getOAuthToken() gets the OAuth2.0 token for the effective user who is authenticated.
Please note that, running Apps Script requires authorization to access user data. When script is launched first time, it asks for ‘Authorization required’.
Click on Review permission. At next step, it will prompt for authentication. Please enter Gmail account credentials. After authentication, read the provided terms carefully and provide consent.
Step 3: Make a request to Google API
- Create a new script file : Properties.gs and define the URL as below:
- Make a request to USER_INFO by passing defined parameters in getUserInfo() function.
Step 4: Parse and Read the response
Use JSON.parse to parse String into JSON object. In case you need to verify the response use console.log to print the result.
Step 5: Populate Data in Google Sheet
- Create a new function populateData();
- Get the active sheet using SpreadsheetApp, clear the existing content or any existing filter and declare a name to the Sheet.
What is SpreadSheetApp ?
SpreadSheetApp is defined class to access and create/modify google sheet files.
- Define headers, apply styles and freeze the header row.
- Define cell range and populate the content/response data as per headers.
- Load image and set image to specified cell.
- Create filter and adjust Column Width and Row Height
Step 6: Add User Information Menu in Google Sheet
- Create onInstall() and onOpen() functions in Code.gs
- Using SpreadsheetApp create Menu and add a Item in the menu.
- In addItem() method, ‘User-Info’ is the label and ‘getUserInfo’ is the defined function which gets called.
Step 7: Run the project
- As we have created a bounded application with Google Sheet, just clicking on Run will apply the changes on Google Sheet.
Conclusion
This tutorial explains how to integrate Apps Script with Google API. There is a lot more you can do in Apps Script which makes computing in excel, customization of functions and hosting web application easy and within a short period of time. It is a fun learning.
#AppsScript #GoogleAPI #AutomateGoogleSheet
