Google script functions

Google script functions DEFAULT

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.

What 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 , , 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 class
  • Google Docs can be accessed using class
  • Google Drive can be accessed using class
  • Gmail can be accessed using class
  • Language service can be accessed using class
  • And more

Apps Scripts also provides base classes. These allow you to access user info, such as email addresses and usernames, and also control script logs and dialog boxes. Some examples of base classes:

  • – provides access to dialog boxes specific to Google Sheets.
  • – allows the developer to write out a value to the execution logs.
  • – 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 class to access Google Sheets’s functionalities and the class to access Google’s language service.

Note: In February 2020, Google introduced the V8 runtime for Apps Script, which supports classes. This new 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). 

  • Opening the Google Apps Script editor

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.

  • Custom Google Apps Script string functions for translation

There is also a Code.gs file opened in the editor. It has a default function, which is blank, named . 

function myFunction() { }

How to add your first function

Delete the 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:

  • An example hello world script

The above function writes “Hello world!” to the spreadsheet. It demonstrates that, by using Apps Script, you can manipulate a spreadsheet. 

The code uses the class to get an object representing the active sheet using the method. Then, it gets a single cell with the address A1 using the method. Another method, , is then called with a string argument to write to A1

How to save your changes

Notice that there is 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 (). After that, the orange icon will be gone, and you’ll be able to run your script. 

How to run Google Apps Script

Click the Run button to execute your function. For the first time you run the script, you will need to authorize it to access your data.

  • Google Apps Script authorization - reviewing permissions

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.

  • Google Apps Script authorization - allowing access

Once authorized, the function will execute, and you’ll see “Hello world!” in A1:

  • Google Apps Script example - Sheets manipulation

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. 

  • Google Apps Script example - Google Docs manipulation

You may find it’s a bit inconvenient always having to 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 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 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 function in the dropdown, then click the Run button. 

  • A custom menu code within the onOpen() function

You’ll see “My Custom Menu” in your spreadsheet menu, as shown in the following screenshot:

  • A custom menu in Google spreadsheet

The 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 , , , , , and . 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 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. And 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 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. 

  • Creating a trigger using a script

Note: Do not run the 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:

  • The results after a few hours

You can see the list of triggers you created on the Triggers page, which can be opened from the left menu:

  • The Triggers menu

The Triggers page:

  • The Triggers page

How to create a trigger using UI

You can also create a trigger manually 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 = '[email protected]'; // change to yours GmailApp.sendEmail(to, 'Bitcoin prices', 'Attached prices in PDF', { attachments: [file], name: 'BitcoinPrices via AppsScript' }); }

Then, on the Triggerspage, click the Add Trigger button. A new pop-up will appear, allowing you to configure a new trigger.

  • Creating-a-new-trigger

To set a trigger that runs the every day, use the following configuration: 

  • Configuring the trigger

Explanations of the options above:

  • Choose which function to run: select 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 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 . 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: .  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 and press Enter. A nice thing here is that you can see the auto-complete shows the description of the new custom function:

  • A custom menu auto-complete

Copy the formula down, and you’ll see the following result:

  • A custom Google Apps Script numeric function

Notice that there’s an error in B5. That’s because is not a numeric value. Thus, using 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:

  • Custom-Google-Apps-Script-string-functions-for-translation

The functions seem to work well for a straightforward and non-idiomatic phrase. And you can use those functions to help you translate English words to 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, in A1. You’ll see a result like this:

  • A custom Google Apps Script date function

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 the IMPORTDATA in combination with the QUERY function to give you more options. 

Another alternative — you can 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: and . The second parameter () accepts column names, each concatenated with a comma, for example:

Example usage in a spreadsheet

In the following screenshot, you can see that only the columns specified in B2 are returned in the spreadsheet:

  • A custom Google Apps Script function for importing CSV data 
  • B1 cell contains the URL of a CSV file
http://samples.openweathermap.org/storage/history_bulk.csv?appid=b1b15e88fa797225412429c1c50c122a1
  • B2 cell contains the columns to import   
