Meet Apps Script

A tour of the editor

Open a new Google Sheet, then choose Extensions → Apps Script. A new tab opens with:

Code lives in functions

A function is a named block of code. The editor only runs functions, never loose lines, so every script you write in this course is a function. You choose which one runs from the dropdown.

function greet() {
  const name = 'Ploy';
  console.log('Hello, ' + name);
}

function today() {
  const now = new Date();
  console.log('Today is ' + now.toDateString());
}

Hello, Ploy

Choose today in the dropdown and press Run again, and you get today's date instead.

Next: your first script →

Step 1: print a message

1. Delete the empty myFunction that the editor gives you.
2. Paste the code below and press Ctrl + S.
3. Check the dropdown shows hello, then press ▶ Run.

function hello() {
  console.log('Hello, Apps Script!');
}

10:15:02 AM   Notice   Execution started
10:15:02 AM   Info     Hello, Apps Script!
10:15:03 AM   Notice   Execution completed

console.log writes a line into the log. You will use it all the time to check what your code is doing. The older Logger.log also works, but it prints numbers as 5.0, so this course uses console.log.

Step 2: write into the sheet

Now the script changes the Sheet it is bound to. Add this function under hello, save, select writeHello and run.

function writeHello() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A1').setValue('Hello from Apps Script');
  sheet.getRange('A2').setValue(new Date());
}

A1  Hello from Apps Script
A2  (today's date and time)

Read it from left to right, the way the computer does:

• SpreadsheetApp is the Sheets service.
• .getActiveSheet() picks the tab that is open.
• .getRange('A1') picks one cell, and .setValue(…) puts something in it.

Each dot asks the thing on its left for something new. This "chain" style is how all of Apps Script reads.

Step 3: make a whole new file

function makeDoc() {
  const doc = DocumentApp.create('My first scripted doc');
  doc.getBody().appendParagraph('This document was made by a script.');
  console.log(doc.getUrl());
}

https://docs.google.com/document/d/1AbC…/edit

Open the link from the log. The new document is in your Drive with the sentence already typed in. A script made it in about a second.

Next: the permission screen →

"Authorization required"

The first time a script touches your data, Google stops and asks. A script runs as you, with your access to your files and email, so you have to agree first. You see this once per project, and again if you add code that needs something new (Gmail, for example).

1. Click Review permissions and choose your account.
2. For your own script you may see "Google hasn't verified this app". Click Advanced, then Go to (your project name) (unsafe). "Unsafe" only means Google hasn't reviewed it. You wrote it, so you know what it does.
3. Read the list of what the script can do, then click Allow. The script runs.

Scopes: what the list means

Each line on the screen is a scope, a permission. Apps Script works them out by reading your code:

To remove a script's access later, go to your Google Account → Security → Your connections to third-party apps & services.

Next: practice →

Practice

Do these in a Sheet-bound project. Try each one before you open the solution.

function aboutMe() {
  console.log('My name is Ploy');
  console.log('I am in M.5');
  console.log('My favourite app is Google Sheets');
}

function writeName() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('B2').setValue('Ploy');
  sheet.getRange('C2').setValue('M.5/3');
}

function sayHi() {
  SpreadsheetApp.getActiveSpreadsheet().toast('Hi from my script!', 'Apps Script', 5);
}

Next: quiz →

Check yourself