Script projects

Scripts are organized as projects. Projects can be of two types, standalone and bounded to a gtype (Google Drive native file type, such as Sheets, Docs, and Forms) file. Standalone scripts are created in a separate script file, you can see these files listed among other files in Drive. Bounded scripts are embedded within individual gtype files and created using the respective applications. As you can see, the standalone script files, among other files in Drive, you can open directly from Drive, but bounded script can be opened within respective applications only. However, bounded script will have more privileges over parent file than standalone scripts. For example, you can get access to the active document within bounded scripts, but not within standalone scripts.

Creating standalone script projects

To create a standalone script file follow these steps:

Follow the steps as described in the Creating Google Sheets in Drive and sharing them with your friends and the public section.
Navigate to NEW | More | Google Apps Script rather than the spreadsheet, as shown in the following screenshot:
A new untitled project will open in a new browser tab or window. The new project includes one code file, Code.gs, with a blank function, myFunction, as shown in the following screenshot:
To save or rename the new project, press Ctrl + S on your keyboard or click on the Save icon (floppy disk) in the editor. If you are saving the project for the first time then a prompt will appear to enter a new project name. Enter the project name (whatever you like) and click on the OK button. The new script file will be saved in the current folder:

Creating new projects in Sheets

Create a new Sheet or open the existing one. You will see a number of menu items at the top of the window. Now, follow these steps:

Click on Tools and select Script editor..., as shown in the following screenshot:
A new browser tab or window with a new project selection dialog will appear, as shown in the following screenshot:
Click on Blank Project or close the dialog (you do not need to always select Blank Project, just this time). A new untitled project will open in a new browser tab/window.
Save the project as described in the preceding section.
Tip
Although you can create as many bounded projects as you like, one project per file is enough. Creating just one project per file may help you to avoid problems with duplicate function and variable names.

Congratulations! You have created a new script project. By following the preceding steps you can create script projects in Docs and Forms too.

Creating a custom formula in Sheets

Open the spreadsheet you created earlier and make the following changes:

In columns A and B, type a few first and last names.
In cell C2, type (including the equals sign) =CONCATENATE(A2," ", B2).

Now you can see the first name and last name in cells A2 and B2 respectively, concatenated with a space in between.

CONCATENATE is Google Sheet's built-in formula. You can also create your own, called custom formula:

Open the script editor and copy-paste this code:
```
function myFunction(s1,s2) {
  return s1 + " " + s2;
}
```
Here is the screenshot for the same:
Press Ctrl + S on your keyboard or click on the Save icon in the editor to save the script.
Now return to the spreadsheet, and in cell C2, type =myFunction(A2,B2).
This works in exactly the same way as the built-in formula. You can extend your formula to other cells below C2. This is a simple formula, but you can create complex formulae as per your requirements.
Your custom formula should return a single value or a two-dimensional array. The following screenshot shows how a custom function will work:

Congratulations! You have created a custom formula.

Note

To add code completion and/or tooltips for your custom function, add the following comments at the preceding lines of code in the function:

/**
 * Concatenates two strings
 *
 * @customfunction
 */
function myFunction(s1,s2){
      …

Learning Google Apps Script

By : Ganapathy

Learning Google Apps Script

By: Ganapathy

Overview of this book

Script projects

Creating standalone script projects

Creating new projects in Sheets

Tip

Creating a custom formula in Sheets

Note

Learning Google Apps Script

By : Ganapathy

Learning Google Apps Script

By: Ganapathy

Overview of this book

Script projects

Creating standalone script projects

Creating new projects in Sheets

Tip

Creating a custom formula in Sheets

Note

Create a Note

Delete Bookmark

Delete Note

Edit Note

Confirmation

Buy this book with your credits?