Server Error Occurred. Please Try Saving the Project Again.

What is Google Apps Script?

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

What does this mean in practice?

Information technology means you write minor programs with Apps Script to extend the standard features of Google Workspace Apps. It's bang-up 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 anybody. Now, when a student submits their feedback, my script creates a draft electronic mail in Gmail prepare for me to review. Information technology includes all the feedback so I can read it inside Gmail and respond immediately.

It made a previously impossible task manageable.

With Apps Script, you tin do cool stuff like automating repeatable tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services y'all use.

Writing your commencement 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 lot're looking for more avant-garde examples and tutorials, check out the total list of Apps Script articles on my homepage.)

How-do-you-do World in Google Apps Script

Let's write our first, extremely basic plan, the classic "Hello world" program beloved of computer teaching departments the earth over.

Brainstorm by creating a new Google Sheet.

And so click the menu Tools > Script editor... 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:

By default, it'll open with a single Google Script file (code.gs) and a default code block, myFunction():

function myFunction() {    }

In the lawmaking window, betwixt the curly braces after the role myFunction() syntax, write the following line of code then you accept this in your code window:

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

Your code window should at present expect like this:

Google Apps Script Authorization

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

When you striking the run push for the commencement time, you lot will be prompted to authorize the app to run:

Clicking Review Permissions pops up another window in turn, showing what permissions your app needs to run. In this example the app wants to view and manage your spreadsheets in Google Drive, and then click Let (otherwise your script won't exist able to collaborate with your spreadsheet or practice annihilation):

❗️When your first run your apps script, you lot may meet the "app isn't verified" screen and warnings about whether you want to continue.

In our example, since nosotros are the creator of the app, we know it'due south safe so we do want to go along. Furthermore, the apps script projects in this post are non intended to be published publicly for other users, so we don't need to submit information technology to Google for review (although if you lot want to exercise that, here's more data).

Click the "Advanced" push button in the bottom left of the review permissions popular-upward, and then click the "Get to Starter Script Code (unsafe)" at the bottom of the next screen to continue. So type in the words "Continue" on the next screen, click Side by side, and finally review the permissions and click "ALLOW", equally shown in this paradigm (showing a different script in the old editor):

More information can be found in this detailed blog mail from Google Programmer Expert Martin Hawksey.

Running a part in Apps Script

In one case you lot've authorized the Google App script, the office volition run (or execute).

If anything goes wrong with your code, this is the stage when you lot'd see a alarm bulletin (instead of the yellow message, y'all'll get a red box with an fault message in it).

Return to your Google Sheet and you should run into the output of your program, a bulletin box popup with the classic "Howdy world!" message:

Click on Ok to dismiss.

Great job! You've at present written your showtime apps script program.

Rename functions in Google Apps Script

Nosotros should rename our function to something more than meaningful.

At present, information technology'south called myFunction which is the default, generic proper name generated by Google. Every time I want to phone call this function (i.e. run it to do something) I would write myFunction(). This isn't very descriptive, so allow'due south rename it to helloWorld(), which gives us some context.

And so change your lawmaking in line i from this:

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

to this:

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

Note, it's convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our part helloWorld, with a lowercase h at the start of howdy and an upper-case letter West at the start of World.

Calculation a custom menu in Google Apps Script

In its electric current grade, our program is pretty useless for many reasons, not to the lowest degree because we tin can only run information technology from the script editor window and not from our spreadsheet.

Permit'due south fix that past adding a custom menu to the menu bar of our spreadsheet so a user tin can run the script within the spreadsheet without needing to open up the editor window.

This is really surprisingly easy to do, requiring just a few lines of code. Add the following half-dozen lines of code into the editor window, in a higher place the helloWorld() part we created above, every bit shown here:

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

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

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

Now, when y'all return to your spreadsheet you'll see a new menu on the right side of the Help option, chosen My Custom Card. Click on it and it'll open up to testify a selection to run your Hullo Earth program:

Run functions from buttons in Google Sheets

An culling fashion to run Google Scripts from your Sheets is to bind the part to a button in your Canvas.

For instance, here's an invoice template Sheet with a RESET push button to clear out the contents:

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

Google Apps Script Examples

Macros in Google Sheets

Some other bully way to get started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that yous tape so that yous tin re-employ them (for case applying standard formatting to a tabular array). They utilise Apps Script under the hood and so it's a great mode to get started.

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

Custom function using Google Apps Script

Permit's create a custom office with Apps Script, and too 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 exist able to accept two identify-names in our spreadsheet, and blazon the new function in a new cell to get the distance, as follows:

The solution should be:

Re-create the post-obit code into the Apps Script editor window and save. First time, you lot'll need to run the script in one case from the editor window and click "Let" to ensure the script tin collaborate with your spreadsheet.

function distanceBetweenPoints(start_point, end_point) {   // go 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 altitude   const route = directions.routes[0];   const altitude = route.legs[0].distance.text;   return distance; }

Saving data with Google Apps Script

Let's take a wait at another unproblematic apply case for this Google Sheets Apps Script tutorial.

Suppose I want to save copy of some data at periodic intervals, similar so:

In this script, I've created a custom menu to run my main function. The main function, saveData(), copies the top row of my spreadsheet (the live data) and pastes information technology to the next bare line below my current data range with the new timestamp, thereby "saving" a snapshot in time.

The code for this case is:

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

Run across this mail service: How To Save Information In Google Sheets With Timestamps Using Apps Script, for a step-by-footstep guide to create and run this script.

Google Apps Script case in Google Docs

Google Apps Script is by no means bars 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:

Nosotros practise this using Google App Scripts every bit follows:

1. Create a new Google Doctor

2. Open script editor from the menu: Tools > Script editor...

3. In the newly opened Script tab, remove all of the boilerplate code (the "myFunction" lawmaking cake)

4. Copy in the following code:

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

v. Y'all can change the special character in this line

cursor.insertText('§§');

to whatever you lot want it to be, eastward.g.

cursor.insertText('( ͡° ͜ʖ ͡°)');

6. Click Save and requite your script projection a proper name (doesn't affect the running so phone call it what yous want e.grand. Insert Symbol)

vii. Run the script for the first time by clicking on the menu: Run > onOpen

8. Google volition recognize the script is non yet authorized and ask you lot if yous want to continue. Click Proceed

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

ten. Click Let

11. Return to your Google Doc now.

12. You'll have a new card option, so click on it:
My Custom Menu > Insert Symbol

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

Google Apps Script Tip: Apply the Logger course

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

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, nether the clock symbol).

The syntax in its most basic grade is Logger.log(something in here). This records the value(s) of variable(southward) at different steps of your program.

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

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

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

Real world examples from my own work

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

Hither's a couple of interesting projects I've worked on:

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

ii) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website condition and emails a summary screenshot every bit a PDF at the cease of each day.

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 than in this article: Save time with this custom Google Sheets, Slack & Email integration

My own journey into Google Apps Script

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

Google Apps Script Resources

For farther 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 Grouping

Stack Overflow GAS questions

Elsewhere On The Net

A huge large upward-to-date list of Apps Script resources hosted on GitHub.

For general Javascript questions, I recommend this JavaScript tutorial page from W3 Schools when y'all're starting out.
​
When you're more comfy with Javascript nuts, then I recommend the comprehensive JavaScript documentation from Mozilla.

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

Related Manufactures

smithlecests.blogspot.com

Source: https://www.benlcollins.com/apps-script/google-apps-script-beginner-guide/

Share this post