Google Docs Editors Help Show
Sign in Google Help
Send feedback on... This help content & information General Help Center experience
Google Docs Editors
Google Docs Editors Help Sign in Google Help
Send feedback on... This help content & information General Help Center experience
Google Docs Editors
There are a lot of great things you can do with in-built functions and functionalities in Google Sheets. But there are a few things that are either not possible by default or require you to take a series of steps to achieve it. With Google Sheets Scripts, you can automate a lot of stuff and even create new Google Sheets script functions that you wish existed. In this article, I will cover the basics of Google Apps Script with some simple yet practical examples of using scripts in Google Sheets.
What is Google Apps Script (GAS)?Google Apps Script is a coding language and allows you to create automation and functions for Google Apps (which can include Google Sheets, Google Docs, Google Forms, Drive, Maps, Calendar, etc.) In this tutorial (and on this site), I will be focussing on using Script for Google Sheets. However, most of what I cover here can also be used for other Google Apps. This Google Apps Script (GAS) coding language uses Javascript and is written in the back-end of these Google Sheets (there is a neat interface that allows you to write or copy/paste the code in the back-end). Since Google Sheets (and other Google Apps) are cloud-based (i.e., can be accessed from anywhere), your Google Apps Script is also cloud-based. This means that if you create a code for a Google Sheets document and save it, you can access it from anywhere. It doesn’t reside on your laptop/system but on Google cloud servers. What Makes Google Apps Script Useful?There are many good reasons you may want to use Google Apps Scripts in Google Sheets. We’ll cover a few use cases in our Google Script tutorial, and there are plenty more such as how it: Allows You to Automate StuffSuppose you regularly download data from any tool or database and have to combine and clean it in Google Sheets. Usually, data cleaning and combining it involves a series of steps. This may not be a big deal if you have to do it only a few times, but if you have to do it quite often, automating these steps can save you a lot of time. And that’s what you can do with Google Apps Script. All you need to do is invest some time in getting the code ready once, and whenever you have to do the same steps again, you simply run the script code in Google Sheets and let GAS do all the heavy lifting for you. Can You Create New Functions in Google Script Language?There are already hundreds of awesome functions in Google Sheets, and in most cases, these function should be enough. But in some cases, you may want some additional functionality which can not be achieved with the inbuilt function (or it can be but the formula become huge and complicated). In such cases, you can quickly write a GAS code to create a custom function. These functions can be used just like regular functions in the Google Sheets document and instantly makes your work easier. Can Talk To Other Google AppsSince Google Apps Script is the common coding language behind a lot of Google Apps, you can use it to interact with other apps as well. For example, if you have 10 Google Sheets documents in your Google Drive, you can use GAs to combine all these and then delete all these Google Sheets documents. This is possible because you can use GAS to work with multiple Google Apps. Another useful example of this could be to use data in Google Sheets to quickly schedule reminders in your Google Calendar. Since both of these apps use GAS, this is possible. Extend the Functionality of Google SheetsApart from automating stuff and creating functions, you can also use the GAS to enhance the functionality of Google Sheets. While Google Sheets provides a lot of functionality for everyone, you can also code something that’s more suited to your needs. And since you can reuse the code, again and again, it makes you a lot more productive and efficient. Getting Started with Google Sheets Script EditorThe script editor in Google Sheets is the place where you can write scripts and then run it. There would be a separate script editor for different Google Apps. For example, in the case of Google Forms, there would be a ‘Script Editor’ where you can write and execute code for Google forms. Where Is the Script Editor in Google Sheets?In Google Sheets, you can find the Script Editor in the Extensions tab. Anatomy of the Google Sheet Script EditorOnce you click on the Script Editor option, it opens the Script Editor in a new window (as shown below). You can change the name of the Project by clicking on the top-left part of the screen that says ‘Untitled project’. When you click on it, it will open a dialog box where you can enter the name of the project. It takes a few seconds to implement the name change. Within a script project, you can have multiple script file. For example, if you have three different things that you want to automate in Google Sheets and you create three different scripts for it, you can have all the three different scripts in the same Project file. In the left pane of the Project, you have the default script file – Code.gs. This script file is where you can write code. You can have multiple scripts in the same script file and you can have multiple script files as well. If you click on the small downward-facing arrow at the right of the Script file name, it will show you options to rename, delete, and create a copy of the script file. Note: You always need to have at least one script file in the project. If you only have one, it will not let you delete it. On the right side of the script file is the code window where you can write the code. Script Editor ToolbarThe Script Editor Toolbar has the following options:
Google Sheets Script ExamplesLet’s take a look at a couple of real-life examples of how Google Sheets Script can help everyday users. Automatically Generate Charts With Google Spreadsheets ScriptFor this example, we can pretend that you’re working on similar sheets month to month that come with new data each time. With each new set of data, you want to create an easy-to-read chart without having to go into the charts menu every time. You can accomplish this with Google Sheets Script. In our imaginary scenario, we’ll pretend you need to compare sales statistics for each employee every month. We’ll use the following data set: We could use the following script to automatically create a graph with the data in each new sheet. function SalesChart() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheets()[0]; var SalesChart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A2:B10')) .setPosition(5, 5, 0, 0) .build(); sheet.insertChart(SalesChart); } All you have to do is:
It looks like ours ran with no issues, so happy days! Create a Custom Formula With Google Apps ScriptGoogle Sheets comes loaded with plenty of formulas right off the bat. But, what if you work with a formula constantly that is missing from their formula library? You can use a spreadsheet script to save you from having to recreate your formula every time. A simple example of this is converting Celsius to Fahrenheit. You can add this formula to your spreadsheet with the following steps.
function CTOFH (input) { return input * 1.8 + 32; } You should now be able to use the function in your spreadsheet Note that the shortcut you created won’t come up in the suggested ones, so make sure its memorable for your own sake. Google Script Editor Menu OptionsApart from the toolbar, there are many other options available in Google Apps Script in Google Sheets. While the most used options are already a part of the toolbar, there are some more options in the menu that you may need when you start working with GAS. In this section of this article, I will cover each menu option and some of the options it has. You can play around with the options on your own to get a better idea.
In this article, I have covered the basics of Google Apps Script and the overall anatomy of the interface. We hope this Google Sheets Script tutorial was useful for you. You May Also Like the following tutorials:
How do I use scripts in Google Sheets app?To write a custom function:. Create or open a spreadsheet in Google Sheets.. Select the menu item Extensions > Apps Script.. Delete any code in the script editor. For the DOUBLE function above, simply copy and paste the code into the script editor.. At the top, click Save save.. How do I open the script in Google Sheets app?Opening Apps Script from your Google Sheets Spreadsheet is as easy as clicking Tools > Script editor.
Does Google App Script work on mobile?You cannot execute Google App Script functions (gas functions) via Google Sheets mobile app. Android add-ons can utilize the Apps Script Execution API to directly call functions in Apps Script projects.
Can you run macros on Google Sheets mobile?Then, every time you want to run that macro, you'll press Ctrl + Alt + Shift + your number on a PC, and Command + Option + Shift + your number on a Mac. (Unfortunately, macros don't work on Google Sheets' iOS and Android apps yet). Let's pull it all together.
|