dt,dt_iso,city_name,temp_min,temp_max,weather_main,weather_description

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 functions 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; }
  • A simple custom function that converts Fahrenheit to Celcius

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 and get the desired outcome.

  • Custom function that returns a range of cells

Google Apps Script alternative: Coupler.io

Coding your own GAS function for importing data (as shown earlier with the 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, you may want to try Coupler.io. It’s an integration tool that allows you to import data from CSV and other popular data sources into Google Sheets. 

Coupler.io also offers more features than the IMPORTDATA function does. For a detailed comparison, you may want to check out this comparison table: IMPORTDATA vs. Coupler.io.

Here’s an example screenshot of selecting CSV data source in Coupler.io:

  • Coupler.io data source list

Simply copy and paste the CSV URL to import:

  • Coupler.io - Specifying the URL of a CSV file to import

Optionally, you can specify which fields to import and even skip some rows if you’d like to:

  • Coupler.io - Options to filter columns and skip rows

And more interestingly, you can set up an automatic data refresh on a schedule:

  • Coupler.io - Automatic data-refresh

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:  

And here are some other free and useful resources:

Happy learning and enjoy! 🙂

Back to Blog
Sours: https://blog.coupler.io/google-apps-script-tutorial/

Google Sheets Scripts 101: Easy Beginner’s Guide

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:

Allows You to Automate Stuff

Suppose 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 Create New Functions in Google Sheets

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 Apps

Since 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 Sheets

Apart 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 Editor

The script editor is the place where you can write scripts in Google Sheets 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.

Anatomy of the Google Sheets Script Editor

In Google Sheets, you can find the Script Editor in the Tools tab.

Google Sheets Script Editor in Tools tab

Once you click on the Script Editor option, it opens the Script Editor in a new window (as shown below).

Google Sheets Script Script Editor Opens in a New Window

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.

Google Sheets Script - Change the name of the Project

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.

Script File in Google Sheets

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.

Script File options

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.

Code Window in Google App Script

Script Editor Toolbar

Script Editor Toolbar in Google Sheets

The Script Editor Toolbar has the following options:

  1. Redo/Undo button: To redo/undo changes you have done in the script
  2. Indentation button: This is a toggle button and you can enable or disable indentation by clicking on it. When indentation is enabled, it automatically indents some parts of your script to make it more readable. This can be the case when you’re using loops or IF statements. It will automatically indent sets of codes within the loop to increase the readability (if indentation is enabled). This option is enabled by default and I recommend you keep it that way
  3. Save button: You can use this button to save any changes to your script. You can also use the keyboard shortcut Control + S. Note that unlike Google Sheets, you need to save your project to make sure the changes are not lost.
  4. Current Project Trigger’s button: When you click on this button, it will open the Triggers dashboard that lists all the triggers you have. A trigger is anything that triggers the running of code. For example, if you want a code to run and enter the current date and time in cell A1 whenever someone opens the Google Sheets, then you will use a trigger to do this.
  5. Run button: Use this to run the script. In case you have multiple functions, select any line in the one you want to run and then click on the Run button.
  6. Debug button: Debug helps you find errors in your code and also gives you some useful information. When you click on the Debug button, it also shows some additional debugging-related options in the toolbar.
  7. Select Function: This is a drop-down that would list all your functions in the script file. This is useful when you have many functions in the script and want to run a specific one. You can simply select the name from here and then click on the run button (or debug it if you want).

Google Script Editor Menu Options

Apart 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.

  • FILE: From the File menu, you can add a new project or a script file. A project would be a completely new project in a separate window where you can create more script files. When you add a new script file, it simply adds it to the same project (you’ll be able to see in the left pane below your current script files). You can also rename and delete projects from here. Another useful option you can find in the File menu is the ability to manage versions of the projects. When you save a project, a version of it is saved and you can go back and revisit that version if you want.
  • EDIT: Edit has some useful options that can help when you are writing or editing the code. For example, there is an option to find and replace text in your code. There are also options such as Word Completion, Content Assist, and Toggle comments.
  • VIEW: This has options that can be useful when you want to get more information about the script when it has executed or want to add logs to help in debugging in the future. For example, you can get the execution transcript, which details all the actions done by your script.
  • RUN: There are options to run different functions or debug them. Since these options are also available on the toolbar, it’s less likely to be used from the menu
  • PUBLISH: This has more advanced features such as publishing your scripts as web-apps.
  • RESOURCES: This gives you access to advanced options such as Libraries and Advanced Google Services. You can use these options to connect with other Google properties such as Google Forms or Docs.
  • HELP: This has tutorials and resources that can help you when you are getting started/working with Google Apps Scripts. One of the most useful options here is the link to the Documentation page where you can find a lot of guides and references to learn Google Apps Scripts

In this article, I have covered the basics of Google Apps Script and the overall anatomy of the interface. We hope this Google Script tutorial was useful for you.

You May Also Like the following tutorials:

Sours: https://spreadsheetpoint.com/google-sheets-script/
  1. Sig p226 history
  2. Anime makeup male
  3. Swedish disc golf
  4. Paper machine headbox

What is Google Apps Script?

Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight cloud-based applications.

What does this mean in practice?

It means you write small programs with Apps Script to extend the standard features of Google Workspace Apps. It’s great for filling in the gaps in your workflows.

For example, I used to be overwhelmed with feedback from my courses and couldn’t respond to everyone. Now, when a student submits their feedback, my script creates a draft email in Gmail ready for me to review. It includes all the feedback so I can read it within Gmail and respond immediately.

It made a previously impossible task manageable.

With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

Writing your first Google Script

In this Google Sheets script tutorial, we’re going to write a script that is bound to our Google Sheet. This is called a container-bound script.

(If you’re looking for more advanced examples and tutorials, check out the full list of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Let’s write our first, extremely basic program, the classic “Hello world” program beloved of computer teaching departments the world over.

Begin by creating a new Google Sheet.

Then click the menu to open a new tab with the code editor window.

This will open a new tab in your browser, which is the Google Apps Script editor window:

Google Apps Script Editor

By default, it’ll open with a single Google Script file () and a default code block, :

function myFunction() { }

In the code window, between the curly braces after the syntax, write the following line of code so you have this in your code window:

function myFunction() { Browser.msgBox("Hello World!"); }

Your code window should now look like this:

Hello World Apps Script

Google Apps Script Authorization

Google Scripts have robust security protections to reduce risk from unverified apps, so we go through the authorization workflow when we first authorize our own apps.

When you hit the run button for the first time, you will be prompted to authorize the app to run:

Google Apps Script Authorization

Clicking Review Permissions pops up another window in turn, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won’t be able to interact with your spreadsheet or do anything):

Google Apps Script Access

❗️When your first run your apps script, you may see the “app isn’t verified” screen and warnings about whether you want to continue.

In our case, since we are the creator of the app, we know it’s safe so we do want to continue. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, so we don’t need to submit it to Google for review (although if you want to do that, here’s more information).

Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to Starter Script Code (unsafe)” at the bottom of the next screen to continue. Then type in the words “Continue” on the next screen, click Next, and finally review the permissions and click “ALLOW”, as shown in this image (showing a different script in the old editor):

More information can be found in this detailed blog post from Google Developer Expert Martin Hawksey.

Running a function in Apps Script

Once you’ve authorized the Google App script, the function will run (or execute).

If anything goes wrong with your code, this is the stage when you’d see a warning message (instead of the yellow message, you’ll get a red box with an error message in it).

Return to your Google Sheet and you should see the output of your program, a message box popup with the classic “Hello world!” message:

Message Box Google Sheets

Click on Ok to dismiss.

Great job! You’ve now written your first apps script program.

Rename functions in Google Apps Script

We should rename our function to something more meaningful.

At present, it’s called myFunction which is the default, generic name generated by Google. Every time I want to call this function (i.e. run it to do something) I would write . This isn’t very descriptive, so let’s rename it to , which gives us some context.

So change your code in line 1 from this:

function myFunction() { Browser.msgBox("Hello World!"); }

to this:

function helloWorld() { Browser.msgBox("Hello World!"); }

Note, it’s convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our function , with a lowercase h at the start of hello and an uppercase W at the start of World.

Adding a custom menu in Google Apps Script

In its current form, our program is pretty useless for many reasons, not least because we can only run it from the script editor window and not from our spreadsheet.

Let’s fix that by adding a custom menu to the menu bar of our spreadsheet so a user can run the script within the spreadsheet without needing to open up the editor window.

This is actually surprisingly easy to do, requiring only a few lines of code. Add the following 6 lines of code into the editor window, above the function we created above, as shown here:

function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('My Custom Menu') .addItem('Say Hello', 'helloWorld') .addToUi(); } function helloWorld() { Browser.msgBox("Hello World!"); }

