What is App Script?

Apps Script is a scripting platform developed by Google for light-weight application development in the Google Workspace platform. Google Apps Script was initially developed by Mike Harm as a side project whilst working as a developer on Google Sheets.

Why do we need App Script ?

Apps Script is versatile. Among other things, you can: Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms. Apps Script is free to use, and all you need to get started is a Google account. So, if you use Gmail, you can start coding in Apps Script in your browser, for free, right now. If you use Sheets, you can start. If you use Docs, you can start.

Google Suit
Javascript

Google App Script has a JavaScript engine that parses and executes script code. The runtime provides rules for how memory is accessed, how the program can interact with the computer's operating system, and what program syntax is legal. Each web browser has a runtime environment for JavaScript. You can also use modern ES6 syntax check here w3schools for ES6.

Inventory Optimization
App Script Inventory Optimization

Creating App Script

There are many ways to create App Script, You can directly create from Google Sheet, Google Doc, Google Slides and Google Apps Script. To create App Script go to the Tools menu and select “<> Script editor”. If you want to create from Apps Script just follow these steps

  • Go to : Google Apps Script
  • Click on + New project
  • Your App Script was created with one empty function as shown above screenshot.
Authorization for Google Services

App Script requires user authorization to execute the script and access the user data from google. When you run your App Script for the first time you will get this below popup. If you add any service or library which requires google data then this popup again apprises.

Authorization for Google Services

When you click the Authorize button then another popup comes which shows you all the permissions which are required to execute the script if you Accept then permission granted for the user.

Authorization window for Google Services

To read more click here Authorization.

Troubleshooting App Script

Troubleshooting is an important part of the development process. we know when we write code on the first try we will get some errors like syntax errors. When App Script encounters any error it will display an error message.

Common errors related to App Script: Common Errors.

Quotas and Limitations

Apps Script services have daily quotas and limitations on some features. If you exceed a quota or limitation, your script throws an exception and execution stops. If you want to get more details regarding limitations click here.

Logging

App Script provides us three different types of logging mechanisms

  • Apps Script execution log
  • Cloud Logging
  • Error Reporting

Apps Script execution log ( built-in )

This log is lightweight and streams in real time, but persists only for a short time.

Cloud Logging

The cloud logging interface in the Developer Console, which provides logs that persist for many days after their creation.

Error Reporting

The Error Reporting interface in the Developer Console, which collects and records errors that occur while your script is running.

Logger Class

This Logger class allows us to print information into debugging logs. Logger objects exist in App Script which have different methods like log, getLogs, clearLogs.

Logger Class in App Script

Logger Vs. Console

Logger and console both are having some limitations. In Logger we cannot print large amounts of text but it will print objects as value whereas console will print large amounts of text but it cannot print object as value as shown in below screenshot.

Logger Vs. Console
Basic examples of App Script

Reading information from Google Sheet

Step 1 : Create a new Google Sheet or you can click here Google Sheet to create.

Step 2 : Then go to Tools menu and click on “<> Script editor” as shown below

Reading information from Google Sheet

Step 3 : After that a new tab will be open which is an online App Script IDE. This Google Sheet and App script are both in same space

Google Sheet and App script are both in same space

Step 4 : Let’s insert some dummy data into the Google sheet

inserting dummy data into the Google sheet

Step 5 : Writing the code to fetch information from the Google Sheet

                               
								function readNameAndEmail(){
								var activeSheet = SpreadsheetApp.getActiveSheet();
								var rows = activeSheet.getRange("A1:B4").getValues();
								console.log(rows);
								}
                                
                            

Step 6 : Authorization & Execute the script

Function for particular cell

We can use the App Script function in the spreadsheet and execute for a particular cell. Suppose we have one function convertIntoDate which takes a number as an arg (unix epoch time) and this function will convert the given value into date format.

                               
								function convertIntoDate(utcSeconds){
								var date = new Date(0);
								date.setUTCSeconds(utcSeconds);
								return date
								}

                                
                            
Custom menu and sub-menu

We can extend some functionality for Google sheet, docs,and forms using App Script. Menus will be created if it is bound to the document, spreadsheet, or form. When the user opens the file once it is loaded into the browser then opOpen() executes and creates a UI menu.

                               
									function onOpen() {
									 var ui = SpreadsheetApp.getUi();
									 ui.createMenu('My Custom Menu')
										 .addItem('Show Current Datetime', 'showDatetime')
										 .addSeparator()
										 .addSubMenu(ui.createMenu('Sheet')
											 .addItem('Create new sheet', 'createSheet')
											 .addItem('Delete active sheet', 'deleteSheet'))
										 .addToUi();
									}
                                
                            
                               
										function showDatetime() { 
										SpreadsheetApp.getUi()
										.alert('showDatetime function executed'); 
										}
                                
                            
                               
									function createSheet() { 
									SpreadsheetApp.getUi()
									.alert('createSheet function executed'); }
                                
                            
                               
									function deleteSheet() { 
									SpreadsheetApp.getUi()
									.alert('deleteSheet function executed'); 
									}
                                
                            
Other links

App Script Limitation: Limitation
App Script Video : Video library
App Script : Home
ES6: w3schools