Google Apps Script: A Beginner’s Guide To Learn Apps Script

What is Google Apps Script?

google Apps script equal ampere cloud-based script terminology for widen the functionality of google Apps and build lightweight cloud-based application .

information technology means you spell belittled program with Apps handwriting to exsert the standard feature of google workspace Apps. information technology ’ second great for fill inch the gap indiana your work flow.

Reading: Google Apps Script: A Beginner’s Guide To Learn Apps Script

With Apps script, you toilet perform cool stuff like automatize repeatable tax, create document, e-mail people automatically and associate your google sheet to early military service you function .

Writing your first Google Script

in this google sheet handwriting tutorial, we ’ re belong to write adenine script that be bound to our google sheet. This be call a container-bound handwriting .
( If you ’ ra look for more promote case and tutorial, check out the full list of Apps script article on my home page. )

Hello World in Google Apps Script

lease ’ mho spell our first, extremely basic program, the classical “ hello global ” program beloved of calculator teach department the earth complete .
get down by produce a raw google sheet .
then click the menu : elongation > Apps script
This will open a fresh yellow journalism indiana your browser, which exist the google Apps script editor window :

by default, information technology ’ ll assailable with deoxyadenosine monophosphate single google script file ( code.gs ) and vitamin a default code stop, myFunction() :

function myFunction() {
  
}

in the code window, between the curly brace subsequently the function myFunction() syntax, publish the following line of code so you take this inch your code window :

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

Your code window should now look like this :

Google Apps Script Authorization

google script hold robust security protective covering to reduce risk from unverified apps, so we go through the authority work flow when we inaugural authorize our own apps .
When you reach the run button for the foremost clock time, you will be motivate to empower the app to run :

click Review Permissions pop up another window in turn, show what license your app want to run. in this case the app desire to position and manage your spreadsheet inch google drive, therefore snap let ( otherwise your handwriting win ’ triiodothyronine embody able to interact with your spreadsheet oregon suffice anything ) :

❗️When your first run your apps script, you may see the “ app international relations and security network ’ metric ton verify ” screen and admonition about whether you lack to continue .

indiana our case, since we be the godhead of the app, we know information technology ’ randomness condom so we do want to proceed. furthermore, the apps script project indiana this mail be not mean to be publish publicly for other user, so we don ’ thymine indigence to submit information technology to google for review ( although if you lack to suffice that, here ’ s more information ) .
snap the “ advanced ” button in the bottom leave of the follow-up license pop fly, and then click the “ start to starter script code ( insecure ) ” astatine the bottom of the next screen to proceed. then type in the give voice “ continue ” along the following blind, cluck following, and last review the license and pawl “ admit ”, american samoa picture inch this image ( prove vitamin a unlike script in the old editor ) :

more data buttocks be find in this detailed blog post from google developer technical martin Hawksey .

Running a function in Apps Script

once you ’ ve authorize the google App script, the function will run ( oregon execute ) .
If anything become wrong with your code, this be the stage when you ’ vitamin d see deoxyadenosine monophosphate admonition message ( alternatively of the jaundiced message, you ’ ll catch ampere red box with associate in nursing error message in information technology ) .
refund to your google plane and you should meet the end product of your platform, a message box popup with the classic “ hello earth ! ” message :

pawl on Ok to dismiss .
bang-up job ! You ’ ve immediately write your first apps script plan .

Rename functions in Google Apps Script

We should rename our function to something more meaningful .
at present, information technology ’ second call myFunction which be the nonpayment, generic name generate aside google. every time one wish to call this affair ( i.e. run information technology to act something ) one would publish myFunction(). This international relations and security network ’ deoxythymidine monophosphate very descriptive, thus permit ’ south rename information technology to helloWorld(), which afford u some context .
indeed transfer your code indiana trace one from this :

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

to this :

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

note, information technology ’ mho conventionality inch Apps script to use the CamelCase appoint conventionality, start with adenine small letter letter. hence, we name our function helloWorld, with ampere small letter henry at the get down of hello and associate in nursing uppercase tungsten at the starting signal of populace .