If you look back at your spreadsheet tab in the browser now, nothing will have changed. You won’t have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our script first, for the menu to show up.

To run from the editor window, first select then run the onOpen function as shown in this image:

Google Apps Script Function Menu

Now, when you return to your spreadsheet you’ll see a new menu on the right side of the Help option, called My Custom Menu. Click on it and it’ll open up to show a choice to run your Hello World program:

Custom menu

Run functions from buttons in Google Sheets

An alternative way to run Google Scripts from your Sheets is to bind the function to a button in your Sheet.

For example, here’s an invoice template Sheet with a RESET button to clear out the contents:

Button with apps script in google sheets

For more information on how to do this, have a look at this post: Add A Google Sheets Button To Run Scripts

Google Apps Script Examples

Macros in Google Sheets

Another great way to get started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you record so that you can re-use them (for example applying standard formatting to a table). They use Apps Script under the hood so it’s a great way to get started.

Read more: The Complete Guide to Simple Automation using Google Sheets Macros

Custom function using Google Apps Script

Let’s create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We’ll be creating a small custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.

The goal is to be able to have two place-names in our spreadsheet, and type the new function in a new cell to get the distance, as follows:

GAS custom function for maps

The solution should be:

GAS custom map function output

Copy the following code into the Apps Script editor window and save. First time, you’ll need to run the script once from the editor window and click “Allow” to ensure the script can interact with your spreadsheet.

