Server Error Occurred Please Try Saving the Project Again Line 0

What is Google Apps Script?

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

It means yous write modest programs with Apps Script to extend the standard features of Google Workspace Apps. It'south keen for filling in the gaps in your workflows.

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

Writing your first Google Script

In this Google Sheets script tutorial, we're going to write a script that is spring to our Google Canvass. This is chosen a container-bound script.

(If y'all're looking for more advanced examples and tutorials, check out the full listing of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Permit'southward write our first, extremely bones plan, the archetype "Howdy world" plan honey of calculator teaching departments the world over.

Begin by creating a new Google Sheet.

And then click the carte du jour: Extensions > Apps Script

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

Past default, it'll open up with a single Google Script file (lawmaking.gs) and a default lawmaking cake, myFunction():

function myFunction() {    }          

In the code window, betwixt the curly braces after the function myFunction() syntax, write the post-obit line of code so you accept this in your code window:

part myFunction() {   Browser.msgBox("How-do-you-do World!"); }          

Your code window should now look like this:

Google Apps Script Authorization

Google Scripts take robust security protections to reduce gamble from unverified apps, so we get through the authority workflow when nosotros first authorize our ain apps.

When you hit the run button for the beginning time, you will exist 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 instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won't be able to collaborate with your spreadsheet or do anything):

❗️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 prophylactic so we do want to go on. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, then nosotros don't demand to submit it to Google for review (although if y'all desire to practice that, here'due south more than data).

Click the "Advanced" push button in the bottom left of the review permissions pop-upwardly, and so click the "Go to Starter Script Lawmaking (dangerous)" at the bottom of the side by side screen to go on. Then type in the words "Keep" on the adjacent screen, click Next, and finally review the permissions and click "ALLOW", equally shown in this prototype (showing a different script in the erstwhile editor):

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

Running a role in Apps Script

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

If anything goes wrong with your code, this is the stage when yous'd run into a alert message (instead of the yellow bulletin, you'll go a red box with an error message in it).

Return to your Google Sheet and you should meet the output of your program, a message box popup with the classic "How-do-you-do world!" bulletin:

Click on Ok to dismiss.

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

Now, it'due south called myFunction which is the default, generic proper noun generated past Google. Every time I want to call this function (i.due east. run information technology to exercise something) I would write myFunction(). This isn't very descriptive, so let'southward rename it to helloWorld(), which gives u.s. some context.

So alter your code in line 1 from this:

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

to this:

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

Note, it'due south convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter of the alphabet. Hence, nosotros name our function helloWorld, with a lowercase h at the kickoff of hello and an uppercase W at the start of World.

Adding a custom bill of fare in Google Apps Script

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

Let's gear up that by adding a custom menu to the menu bar of our spreadsheet and then a user can run the script within the spreadsheet without needing to open the editor window.

This is actually surprisingly easy to do, requiring only a few lines of code. Add the following half-dozen lines of lawmaking into the editor window, above the helloWorld() function we created above, equally 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 yous await back at your spreadsheet tab in the browser now, nothing will have inverse. You won't have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our onOpen() script first, for the menu to prove upwardly.

To run onOpen() from the editor window, kickoff select and then run the onOpen function as shown in this paradigm:

Now, when y'all return to your spreadsheet you'll run across a new menu on the right side of the Assistance selection, called My Custom Bill of fare. Click on information technology and it'll open to bear witness a choice to run your Hello Earth program:

Run functions from buttons in Google Sheets

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

For instance, hither's an invoice template Canvas 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 A Google Sheets Button To Run Scripts

Google Apps Script Examples

Macros in Google Sheets

Another keen fashion to go started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you tape and then that you tin re-use them (for example applying standard formatting to a tabular array). They employ Apps Script under the hood and so it'south a keen way to go started.

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

Custom function using Google Apps Script

Let's create a custom part with Apps Script, and besides demonstrate the use of the Maps Service. We'll be creating a minor custom role that calculates the driving distance betwixt two points, based on Google Maps Service driving estimates.

The goal is to exist able to have two place-names in our spreadsheet, and type the new function in a new jail cell to get the altitude, every bit follows:

The solution should be:

Copy the following lawmaking into the Apps Script editor window and salvage. Offset fourth dimension, you'll demand to run the script in one case from the editor window and click "Allow" to ensure the script can interact with your spreadsheet.

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

Saving data with Google Apps Script

Let'due south have a look at another simple use case for this Google Sheets Apps Script tutorial.

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

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

The code for this instance is:

// custom menu office role onOpen() {   const ui = SpreadsheetApp.getUi();   ui.createMenu('Custom Menu')       .addItem('Salve Data','saveData')       .addToUi(); }  // function to save information 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 appointment = canvas.getRange('Sheet1!C1').getValue();   sheet.appendRow([url,follower_count,date]); }          

See this post: How To Relieve Data In Google Sheets With Timestamps Using Apps Script, for a pace-by-pace guide to create and run this script.

Google Apps Script example in Google Docs

Google Apps Script is past no ways confined to Sheets simply and can be accessed from other Google Workspace tools.

Hither'due south a quick example in Google Docs, showing a script that inserts a specific symbol wherever your cursor is:

We do this using Google App Scripts as follows:

1. Create a new Google Doc

ii. Open script editor from the menu: Extensions > Apps Script

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

4. Copy in the post-obit code:

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

5. You can modify the special character in this line

cursor.insertText('§§');

to any yous want it to be, due east.g.

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

6. Click Salvage and give your script project a name (doesn't bear upon the running so call it what you want eastward.g. Insert Symbol)

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

8. Google will recognize the script is not even so authorized and ask you if you desire to continue. Click Continue

9. Since this the starting time run of the script, Google Docs asks you to qualify the script (I chosen my script "test" which you can meet below):

10. Click Allow

eleven. Return to your Google Doc now.

12. You lot'll take a new menu pick, so click on it:

My Custom Menu > Insert Symbol

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

Google Apps Script Tip: Use the Logger course

Employ the Logger form to output text messages to the log files, to aid debug code.

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

The syntax in its nearly basic form is Logger.log(something in here). This records the value(s) of variable(due south) at different steps of your plan.

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

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

Run the script in the editor window and you should run into:

Real world examples from my ain piece of work

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

Here are a couple of interesting projects I've worked on:

i) A Sheets/spider web-app consisting of a custom web form that feeds information into a Google Sheet (including uploading images to Bulldoze and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails information technology to the users. And with all the information in a master Google Sail, it'south possible to perform information 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 every bit a PDF at the end of each day.

3) A marking template that tin can send scores/feedback to students via email and Slack, with a unmarried click from inside Google Sheets. Read more in this commodity: Save time with this custom Google Sheets, Slack & E-mail integration

My ain journey into Google Apps Script

My friend Julian, from Mensurate Schoolhouse, interviewed me in May 2017 about my journeying into Apps Script and my thoughts on getting started:

Google Apps Script Resource

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

Elsewhere On The Internet

A huge big up-to-date list of Apps Script resource hosted on GitHub.

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

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

Related Articles

cruzmonsuldn1960.blogspot.com

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

Share this post