Google Sheets Cannot Read Property "Source" From Undefined.

What is Google Apps Script?

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

What does this hateful 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 exist overwhelmed with feedback from my courses and couldn't respond to everyone. At present, when a student submits their feedback, my script creates a typhoon 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 lot can practice cool stuff similar automating repeatable tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

Writing your commencement Google Script

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

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

Hello Globe in Google Apps Script

Let's write our first, extremely bones program, the classic "Hello world" programme beloved of computer pedagogy departments the earth over.

Begin by creating a new Google Sheet.

Then click the card Tools > Script editor... to open a new tab with the code editor window.

This will open up 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 (lawmaking.gs) and a default code cake, myFunction():

part myFunction() {    }

In the code window, betwixt the curly braces after the office myFunction() syntax, write the following line of code so yous have this in your code window:

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

Your lawmaking window should now look like this:

Google Apps Script Authorisation

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

When you hit the run push button for the commencement fourth dimension, you 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 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 exercise anything):

❗️When your showtime run your apps script, you may see the "app isn't verified" screen and warnings about whether y'all desire to continue.

In our case, since we are the creator of the app, nosotros know information technology's safe and so nosotros do want to continue. Furthermore, the apps script projects in this mail service 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 exercise that, here's more data).

Click the "Advanced" button in the lesser left of the review permissions pop-up, then click the "Become to Starter Script Code (unsafe)" at the bottom of the next screen to go on. Then type in the words "Go along" on the next screen, click Next, and finally review the permissions and click "Permit", as shown in this image (showing a dissimilar script in the old editor):

More than information can be institute 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 part 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 yellowish message, yous'll get a red box with an error message in information technology).

Return to your Google Sail and you should see the output of your program, a message box popup with the archetype "Hello world!" message:

Click on Ok to dismiss.

Great job! You've at present written your outset apps script plan.

Rename functions in Google Apps Script

Nosotros should rename our function to something more meaningful.

At present, it's chosen myFunction which is the default, generic name generated past Google. Every time I want to call this role (i.e. run it to practise something) I would write myFunction(). This isn't very descriptive, so allow's rename it to helloWorld(), which gives united states of america some context.

So change your code in line 1 from this:

role myFunction() {   Browser.msgBox("Howdy Earth!"); }

to this:

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

Note, information technology'south convention in Apps Script to utilize the CamelCase naming convention, starting with a lowercase letter. Hence, nosotros name our office helloWorld, with a lowercase h at the kickoff of hello and an uppercase W at the starting time of World.

Adding a custom menu in Google Apps Script

In its current grade, our program is pretty useless for many reasons, not least considering we can only run information technology from the script editor window and non from our spreadsheet.

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

This is actually surprisingly easy to do, requiring only a few lines of lawmaking. Add the following 6 lines of code into the editor window, higher up the helloWorld() function nosotros created higher up, as shown hither:

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

If you expect back at your spreadsheet tab in the browser now, naught 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 show upwards.

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

Now, when y'all return to your spreadsheet you'll see a new bill of fare on the correct side of the Assist option, called My Custom Menu. Click on it and information technology'll open up to show a choice to run your Hullo Globe program:

Run functions from buttons in Google Sheets

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

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

For more information on how to practice 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 way to get started with Google Scripts is by using Macros. Macros are pocket-size programs in your Google Sheets that you record so that you lot tin can re-apply them (for example applying standard formatting to a table). They use Apps Script nether the hood and then it's a great way to get started.

Read more: The Consummate 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 pocket-size custom function that calculates the driving altitude 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 blazon the new office in a new cell to get the altitude, as follows:

The solution should exist:

Re-create the following code into the Apps Script editor window and save. Commencement time, y'all'll demand to run the script one time 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 starting time route and return the distance   const route = directions.routes[0];   const altitude = route.legs[0].distance.text;   return distance; }

Saving data with Google Apps Script

Let'south take a await at another simple use case for this Google Sheets Apps Script tutorial.

Hither, 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:

In this script, I've created a custom carte du jour (every bit nosotros did above) to run my main function. The chief function, saveData(), copies the acme row of my spreadsheet (the live data) and pastes it to the next blank line below my electric current data range as text, thereby "saving" a snapshot in fourth dimension.

The code for this example is:

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

Encounter this mail service: Saving Data in Google Sheets, for a stride-by-footstep guide to creating and running this script.

Google Apps Script example 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:

We do this using Google App Scripts equally follows:

1. Create a new Google Md

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

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

iv. 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(); };  // lawmaking 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

cursor.insertText('§§');

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

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

half-dozen. Click Salvage and give your script project a proper name (doesn't affect the running so call it what you desire e.thou. Insert Symbol)

7. Run the script for the showtime fourth dimension past clicking on the menu: Run > onOpen

8. Google volition recognize the script is not yet authorized and inquire you if yous want to continue. Click Continue

ix. Since this the first run of the script, Google Docs asks you to authorize the script (I chosen my script "examination" which y'all can see beneath):

10. Click Allow

xi. Return to your Google Physician now.

12. You'll accept a new carte 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: Use the Logger form

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

The log files are shown automatically afterward 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 bones grade is Logger.log(something in here). This records the value(s) of variable(s) at different steps of your program.

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

part logTimeRightNow() {   const timestamp = new Engagement();   Logger.log(timestamp); }

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

Real world examples from my own work

I've only scratched the surface of what's possible using Yard.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 re-create of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it'south possible to perform information analysis, build dashboards showing data in existent-time and share/interact with other users.

2) A dashboard that connects to a Google Analytics business relationship, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each twenty-four hours.

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 fourth dimension with this custom Google Sheets, Slack & Email integration

My own 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 data and inspiration:

Class

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 yous tin can get with GAS. I hope yous experience inspired to try extending your Sheets and Docs and automate those ho-hum, repetitive tasks!

Related Articles

bergletwo1938.blogspot.com

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

Share this post