function distanceBetweenPoints(start_point, end_point) { // get the directions const directions = Maps.newDirectionFinder() .setOrigin(start_point) .setDestination(end_point) .setMode(Maps.DirectionFinder.Mode.DRIVING) .getDirections(); // get the first route and return the distance const route = directions.routes[0]; const distance = route.legs[0].distance.text; return distance; }

Saving data with Google Apps Script

Let’s take a look at another simple use case for this Google Sheets Apps Script tutorial.

Here, I’ve setup an importxml function to extract the number of followers a specific social media channel has (e.g. in this case a Reddit channel), and I want to save copy of that number at periodic intervals, like so:

save data in google sheet

In this script, I’ve created a custom menu (as we did above) to run my main function. The main function, , copies the top row of my spreadsheet (the live data) and pastes it to the next blank line below my current data range as text, thereby “saving” a snapshot in time.

The code for this example is:

// custom menu function function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('Save Data','saveData') .addToUi(); } // function to save data function saveData() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0]; const url = sheet.getRange('Sheet1!A1').getValue(); const follower_count = sheet.getRange('Sheet1!B1').getValue(); const date = sheet.getRange('Sheet1!C1').getValue(); sheet.appendRow([url,follower_count,date]); }

See this post: Saving Data in Google Sheets, for a step-by-step guide to creating and running this script.

Google Apps Script example in Google Docs

Google Apps Script is by no means confined to Sheets only and can be accessed from other Google Workspace tools.

Here’s a quick example in Google Docs, showing a script that inserts a specific symbol wherever your cursor is:

Google Docs Apps Script

We do this using Google App Scripts as follows:

1. Create a new Google Doc

2. Open script editor from the menu:

3. In the newly opened Script tab, remove all of the boilerplate code (the “myFunction” code block)

4. Copy in the following code:

// code to add the custom menu function onOpen() { const ui = DocumentApp.getUi(); ui.createMenu('My Custom Menu') .addItem('Insert Symbol', 'insertSymbol') .addToUi(); }; // code to insert the symbol function insertSymbol() { // add symbol at the cursor position const cursor = DocumentApp.getActiveDocument().getCursor(); cursor.insertText('§§'); };

5. You can change the special character in this line

to whatever you want it to be, e.g.

6. Click Save and give your script project a name (doesn’t affect the running so call it what you want e.g. Insert Symbol)

7. Run the script for the first time by clicking on the menu:

8. Google will recognize the script is not yet authorized and ask you if you want to continue. Click Continue

9. Since this the first run of the script, Google Docs asks you to authorize the script (I called my script “test” which you can see below):

Docs Apps Script Auth

10. Click Allow

11. Return to your Google Doc now.

12. You’ll have a new menu option, so click on it:

13. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.

Google Apps Script Tip: Use the Logger class

Use the Logger class to output text messages to the log files, to help debug code.

The log files are shown automatically after the program has finished running, or by going to the Executions menu in the left sidebar menu options (the fourth symbol, under the clock symbol).

The syntax in its most basic form is . This records the value(s) of variable(s) at different steps of your program.

For example, add this script to a code file your editor window:

function logTimeRightNow() { const timestamp = new Date(); Logger.log(timestamp); }

Run the script in the editor window and you should see:

Google Apps Script Execution Logs

Real world examples from my own work

I’ve only scratched the surface of what’s possible using G.A.S. to extend the Google Apps experience.

Here’s a couple of interesting projects I’ve worked on:

1) A Sheets/web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it’s possible to perform data analysis, build dashboards showing data in real-time and share/collaborate with other users.

2) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each day.

Marketing dashboard using Google Apps Script

3) A marking template that can send scores/feedback to students via email and Slack, with a single click from within Google Sheets. Read more in this article: Save time with this custom Google Sheets, Slack & Email integration

Send data from Google Sheets to Slack

My own journey into Google Apps Script

My friend Julian, from Measure School, interviewed me in May 2017 about my journey into Apps Script and my thoughts on getting started:

Google Apps Script Resources

For further reading, I’ve created this list of resources for information and inspiration:

Course

Documentation

Official Google Documentation

Google Workspace Developers Blog

Communities

Google Apps Script Group

Stack Overflow GAS questions

Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!

Related Articles

Sours: https://www.benlcollins.com/apps-script/google-apps-script-beginner-guide/
Functions and Variables - Google Apps Script

Google Sheets is a powerful cloud-based spreadsheet tool that lets you do nearly everything you could do in Microsoft Excel. But the real power of Google Sheets is the Google Scripting feature that comes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); var row = 2; var col = 1; var data = sheet.getRange(row, col).getValue(); }

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

var results = data * 100; sheet.getRange(row, col+1).setValue(results); }

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues();

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

for (var i = 1; i < data.length; i++) { var result = data[i][0] * 100; sheet.getRange(i+1, 2).setValue(result);  } }

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); for (var i = 1; i<11; i++) { var result = i * 2; sheet.appendRow([i,result]); } }

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); var item, date, title, link, desc;  var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText(); var doc = Xml.parse(txt, false);   title = doc.getElement().getElement("channel").getElement("title").getText(); var items = doc.getElement().getElement("channel").getElements("item");    // Parsing single items in the RSS Feed for (var i in items) { item  = items[i]; title = item.getElement("title").getText(); link  = item.getElement("link").getText(); date  = item.getElement("pubDate").getText(); desc  = item.getElement("description").getText(); sheet.appendRow([title,link,date,desc]); } }

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Sours: https://helpdeskgeek.com/office-tips/5-google-sheets-script-functions-you-need-to-know/

Script functions google

Custom functions for Google Sheets using Apps Script

