Google Sheets has a large number of built-in functions, including many that you don’t need. However, it does not have everything. At some point, you may want to do a particular task but can’t find a function for that. Well, that doesn’t mean you can’t do it. You may only need to take a few steps to get there. ?
With Google Apps Script, you can do a lot. It basically allows you to create your own functions, automate a lot of stuff, and even integrate Google Sheets with other third-party services.
The Google spreadsheet Apps Script tutorial in this article covers the basics of Google Apps Script, including several easy-to-follow examples. We will focus more on Google Sheets, though you can also write code in Google Docs, Forms, or a standalone script.
What is Google Apps Script?
Google Apps Script (GAS) is a development platform that allows you to create applications that integrate with Google Workspace apps and services.
It uses modern JavaScript as its scripting language. You don’t need to install anything. Google gives you an integrated code editor that allows you to edit your scripts within your browser. Your scripts execute on Google’s servers.
GAS plays a similar role in Google Sheets as Visual Basic for Applications (VBA) does in Excel. Both are used to extend functionality and integrate with other applications and third-party services.
What makes Google Apps Script useful?
Here are a few reasons why you would need Google Apps Script:
- Create Google Sheets custom functions.
- Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
- Interact with other Google Workspace apps and services, including Docs, Gmail, Calendar, Language, Docs, and more.
- Automate tasks using triggers.
- Build add-ons for Google Sheets, Docs, Slides, and Forms, and also publish them to the Google Workspace Marketplace.
- Develop a user interface and publish it as a web app.
- Connect with external relational databases (including Google Cloud SQL, MySQL, SQL Server, and Oracle) via the JDBC service.
If you’re looking to connect to databases or multiple cloud apps without coding, Coupler.io offers pre-built connectors to popular ones. This lets you query and import data through a user-friendly interface rather than writing a script.
Automate data import to and from Google Sheets with Coupler.io
Get started for freeWhat are Google Apps Script classes?
Classes are templates that encapsulate data with code for creating objects. Historically, JavaScript was class-free, so talking about classes may cause confusion. The classes described here are specific to Google implementations — Google refers to SpreadsheetApp, GmailApp, etc., as classes.
Google Apps Script provides several top-level classes. These main classes allow you to access features of other Google apps and services, for example:
- Google Sheets can be accessed using
SpreadsheetAppclass - Google Docs can be accessed using
DocumentAppclass - Google Drive can be accessed using
DriveAppclass - Gmail can be accessed using
GmailAppclass - Language service can be accessed using
LanguageAppclass - And more
Apps Script also provides base classes. These allow you to access user info, such as email addresses and usernames, as well as control script logs and dialog boxes. Some examples of base classes:
Browser– provides access to dialog boxes specific to Google Sheets.Logger– allows the developer to write out a value to the execution logs.Session– provides access to session information, such as the user’s email address (in some circumstances) and language setting.
In this Google Apps Script Sheets tutorial, we will also use some of the classes mentioned above. For example, we’ll use the SpreadsheetApp class to access Google Sheets’s functionalities and the LanguageApp class to access Google’s language service.
Note: Google also offers the V8 runtime for Apps Script, which supports classes. This runtime lets you create your own classes, which provide a means to organize code with inheritance. Think of it as creating a blueprint from which copies can be made.
Getting started with Google Apps Script
Enough intro — let’s dig in! ?
The best way to learn Google Apps Script is to write some code. Getting started is very straightforward — all you need is a Google account, a browser, and an internet connection.
To get started, go to Google Drive and create a new spreadsheet. Give your spreadsheet a name, for example, My First Script.
You can open the Apps Script editor by clicking Extensions > Apps Script from the menu (or Tools > Script editor if you can’t find the Extensions menu).
This will launch the Apps Script editor in a separate tab in your browser.
How to use Google Apps Script Editor
Now, we’ll show you how to use the Apps Script editor. For example, how to rename your project, add a new function, and save your changes.
How to rename your project
Scripts are organized as projects. By default, the project name for your scripts embedded within the spreadsheet file you just created is Untitled project.
At the top left, you’ll see the project name. Click on it to rename it. A small window will appear, allowing you to enter a new project title.
There is also a Code.gs file that is opened in the editor. It has a default function, which is blank, named myFunction().
function myFunction() {
}
How to add your first function
Delete the myFunction() code block so that your editor is now blank. Then, copy and paste the following code:
function writeHelloWorld() {
var greeting = 'Hello world!';
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setValue(greeting);
}
Your entire code will look like this:
The above writeHelloWorld() function writes “Hello world!” to the spreadsheet. It demonstrates that, by using Apps Script, you can manipulate a spreadsheet.
The code uses the SpreadsheetApp class to get an object representing the active sheet using the getActiveSheet() method. Then, it gets a single cell with the address A1 using the getRange() method. Another method, setValue(), is then called with a string argument to write to A1.
How to save your changes
Notice that there is an Unsaved changes alert displayed next to the project name at the top and an orange circle icon on the left side of the Code.gs file name. It means your changes have not been saved yet.
To save your changes, press Ctrl+S on your keyboard. Alternatively, you can click the disk icon (
How to run Google Apps Script
Click the Run button to execute your function. The first time you run the script, you will need to authorize it to access your data.
Click the Review permissions button. Another pop-up will appear, asking you to select an account to continue. After that, you may see a warning screen saying “Google hasn’t verified this app.” Continue anyway – because, in this case, we know it’s safe. Then, allow the script to access your Google account.
Once authorized, the writeHelloWorld() function will execute, and you’ll see “Hello world!” in A1:
The authorization process can be complex when deploying custom solutions. When you use Coupler.io to automate data flow to Google Sheets, there won’t be a need for repeated authorization workflows. Once you authorize the initial connection to your data sources and destinations, scheduled refreshes run seamlessly in the background
Google Apps Script examples
Now, let’s look at some more interesting examples using Apps Script.
Connect to other Google apps using Google Apps Script
The following example shows that a GAS written in one app (Sheets) can be used to manipulate other Google apps (Docs). Though trivial and useless, this example demonstrates a very powerful feature of GAS!
Copy and paste the following function into your editor, then click the Run button to execute it.
function createDocument() {
var greeting = 'Hello world!';
var doc = DocumentApp.create('Hello_DocumentApp');
doc.setText(greeting);
doc.saveAndClose();
}
Once authorized, it will create a new Google Document with “Hello world!” written on it.
You may find it’s a bit inconvenient to always open the editor when executing your code. A simple solution for that is to add a custom menu.
You can add the code for the custom menu within the onOpen() function. A trigger will then execute your code inside this function every time you open the spreadsheet. Here’s an example:
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('My Custom Menu')
.addItem('First item', 'function1')
.addSeparator()
.addSubMenu(ui.createMenu('Sub-menu')
.addItem('Second item', 'function2'))
.addToUi();
}
function function1() {
SpreadsheetApp.getUi().alert('You clicked the first menu item!');
}
function function2() {
SpreadsheetApp.getUi().alert('You clicked the second menu item!');
}
Note: The e parameter passed to the function is an event object. It contains information about the context that caused the trigger to fire, but using it is optional.
To test it, select the onOpen() function in the dropdown, then click the Run button.
You’ll see “My Custom Menu” in your spreadsheet menu, as shown in the following screenshot:
The onOpen() function is one of GAS’s reserved function names. Whenever you open a document, a built-in trigger executes this function first. These built-in triggers are also called simple triggers and do not need user authorization.
Other reserved function names include onEdit(), onInstall(), onSelectionChange(e), onFormSubmit(), doGet(), and doPost(). Make sure you don’t use these as your function names.
Automate tasks using Google Apps Script trigger
GAS lets you create your own triggers. These triggers are also called installable triggers because you need to authorize them before use.
With installable triggers, you can set your script to run at a certain event (when opening or editing a document, etc.) or on a schedule (hourly, daily, etc.).
Notice the following script. The getBitcoinPrice() function gets the data in the last 24 hours from the Bitstamp trading platform and outputs them in a sheet.
function getBitcoinPrice() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the sheet with the name Sheet1
var sheet = spreadsheet.getSheetByName("Sheet1");
var header = ['Timestamp','High','Low','Volume','Bid','Ask'];
// Insert headers at the top row.
sheet.getRange("A1:F1").setValues([header]);
var url = 'https://www.bitstamp.net/api/ticker/';
var response = UrlFetchApp.fetch(url);
// Proceed if no error occurred.
if (response.getResponseCode() == 200) {
var json = JSON.parse(response);
var result = [];
// Timestamp
result.push( new Date(json.timestamp *= 1000) );
// High
result.push(json.high);
// Low
result.push(json.low);
// Volume
result.push(json.volume);
// Bid (highest buy order)
result.push(json.bid);
// Ask (lowest sell order)
result.push(json.ask);
// Append output to Bitcoin sheet.
sheet.appendRow(result);
} else {
// Log the response to examine the error
Logger.log(response);
}
}
Other than running it manually, you can create a trigger that runs the function, let’s say, every hour. You can create a trigger either using a script or UI.
How to create a trigger using a script
To create a trigger that executes the getBitcoinPrice function every hour, copy and paste the following function to the editor:
function createTrigger() {
ScriptApp.newTrigger('getBitcoinPrice')
.timeBased()
.everyHours(1)
.create();
}
Then, run the function manually by clicking the Run button.
Note: Do not run the createTrigger() function more than once, or you’ll end up creating multiple triggers instead of just one.
Wait a few hours, and you should see the output in your spreadsheet, similar to the screenshot below:
You can see the list of triggers you created on the Triggers page, which can be opened from the left menu:
The Triggers page:
To set up effective triggers, you must plan carefully to avoid hitting execution limits. When you use Coupler.io for data import automation, you can schedule data refresh as often as every 15 minutes and set up incoming and outgoing webhooks to create sets of triggered dataflows. Plus, you will receive email notifications if any issues arise.
How to create a trigger using UI
You can also manually create a trigger from the Triggers page. But before that, let’s add this new function to send Bitcoin prices as an email with a PDF attachment.
function sendEmailBitcoinPricesPdfAttachment() {
var file = SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF);
var to = 'youremail@domain.com'; // change to yours
GmailApp.sendEmail(to, 'Bitcoin prices', 'Attached prices in PDF',
{ attachments: [file], name: 'BitcoinPrices via AppsScript' });
}
Then, on the Triggers page, click the Add Trigger button. A new pop-up will appear, allowing you to configure a new trigger.
To set a trigger that runs the sendEmailBitcoinPricesPdfAttachment() every day, use the following configuration:
Explanations of the options above:
- Choose which function to run: select the
sendEmailBitcoinPricesPdfAttachment()the function we just created. - Choose which deployment to run: use Head to test code.
- Select event source: select Time-driven to run the function on a schedule.
- Select the type of time-based trigger: select Day timer to run the function daily. Another dropdown will appear, allowing you to select the time of day when the function will run. Note: In the above screenshot, the time is set to 8 a.m. to 9 a.m., which means it will run at some time between these hours.
- Failure notification settings: by default, you will be notified daily if the trigger fails to run. Optionally, you can change it to hourly, weekly, or even immediately.
When finished, don’t forget to click the Save button.
Custom function examples using Google Apps Script
Google Sheets offers hundreds of built-in functions like SUM, AVERAGE, CONCATENATE, and also more advanced functions like VLOOKUP, REGEXMATCH, and QUERY. When these aren’t enough for your needs, you can use GAS to write your own functions.
Let’s look at a few examples of custom functions below.
Example #1. Custom numeric function using Google Apps Script
Here’s a simple custom function named AREAOFCIRCLE. The function calculates the area of a circle by taking a single numeric argument, which is the radius of the circle, and returns the area of a circle using the formula PI * radius2. It also validates that the radius is numeric and not less than 0.
/**Returns the area of ??the circle from the specified radius input.
*
* @param {number} radius
* @return {number}
* @customfunction
*/
function AREAOFCIRCLE (radius) {
if (typeof radius !== 'number' || radius < 0){
throw Error('Radius must be a positive number');
}
return Math.PI * Math.pow(radius, 2);
}
To use the function, write a few radius values in your spreadsheet. Then, type an equals sign followed by the function name and any input value. For example, type =AREAOFCIRCLE(A2) and press Enter. A nice thing here is that you can see the auto-complete shows the description of the new custom function:
Copy the formula down, and you’ll see the following result:
Notice that there’s an error in B5. That’s because xxxxx is not a numeric value. Thus, using =AREAOFCIRCLE(A5) returns an error message that we defined in the code.
Example #2. Custom string function using Google Apps Script
In the following example, you will see how custom functions can be used to access other Google services, such as the language service.
The following functions perform translations from English to other languages. Each of the functions takes a string argument and translates it to a different language: French, Spanish, or Italian.
function ENGLISHTOFRENCH(english_words) {
return LanguageApp.translate(english_words, 'en', 'fr');
}
function ENGLISHTOSPANISH(english_words) {
return LanguageApp.translate(english_words, 'en', 'es');
}
function ENGLISHTOITALIAN(english_words) {
return LanguageApp.translate(english_words, 'en', 'it');
}
Example usage in a spreadsheet:
The functions seem to work well for a straightforward and non-idiomatic phrase. You can also use those functions to help you translate English words into other languages. However, it’s always best to check again with native speakers for the accuracy and quality of the translations.
Example #3. Custom date function using Google Apps Script
The following function returns an array of dates, which are the first day of each month for the given year, including their day names. This demonstrates that a custom function can also return a two-dimensional array.
function FIRSTDAYOFTHEMONTH(year) {
var array = [];
for (var m = 0; m <= 11; m++) {
var firstDay = new Date(year, m, 1);
var dayName = '';
switch(firstDay.getDay()) {
case 0: dayName = 'Sunday'; break;
case 1: dayName = 'Monday'; break;
case 2: dayName = 'Tuesday'; break;
case 3: dayName = 'Wednesday'; break;
case 4: dayName = 'Thursday'; break;
case 5: dayName = 'Friday'; break;
case 6: dayName = 'Saturday'; break;
}
array.push([(m+1) + '/1/' + year, dayName]);
}
return array;
}
Type in a cell, for example, =FIRSTDAYOFTHEMONTH(2021) in A1. You’ll see a result like this:
Example #4. Custom web function for importing CSV using Google Apps Script
Suppose you want to import CSV data from a published online file. Google Sheets has a built-in function called IMPORTDATA for that. But when doing the import, you may want to add some filters. For example, to exclude several columns. In this case, you can use IMPORTDATA in combination with the QUERY function to give you more options.
Another alternative is to write your own function using GAS to avoid writing several functions in one cell. Here’s an example:
function CUSTOMCSVIMPORT(url, columns) {
var csvContent = UrlFetchApp.fetch(url).getContentText();
var csvData = Utilities.parseCsv(csvContent);
// Remove all white spaces, change to lower case, and split.
var requiredColumns = columns.split(",");
// Get the indexes of required columns
var indexesOfRequiredColumns = [];
if (requiredColumns.length > 0) {
for (var i = 0; i < csvData[0].length; i++) {
if (requiredColumns.includes((csvData[0][i]))) {
indexesOfRequiredColumns.push(i);
}
}
}
if (indexesOfRequiredColumns.length > 0) {
return csvData.map(r => indexesOfRequiredColumns.map(i => r[i]));
}
return csvData;
}
The above function allows you to import a CSV file from a URL and choose only a few columns to import.
The function has two parameters: url and columns. The second parameter (columns) accepts column names, each concatenated with a comma, for example: "columnname1,columnname2,..."
Example usage in a spreadsheet
In the following screenshot, you can see that only the columns specified in B2 are returned in the spreadsheet:
- The B1 cell contains the URL of a CSV file
http://samples.openweathermap.org/storage/history_bulk.csv?appid=b1b15e88fa797225412429c1c50c122a1
- The B2 cell contains the columns to import
dt,dt_iso,city_name,temp_min,temp_max,weather_main,weather_description
While custom functions offer flexibility, they require ongoing maintenance and troubleshooting. If you need a reliable, hands-off way to import CSV data, use Coupler.io – a no-code alternative to Apps Script for automating data flow. It allows you to schedule data refreshes, ensuring your spreadsheets contain the most recent information without manual effort.
Can I use custom functions with ARRAYFORMULA?
You can’t nest custom functions with ARRAYFORMULA, but you can modify your function to input and return a range in the form of a two-dimensional array. This will also optimize the performance of your spreadsheet since each custom function run requires a call to the Apps Script server. The more custom function calls are sent, the slower the performance of your spreadsheet will be.
Here is an example of a simple custom function that converts Fahrenheit to Celsius:
function FtoC(input) {
return (input - 32) * 0.5556;
}
We modified it to accept both a single cell and a range of cells, as follows:
function FtoC(input) {
return Array.isArray(input) ?
input.map(row => row.map(cell => (cell - 32) * 0.5556)) :
(input - 32) * 0.5556;
}
So, now you can type in =ftoc(A2:A5) and get the desired outcome.
Google Apps Script alternative for data import: Coupler.io
Coding your own GAS function for importing data (as shown earlier with the CUSTOMCSVIMPORT function) may require a lot of effort. You’ll need to test various scenarios, try different filtering criteria, and so on.
If you’re looking for a convenient solution that is powerful and does not require any coding, use Coupler.io. It’s a reporting automation platform that allows you to import data from CSV and other popular data sources into Google Sheets on a set schedule.
Coupler.io also offers more features than the IMPORTDATA function does. Here’s a snapshot of what you can do when importing data with Coupler.io:
- Avoid total data loss if an error occurs.
- ??Load data from secured links by providing authentication credentials.
- Select the number of rows and columns, as well as the order of columns.
- Automatically convert date values into the correct date format.
- Make various transformations to your data on the go before the import.
- Combine data extracted from multiple sources in a single view.
- Fetch data to multiple destinations: Google Sheets, BigQuery, Excel, etc.
- Set up the target cell/range to get the output exactly where you need it.
For a detailed comparison, check out this comparison table: IMPORTDATA vs. Coupler.io.
When using Coupler.io, it only takes three simple steps to import data from a CSV to Google Sheets:
Step 1: Collect data from your CSV
Start by clicking Proceed in the form below:
Sign up for Coupler.io at no cost and provide the CSV URL:
Proceed, leave the HTTP method unchanged, and if your CSV URL is secured, enter the authorization credentials as follows:
Here, you can also define the response rules for the returned content:
Next, you can provide URL query parameters to filter the data extracted from your CSV:
Move forward, and if you need to cut off unnecessary columns, enter the relevant column names separated by commas. An added benefit is that the order in which you list the column names will determine the order of the columns in the output. An important note: you must ensure that such headers are in the first row of the output result.
Continue, and if needed, skip rows from the top of the CSV file. This is useful when you want to exclude notation or metadata before the table structure or column headers. To do this, specify the number of rows to skip. Otherwise, Coupler.io will import all rows by default. You can also choose to ignore quotes.
Go ahead, and if you need to extract data from more CSV files or other sources, simply add as many sources as necessary and adjust the settings for each.
Once you’re all set with your data source(s), move to the next step.
Step 2: Preview and transform your data
Preview the data extracted from your CSV. If needed, you can apply any of the following transformations:
- Rename columns and change their types.
- Create new columns with custom calculation formulas.
- Sort data in ascending or descending order.
- Filter the data based on advanced criteria, as shown below:
- Aggregate data using operations like sum, average, count, min., or max. For example, you can sum the values:
If you’ve previously connected several sources, blend data in the chosen manner:
- Append – merge datasets with similar structures and columns matched by name.
- Join – combine datasets with different structures and at least one matched column.
When your data is ready, proceed to set up your destination.
Step 3: Load the data and schedule a refresh
Connect your Google account. Select a spreadsheet and the sheet where the data will be loaded. Alternatively, you can type in a name to create a new sheet.
You can also specify the target cell or range where your data will be imported. This can be done in one of the following ways:
- Overwrite all data to the right, starting from the top-left cell specified (e.g., C1) – if you need to perform calculations using imported data and your data source has no fixed number of columns.
- Insert data into a defined range (e.g., A1:H10) – if your data source has a fixed number of columns and you need to insert data without erasing existing data to the left or right.
Next, you’ll see import mode options. The first one, Replace, is suitable if you want to substitute all previously imported information with the latest data from the source. The second one, Append, adds new data below the previously imported entries. It’s ideal for tracking historical data changes.
Other available import features include:
- Last updated column – enable it to add a column showing the date and time of the last data refresh.
- Update data each run – turn this off to skip the import if no changes have been made in the source.
Proceed and toggle on the Automatic data refresh, then set the schedule you want. With Coupler.io, you can have your sheet updated as frequently as every 15 minutes.
Finally, enjoy having your CSV data automatically imported into Google Sheets on schedule.
Google Apps Script limitations
As GAS runs on Google’s server, Google sets limits on its execution, such as:
- A script cannot run continuously for more than 6 minutes. So, for example, if you use “looping” in your function, ensure that it is bug-free and will not cause an endless execution. Also, avoid handling a huge dataset that may cause the script to run for more than 6 minutes.
- A custom function cannot execute for more than 30 seconds. So, avoid handling large external data using custom functions. Otherwise, you may see a “Maximum execution time exceeded” error.
Please note that the above limitations may change in the future. You can find the complete list of GAS limitations here.
How can I learn more about Google Apps Script?
In this Google Apps Script tutorial, you’ve learned how to create custom functions, custom menus, and access to other Google apps and services. However, GAS is powerful, and you can do a lot more, such as creating add-ons, publishing web apps, and connecting to external databases.
If you want to learn more about Google Apps Script, one common suggestion is to learn JavaScript and follow the numerous tutorials available on the internet. Well, there is no doubt that the number of tutorials on the internet is huge and they can be overwhelming.
Therefore, we suggest you start with the following sources:
- Documentation on Apps Script by Google.
- Samples, codelabs, video tutorials, articles, and other learning resources for Apps Script by Google.
And here are some other free and useful resources:
- Eloquent JavaScript – ebook by Marijn Haverbeke; it’s free digitally.
- Apps Script Blastoff! – free introductory Apps Script course by Ben Collins.
- Stackoverflow entries for Google Apps Script – ask questions here.
- Apps Script open issues – post any issues (or enhancement requests) here.
Google Apps Script offers powerful customization if you’re comfortable with JavaScript. However, you may not have the time or resources to develop and maintain custom scripts. In that case, a ready-to-go solution will help. Use Coupler.io to connect Google Sheets to multiple data sources and avoid the learning curve of JavaScript programming or the limitations of script execution times.
Automate data import to and from Google Sheets with Coupler.io
Get started for free
