The Beginner’s Guide to Google Sheets and App Script-1
What is Google App Script
Google App Script (GAS for short) is a script provided by Google for developing applications based on the Google Workspace.
GAS can use JavaScript as its programming language, allowing developers to use JavaScript to create automation tools and applications for Google Spreadsheets, Google Forms, Google Documents, and other Google applications. It has the following advantages:
Easy to use: Since GAS is based on the JavaScript programming language, which is a very universal language, it has a relatively simple learning curve for most developers.
Integrated: GAS can be integrated with many services in the Google Workspace, such as integrating Gmail, Calendar, Cloud Drive, and services like Google Forms, Google Spreadsheets, Google Documents, etc.
Extend Google applications: GAS can extend and automate Google applications and integrate with the APIs of various Google services and third-party APIs. For example, YouTube API, Google Maps API, etc., it can even be used to develop your own Google applications. From the perspective of extending Google services, its role is similar to Microsoft VBA for the Office suite of software.
Operates in the cloud server: GAS can operate on Google’s servers and directly access data located on Google’s servers. This design avoids the bottleneck of local server hardware performance and allows for development anytime, anywhere.
Develop and deploy anytime, anywhere: Since GAS operates on Google’s servers, the written code can be executed directly in the browser, and developers can develop, execute, and debug anytime, anywhere without geographic location restrictions, as long as there is an internet connection.
Free: Finally, using GAS is free and does not require any additional costs.
Usage: Google Spreadsheet as an example
As previously mentioned, Google App Script can be integrated with various services in Google Workspace.
Google Spreadsheet has similar functions and interfaces to Microsoft Excel. It can store various values and formulas in tables, create charts, and even export and import different spreadsheet file formats. Therefore, we sometimes use Google Spreadsheet as a database role. By using Google App Script to write programs to automate routine tasks in our daily lives, we can increase efficiency. Therefore, we choose Google Spreadsheet to explain some common ways and syntax of using Google App Script.
Installing GAS
Before you start writing the program, you need to make sure the Google App Script package is installed (as shown in picture one). The Google App Script package is a default application, and if you can’t find the package, you will need to install it manually (as shown in picture two).
First, go to the cloud drive, click on “New | More”, and see if you can see the option for the Google App Script package?
If you can find the select option for Google App Script, it means it is already installed. If you cannot find it, you need to install it from “New | More | Connect more apps.”
Developing Environment
After entering the cloud disk and clicking on “Add | More | Google App Script,” you will see the following screen. This is the Google App Script development environment, also known as the IDE. The environment displays different colors for different variables, reserved words, etc., making it as easy to use as a general IDE.
To know the IDE, we can divide it into three sections: the top, the left, and the center.
The part on top contains the project name and the deployment button.
The left-side menu is the project’s settings column and file list. The settings column can set various functions and triggering conditions related to the project, while the file list can create file formats including script files (.gs) and HTML files.
The center part of the IDE is the code editor where the developer can write code. Above the editing area is the quick function bar that allows the developer to save, execute, and debug.
Develop Web Applications by GAS
Google App Script has two modes of operation. The first mode is attached to Google Workspace services, such as documents and spreadsheets. The second mode is an independent file operation and is published as a web application, allowing the project to operate on the web.
Therefore, there are two ways to create a Google App Script project:
Create a Google spreadsheet (or start by using other Google App application) first, and then open the script editor from the menu of the spreadsheet. By this way, the code was written as attached to the Google Workspace service. The example is as seen in the green icon in the picture below. Add a Google App Script file directly from the cloud drive. The newly added file will operate as an independent file. As seen in the blue icon in the picture below.
In the development process of a Google App Script project, interaction with various Google App applications must be done through the Google App Script API. The operation and usage of each application are different, so the usage will also be different. Google App Script provides various APIs for developers to easily call and use for different Google applications.
Google App Script operates on applications through classes, each application has different class names, such as DriveApp for Google Drive and DocumentApp for Google Documents, etc. If you want to interact with Google Spreadsheet through Google App Script today, you need to know the operation of SpreadsheetApp. The following introduces several common operation methods in Google Spreadsheet.
Manipulating Google Spreadsheet
The following shows how to create a new spreadsheet, retrieve information from the spreadsheet, or open an existing spreadsheet so that you can perform the operations afterword.
Creating a Spreadsheet
The following commands help you create a new spreadsheet by using Google App Script:
create(name)
The method “create” in SpreadsheetApp allows us to create a new Google spreadsheet. As shown in the example below, the parameter for create is the name of the spreadsheet.
function myFunction() {
var ss = SpreadsheetApp.create('New Test Sheet');
Logger.log(ss.getUrl());
}
create(name, rows, columns)
If you want to create a new Google Spreadsheet with specified number of rows and columns, you can use the following way to create a Spreadsheet with Rows and Columns Specified.
This method not only allows us to create a new Google Spreadsheet, but also you can creates a new one with the given name and the specified number of rows and columns. By adding numbers of rows and columns you want in the parameter, you can pre-specify the number of rows and columns in the new spreadsheet.
function myFunction() {
var ss = SpreadsheetApp.create('New Test Sheet2',30,20);
Logger.log(ss.getUrl());
}
Note: In the equation above, you can see the following line, through Logger.log() you can display the desired information. Its function is similar to console.log() in JavaScript, where ss.getUrl() will get the path of the spreadsheet that you newly created.
Logger.log(ss.getUrl());As shown in the figure below, the spreadsheet which produced by the code above, has already pre-designated 20 columns (from column A to column T) and 30 rows (from row 1 to row 30).
Retrieve Spreadsheet Information
getUrl()
In the previous code, we will use Logger.log() which shown below. You can see that there is a ss.getUrl() inside, that help us retrieve informations of the spreadsheet.
Logger.log(ss.getUrl());The following method can be used to retrieve the URL of the spreadsheet:
function myFunction() {
var ss = SpreadsheetApp.create('New Test Sheet',30,20);
url = ss.getUrl()
Logger.log(url);
}The URL information will be displayed in the “Execution log” (as shown in the figure below)
getId()
Besides the URL of the spreadsheet, if you want to retrieve the ID of the spreadsheet, you can use the following code:
function myFunction() {
var ss = SpreadsheetApp.create('New Test Sheet',30,20);
Id = ss.getId()
Logger.log(Id);
}After run the code shown above, you will get a string with spreadsheet ID that displayed in the “Execution log”.
getName()
It can retrieve the name of the spreadsheet.
function myFunction() {
var ss = SpreadsheetApp.create('New Test Sheet',30,20);
name = ss.getName()
Logger.log(name);
}After run the code, the name of the spreadsheet, New Test Sheet, will appear in the “Execution log” .
By retrieve URL and ID of the spreadsheet, we can perform other operations on the spreadsheet through them in the future.
Open A Spreadsheet
How to open a spreadsheet that already being created?
To open an existing spreadsheet, we need a way to specifically specify which spreadsheet to open. In the previous section, we learned how to obtain the URL and ID of the spreadsheet using getUrl() and getId(). With these informations, we can open the spreadsheet by using the methods introduced below.
openById(id)
SpreadsheetApp.openById(id) is a function in Google App Script that allows us to open a Google spreadsheet using a unique identifier represented by the id parameter. The function returns a Spreadsheet object, providing subsequent access to read data on the spreadsheet.
Therefore, by providing the id of the spreadsheet as a parameter, the spreadsheet can be opened through the openById function.
function myFunction() {
var ss = SpreadsheetApp.openById('th id of the spreadsheet');
Logger.log(ss.getName());
}As shown in the following image, executing the above code will display the name of the corresponding id spreadsheet.
openByUrl(url)
SpreadsheetApp.openByUrl(url) is another GAS function that allows you to open a spreadsheet by using its URL. The URL is represented by the url parameter. This function will also return a Spreadsheet object, and providing subsequent access to the data in the spreadsheet.
Therefore, the spreadsheet can be opened simply by providing the spreadsheet URL as a parameter in the openByUrl function.
The following code, as an example, which has the only difference from the previous openById code. Being that openById is changed to openByUrl, and the input value must also be changed to the URL.
Run the code. The “Execution log” will display the name of the spreadsheet.
function myFunction(){
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/id/edit#gid=0>');
Logger.log(ss.getName());
}
Conclusion
So far, we have been able to create Google spreadsheets through Google App Script, which is a convenient tool to help us automate data processing. In the process, we can create the spreadsheet template we need by setting parameters. In addition, we can also carry out activities to open files and edit or modify existing Google spreadsheets.
In Google App Script, we can use either the openById or openByUrl method to open spreadsheets, where the former opens the spreadsheet through its ID, while the latter opens it through its URL. The usage is similar, with differences only in the parameters.
Due to space constraints, this is where we will stop for now. In the next article, we will go into detail about how to operate the cells in Google spreadsheets. This will be a very important stage as we will be processing, analyzing and summarizing data in Google spreadsheets to help us better understand the data. If you are interested in Google App Script, then the next article will be an important content that you cannot miss.