If you're familiar with Google Sheets, you've probably used functions such as SUM, AVERAGE, COUNT, etc. in formulas.

Using Apps Script, you can also write your own functions. These are called custom functions. Once you create these functions, you can use them in Sheets just like any other function.

A function is a piece of code that has a name. You can run (or execute) this piece of code elsewhere in your program by using its name. This enables reuse of code. Instead of typing the same code in multiple places, you can define a function in one place and then run it from other places in your program.

Functions in Apps Script have the following structure.

function functionName (inputValues) { return outputValue; }

The words and are called keywords.

Keywords and reserved words in Apps Script

In Apps Script, there are a few words that have a special meaning. These words are called keywords. You can see the full list of keywords here.

You don't need to memorize this list. Just remember that Apps Script places some restrictions on these words. For example, you cannot use a keyword as the name of a function.

Keywords are a subset of a larger set of words called reserved words. You also cannot use a reserved word as the name of a function.

The keyword function tells Apps Script that you are about to define a function. A function can accept input values and it can also return an output value. The return keyword tells Apps Script that you're about to return a value from the function. A function can only return a maximum of one value but you can have functions that don't return any value.

If the function accepts multiple input values, use a comma to separate them.

function functionName (inputValue1, inputValue2, inputValue3) { //The input values are processed to generate the output return outputValue; }

Note

The line in the above code that starts with is a comment. Apps Script ignores comments. They are used to help others understand your code.

Here is a diagram that visualizes what the function is doing.

function functionName (inputValue1, inputValue2, inputValue3) { }

Statements in Apps Script

A statement is a line of code in Apps Script that does something. Statements usually end with a semicolon. In the previous para, by "return statement", I meant the following line of code:

return outputValue;

There are functions that don't need input values. Similarly, you can have functions that don't return any value. It all depends on what the function does.

A function that does not accept any input value but returns an outputValue.

function functionName () { return outputValue; }

A function that neither accepts input values nor returns any value.

function functionName () { }

You must follow certain rules while naming your function.

  • Function names can only contain letters, numbers, the dollar sign ($) and underscores.

  • Function names cannot be a keyword.

  • Function names cannot start with a number.

Remember that function names in Apps Script are case-sensitive. So, MYFUNCTION is not the same as myFunction.

Here are some best practices for naming your function.

  • Use a descriptive name to help future readers of your code understand what the function does.

  • If the name is made up of several words, either use camel case or use underscores to separate the words. If you choose to separate the words using underscores, ensure that the words are in lower case.

You're going to write a function that returns a number that you believe will bring you luck. Let's start by documenting a few details.

  • What the function should do: It will return a number.

  • Function name: Pick a descriptive name (eg. myLuckyNumber).

  • Input values: This function does not need any input values.

  • Output value: A number (your lucky number).

I think the number 3 brings me luck so here is a function that returns my lucky number.

function myLuckyNumber () { return 3; }

Here is a screenshot of the above function in the Apps Script editor.

Screenshot of the Google Apps Script code editor.

You can now use this custom function in a Google Sheets formula just like any other function.

Screenshot of a Google Sheets spreadsheet.

You should see your lucky number displayed in the cell where you entered the formula.

Screenshot of a Google Sheets spreadsheet.

Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!

Sours: https://spreadsheet.dev/writing-custom-functions-for-google-sheets
Google Sheets: Working with Functions

So the evening ended, everyone passed out in the hall happy and devastated, a little later the nephew a couple of times alternately attached. Itself to Svetka, then to Yulka, and Oleg and I just watched how our girls stand next to cancer and have them, discussing who the nephew will end up with, after why went to bed.

In the morning, having breakfast, Yulka and Oleg began to get ready for the road, talking about yesterday, the cock was buzzing with such an orgy, we laughed remembering.

Similar news:

Igor, forgive me, I cannot, I said. You can't, he asked. I can't give you what you want so much, I whispered. Alcohol has already disappeared from my head, I began to ponder the situation.



1114 1115 1116 1117 1118