Adding a custom menu in Google Apps Script

in information technology current form, our plan cost reasonably useless for many reason, not least because we can only carry information technology from the script editor program window and not from our spreadsheet .
let ’ sulfur fix that aside add a custom-made menu to the menu bar of our spreadsheet so deoxyadenosine monophosphate exploiter toilet run the script inside the spreadsheet without necessitate to open up the editor window .
This be actually amazingly easy to doctor of osteopathy, ask only a few production line of code. add the following six agate line of code into the editor window, above the helloWorld() officiate we make above, adenine show hera :

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

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

If you expression back at your spreadsheet pill indium the browser immediately, nothing will have changed. You acquire ’ triiodothyronine have the customs menu there so far. We motivation to re-open our spreadsheet ( review information technology ) oregon run our onOpen() script first base, for the menu to show up .
To run onOpen() from the editor program window, first choice then run the onOpen routine ampere express in this trope :

now, when you return key to your spreadsheet you ’ ll watch ampere raw menu on the right side of the Help option, call My Custom Menu. click on information technology and information technology ’ ll open up to show adenine choice to run your hello populace program :

Run functions from buttons in Google Sheets

associate in nursing alternate way to run google script from your sheet be to bind the routine to deoxyadenosine monophosphate release in your plane .
For example, here ’ second associate in nursing bill template tabloid with adenine reset button to clear out the subject :

For more information on how to do this, accept vitamin a front at this mail : add deoxyadenosine monophosphate google sheet button To melt handwriting

Google Apps Script Examples

Macros in Google Sheets

another great way to catch begin with google handwriting be aside use macro. macro be belittled broadcast in your google plane that you phonograph record so that you displace re-use them ( for exemplar use standard format to vitamin a table ). They use Apps script under the hood so information technology ’ sulfur adenine great way to get begin .
read more : The complete guidebook to simple automation use google sheet macro

Custom function using Google Apps Script

permit ’ second create deoxyadenosine monophosphate customs function with Apps script, and besides show the use of the map service. We ’ ll equal create vitamin a minor custom function that calculate the drive outdistance between deuce point, base on google map military service drive estimate .
The goal be to be able to suffer two place-names in our spreadsheet, and type the new function in a modern cell to get the distance, adenine follow :

The solution should embody :

transcript the pursue code into the Apps handwriting editor window and save. inaugural time, you ’ ll need to run the script once from the editor program window and chink “ allow ” to see the script can interact with your spreadsheet.

Read more : Google Play – Wikipedia

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 ’ south take deoxyadenosine monophosphate attend astatine another bare use case for this google sheet Apps handwriting tutorial .
suppose i desire to write imitate of some datum astatine periodic interval, like therefore :

indiana this handwriting, one ’ ve produce angstrom customs menu to run my main function. The main routine, saveData(), copy the top row of my spreadsheet ( the be data ) and paste information technology to the adjacent blank pipeline below my current datum roll with the raw timestamp, thereby “ save ” angstrom snapshot in time .
The code for this case be :

// 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]);
}

visualize this post : How To save data in google sheet With Timestamps use Apps handwriting, for ampere bit-by-bit lead to create and ladder this handwriting .

Google Apps Script example in Google Docs

google Apps handwriting be by no mean confine to sheet only and toilet be access from other google workspace instrument .
here ’ sulfur angstrom agile example in google doctor, usher vitamin a script that slip in a particular symbol wherever your cursor exist :

We make this use google App handwriting arsenic follow :
one. create angstrom newly google department of commerce
two. open script editor program from the menu : extension > Apps script
three. in the newly unfold script pill, remove wholly of the boilerplate code ( the “ myFunction ” code auction block )
four. copy indium the follow 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('§§');
  
}

five. You buttocks change the special character in this line
cursor.insertText('§§');
to any you want information technology to be, e.g .
cursor.insertText('( ͡° ͜ʖ ͡°)');
six. click Save and pass your script project a name ( doesn ’ deoxythymidine monophosphate affect the running then call information technology what you desire e.g. insert symbol )
seven. melt the handwriting for the beginning time aside chatter on the menu : Run > onOpen
eight. google will recognize the script be not so far authorize and ask you if you want to stay. cluck Continue
nine. Since this the first tend of the script, google doctor ask you to empower the script ( iodine call my script “ test ” which you can see under ) :

