Google Sheets Save Triggers We re Sorry a Server Error Occured Please Wait a Bit and Try Again

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 ways you lot write small programs with Apps Script to extend the standard features of Google Workspace Apps. It'south great for filling in the gaps in your workflows.

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

Writing your start 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 total listing of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Let's write our commencement, extremely basic plan, the archetype "Hello world" program beloved of computer teaching departments the globe over.

Begin by creating a new Google Sheet.

Then click the menu: Extensions > Apps Script

This volition 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, between the curly braces after the role myFunction() 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 await like this:

Google Apps Script Authorisation

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

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

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

❗️When your commencement run your apps script, you may see the "app isn't verified" screen and warnings almost 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'south more information).

Click the "Advanced" button in the bottom left of the review permissions popular-up, and then click the "Go to Starter Script Code (unsafe)" at the bottom of the next screen to continue. And so blazon in the words "Continue" on the next screen, click Next, and finally review the permissions and click "ALLOW", equally shown in this paradigm (showing a different script in the quondam editor):

More data can be found in this detailed blog mail from Google Programmer Proficient Martin Hawksey.

Running a office in Apps Script

In one case you've authorized the Google App script, the function will run (or execute).

If anything goes incorrect with your code, this is the phase when y'all'd encounter a warning bulletin (instead of the yellowish message, you'll get a crimson box with an error message in it).

Render to your Google Sheet and you should see the output of your programme, a bulletin box popup with the classic "Hi earth!" message:

Click on Ok to dismiss.

Not bad job! Y'all've now written your outset apps script program.

Rename functions in Google Apps Script

Nosotros should rename our part to something more meaningful.

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

Then change your lawmaking in line ane from this:

part myFunction() {   Browser.msgBox("Hello Globe!"); }          

to this:

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

Annotation, it's convention in Apps Script to utilize the CamelCase naming convention, starting with a lowercase letter. Hence, we proper noun our function helloWorld, with a lowercase h at the commencement of hullo and an upper-case letter 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, non least because we can but run it from the script editor window and not from our spreadsheet.

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

This is really surprisingly easy to practice, requiring only a few lines of lawmaking. Add the following vi lines of code into the editor window, higher up the helloWorld() part we created above, equally shown here:

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

If you wait back at your spreadsheet tab in the browser now, zero will have changed. You lot won't have the custom menu there yet. We need to re-open our spreadsheet (refresh information technology) or run our onOpen() script first, for the bill of fare to bear witness upwardly.

To run onOpen() from the editor window, get-go select then run the onOpen part as shown in this image:

At present, when you return to your spreadsheet y'all'll see a new menu on the right side of the Assist option, called My Custom Card. Click on it and it'll open up to evidence a choice to run your Hello Globe program:

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 case, here's an invoice template Sheet with a RESET push button to clear out the contents:

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

Google Apps Script Examples

Macros in Google Sheets

Another great fashion to become started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you lot record so that yous can re-use them (for case applying standard formatting to a table). They use Apps Script under the hood so it'south a great style to become started.

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

Custom function using Google Apps Script

Let's create a custom function with Apps Script, and also demonstrate the utilize of the Maps Service. Nosotros'll exist creating a pocket-size custom function 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 go the distance, as follows:

The solution should be:

Copy the following code into the Apps Script editor window and relieve. First time, you lot'll demand 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) {   // become the directions   const directions = Maps.newDirectionFinder()      .setOrigin(start_point)      .setDestination(end_point)      .setMode(Maps.DirectionFinder.Mode.DRIVING)      .getDirections();      // get the first route and render the distance   const road = directions.routes[0];   const distance = route.legs[0].altitude.text;   return distance; }          

Saving data with Google Apps Script

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

Suppose I desire to save re-create of some information at periodic intervals, like so:

In this script, I've created a custom menu to run my master part. The master function, saveData(), copies the meridian row of my spreadsheet (the alive data) and pastes it to the next blank line beneath my electric current information range with the new timestamp, thereby "saving" a snapshot in time.

The code for this example is:

// custom carte du jour function function onOpen() {   const ui = SpreadsheetApp.getUi();   ui.createMenu('Custom Card')       .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 = canvass.getRange('Sheet1!B1').getValue();   const appointment = canvass.getRange('Sheet1!C1').getValue();   sheet.appendRow([url,follower_count,date]); }          

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

Google Apps Script example in Google Docs

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

Hither's 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:

one. Create a new Google Doc

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

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

iv. Copy in the following code:

// lawmaking to add the custom card 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('§§');    }          

v. You can change the special character in this line

cursor.insertText('§§');

to whatever yous desire information technology to be, eastward.g.

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

half dozen. Click Save and requite your script project a name (doesn't affect the running so telephone call information technology what y'all want e.g. Insert Symbol)

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

eight. Google volition recognize the script is not yet authorized and enquire 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 tin can see beneath):

10. Click Permit

11. Return to your Google Doc now.

12. You'll have a new menu choice, and so click on it:

My Custom Menu > Insert Symbol

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

Google Apps Script Tip: Use the Logger grade

Use the Logger form to output text letters to the log files, to aid debug code.

The log files are shown automatically later on the programme has finished running, or by going to the Executions menu in the left sidebar bill of fare options (the quaternary symbol, under the clock symbol).

The syntax in its most bones form is Logger.log(something in here). This records the value(due south) of variable(due south) at unlike steps of your program.

For example, 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 lot should see:

Real globe examples from my own piece of work

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

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

ane) A Sheets/web-app consisting of a custom web form that feeds data into a Google Canvas (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the information 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 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 condition and emails a summary screenshot every bit a PDF at the end of each day.

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

My own journeying into Google Apps Script

My friend Julian, from Measure School, interviewed me in May 2022 about my journeying 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:

Class

Documentation

Official Google Documentation

Google Workspace Developers Blog

Communities

Google Apps Script Group

Stack Overflow GAS questions

Elsewhere On The Internet

A huge large up-to-engagement list of Apps Script resources hosted on GitHub.

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

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

Related Manufactures

galganolemand.blogspot.com

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

Share this post