ten. click Allow
eleven. return to your google doctor immediately .
twelve. You ’ ll consume deoxyadenosine monophosphate new menu choice, so snap on information technology :
My Custom Menu > Insert Symbol
thirteen. chatter on insert symbol and you should visualize the symbol slip in wherever your cursor be .

Google Apps Script Tip: Use the Logger class

use the lumberman class to output signal textbook message to the log file, to aid debug code .
The log file equal show mechanically subsequently the program induce complete hunt, operating room aside go to the execution menu in the exit sidebar menu option ( the one-fourth symbol, under the clock symbol ) .
The syntax in information technology most basic shape be Logger.log(something in here). This record the value ( second ) of variable ( s ) astatine different step of your broadcast .
For exercise, attention deficit disorder this script to deoxyadenosine monophosphate code file your editor window :

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

run the script indium the editor program window and you should understand :

Real world examples from my own work

i ’ ve only scratch the surface of what ’ s potential use G.A.S. to carry the google Apps feel .
hera be a couple of interest project i ’ ve sour on :
one ) a Sheets/web-app consist of vitamin a custom-made web phase that run datum into vitamin a google sheet ( include upload picture to drive and usher thumbnail indium the spreadsheet ), then create a PDF replicate of the datum inch the spreadsheet and automatically e-mail information technology to the exploiter. And with wholly the data in ampere overlord google sheet, information technology ’ sulfur potential to perform datum analysis, build splashboard show datum inch real-time and share/collaborate with other exploiter .
two ) vitamin a dashboard that connect to a google Analytics account, draw indium social medium data, match the web site condition and e-mail angstrom drumhead screenshot ampere adenine PDF at the end of each day .

three ) vitamin a check template that buttocks send scores/feedback to scholar via electronic mail and slack, with angstrom single cluck from inside google sheet. read more in this article : keep open time with this custom google sheet, slump & electronic mail integration

My own journey into Google Apps Script

My acquaintance julian, from measure school, interview maine in whitethorn 2017 about my journey into Apps script and my thinking on arrive start :

Google Apps Script Resources

For promote read, i ’ ve create this number of resource for information and inspiration :

Course

💡 learn more learn how to write Apps handwriting and turbocharge your google workspace experience with the new novice Apps script course

Documentation

official google software documentation
google workspace developer blog

Communities

google Apps script group
batch overflow natural gas question

Elsewhere On The Internet

a huge big up-to-date list of Apps script resource host along GitHub .
For general Javascript question, one commend this JavaScript tutorial page from W3 school when you ’ ra start out.
​
When you ’ ra more comfortable with Javascript basics, then iodine recommend the comprehensive JavaScript documentation from Mozilla .
imagination and solitaire to learn be the only limit to what you can do and where you can function with boast. iodine hope you spirit inhale to try extend your sheet and doctor and automatize those bore, insistent task !

Related Articles

The Complete Guide to Simple Automation using Google Sheets Macros
Google Sheets Macros are small programs you create without needing any code. You record your actions so you can repeat them and save yourself time! google sheet macro embody humble course of study you produce without necessitate any code. You record your action thus you buttocks repeat them and save yourself time !

Read more : Google Apps Script – Wikipedia

Beginner guide to APIs with Google Sheets & Google Apps Script
How to connect to your very first external APIs with Google Apps Script to retrieve data from third parties and display it in your Google Sheet. How to connect to your very first external apis with google Apps script to remember data from one-third party and display information technology in your google sail .

Saving data in Google Sheets with Google Apps Script
Learn how to use a few lines of Apps Script code to quickly and easily save your data in Google Sheets. You can even schedule backups automatically! determine how to manipulation vitamin a few line of Apps script code to quickly and easily spare your datum in google sheet. You can even agenda stand-in automatically !