Lesson 4: Getting Started with Google Apps Script in Google Sheets

4.1 From the Browser to Google Sheets

So far, your code has run in this book's editors, where all it could do was display things. That's a good way to learn, but most real work involves data: lists of people, schedules, budgets, inventories. A lot of that data lives in spreadsheets.

Google Apps Script lets JavaScript work with that data. It's JavaScript, running on Google's servers, with extra tools for working with Google's apps: Sheets, Gmail, Calendar, Docs and Drive. Everything you've learned still applies. What's new is that your code can reach into a spreadsheet, read what's there, and change it.

Google Sheets is a good place to learn to program, for three reasons:

• You can see the results. When your code writes a value or changes a format, the change appears in the sheet in front of you.
• The structure is already there. Rows, columns and cells give your data a shape, so you don't have to design one.
• Everything is free. You need only a Google account, and nothing to install.

Here are a few of the things people build with Apps Script and Sheets. You'll build versions of several of them in this book:

• Tools that clean up data, such as standardizing names or dates
• Reports that summarize a sheet with one click
• Emails sent automatically, based on what's in a sheet
• Custom menus and buttons inside a spreadsheet
• Scripts that run on a schedule, or whenever a form is submitted

4.2 Opening the Apps Script Editor

Every Google Sheet can have its own Apps Script project attached to it. To open one:

1. Create a new spreadsheet by going to sheets.new in your browser. (You need to be signed in to a Google account.)
2. In the menu bar, click Extensions, then Apps Script.

The Apps Script editor opens in a new browser tab.

You could also have asked your AI assistant how to get there. Here's what happened when this question was sent in a new chat that started with the learner profile from Lesson 3. Your First Lines of Code:

These steps match the screen exactly. That won't always be true of instructions for using an app, whether they come from an assistant or from a web page. Google, Microsoft and other companies rearrange their menus from time to time, while older instructions stay online. Before the current menus, for example, the editor was under Tools, then Script editor, and you'll still find instructions that say so. An assistant can learn from those older pages too. So when an assistant tells you where to click, check each step against what's on your screen. If they don't match, tell the assistant what you actually see.

Look at the reply's last section, too. The assistant noticed that Apps Script has tools, such as SpreadsheetApp, that aren't on your "what I know so far" list, and it offered to explain them as new tools rather than assume you know them. It was reading your profile carefully. The profile still says your code runs in "an online editor that shows console.log output," though, and now you're working in Sheets. You'll update the profile to match at the end of this lesson.

4.3 A Tour of the Editor

When the editor opens, it looks like this:

The main parts are:

• Files, on the left. A new project has one file, Code.gs, where your code goes. (.gs stands for Google Script.) Larger projects can have several files.
• The code editor, in the middle, where you write code.
• The toolbar, above the code, with a Run button, a Debug button, and a menu that shows the name of a function (here, myFunction).
• The Execution log, which opens below the code when you run something. It shows what your code displayed with console.log, and any errors.

The project is attached to the spreadsheet you opened it from. That's what lets its code read and change that sheet. The project is saved automatically, and you can get back to it any time through Extensions, then Apps Script.

4.4 A First Look at Functions

Notice that the new project isn't empty. It already contains this:

function myFunction() {

}

This is a function: a named group of steps. Apps Script needs functions because it doesn't run loose lines of code the way this book's editors do. Instead, you choose a function by name, from the menu in the toolbar, and click Run. Apps Script then runs the steps inside that function, from top to bottom.

Here are the parts of a function:

• function is a keyword that tells JavaScript a function is starting.
• myFunction is the function's name. It's what appears in the toolbar's menu.
• ( ) is a pair of parentheses. They're empty for now. You'll see what can go in them in a later lesson.
• { } is a pair of curly braces. The steps of the function go between them, one per line.

You've already been using functions without knowing it. console.log is a function, and each time you wrote console.log("Hello"), you called it. You told it to run, and the parentheses carried the value it should display. Writing your own function is the other half of the story: you define the steps, and Apps Script calls it when you click Run.

A file can hold several functions. The menu in the toolbar lists them all, and Run runs whichever one is selected. That's handy, and it's also a common source of confusion: if you click Run and nothing seems to happen, check which function is selected.

Give your functions names that say what they do, such as writeGreeting or listUnpaidMembers, not myFunction. Like the names of values, function names can't contain spaces, so this book uses camelCase: the first word in lowercase, and each word after it starting with a capital letter.

4.5 Running Your First Script

Try it. Replace the empty function with this code:

function myFunction() {
  let count = 10
  console.log(count)
}

The lines inside the braces are indented by two spaces. JavaScript doesn't require that, but indenting the steps inside a function makes it easy to see where the function starts and ends.

Make sure myFunction is selected in the toolbar's menu, then click Run:

The Execution log opens below the code:

It has three entries. The first and last are notices from Apps Script saying when the function started and finished. The middle entry, marked Info, is the output of console.log(count): the value 10.

4.6 Writing to Your Sheet

Displaying a value in the log works just like it did in this book's editors. The new part is changing the spreadsheet itself. Here's a request for that, sent with the learner profile from the previous lesson:

The assistant didn't write the code, and that's the learner profile doing its job. The profile says to tell you when a task needs something you haven't learned, instead of using it. Writing to a sheet needs two new things, a function and Apps Script's commands for sheets, so the assistant named them and let you choose what to do next: learn about functions first, or see the code with extra comments.

This shows the other side of a learner profile: it has to keep up with you. You've already met functions in this lesson, but the profile doesn't know that. It still says your code runs in "an online editor" and lists nothing about Apps Script, so the assistant is right to hold back. At the end of this lesson you'll update the profile, and after that, requests like this one will get code.

First, though, here's the missing piece. This is the kind of line the assistant was describing:

SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Hello")

It's long, but it reads left to right, one step at a time. Each dot (.) means "and then, from what we have so far":

• SpreadsheetApp is Apps Script's tool for working with Google Sheets.
• .getActiveSheet() gets the sheet you're looking at, the one tab that's showing in the spreadsheet.
• .getRange("A1") gets cell A1 on that sheet. A range is one cell or a block of cells.
• .setValue("Hello") writes the value "Hello" into that cell.

Each of these steps is a method, an action that belongs to something, such as the spreadsheet tool, a sheet or a cell. You use a method by writing a dot after the thing it belongs to, then the method's name and parentheses. The parentheses work just as they do with console.log: they carry any values the method needs, such as which cell to get or what to write.

You don't need to memorize these. What matters is that you can read a line like this one and say what each part does. In the next lesson, you'll write the same steps on separate lines, which makes them easier to follow and check.

You can try the code right here. Below is a small practice sheet, and the editor under it can run Apps Script code against it, much as the real Apps Script editor runs code against your spreadsheet. Click Run, and watch cell A1:

{"sheetName": "Practice", "rows": 5, "columns": 4, "data": [], "formats": []}

function writeHello() {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Hello")
}

Now change "A1" to "B3", or change the message, and run it again.

To run it for real, paste the same function into your spreadsheet's Apps Script editor, choose writeHello in the toolbar's menu, and click Run. The first time, Apps Script asks for permission, as the next section describes.

4.7 Giving Permission

The first time you run code that works with your spreadsheet, Apps Script stops and asks for permission. That's a safety feature. Code can read, change and delete data, and someone could try to trick you into running code that does harm. So Google asks you to approve what a script can do before it runs.

Here's what you'll see. First, a message that the script needs authorization. Click Review permissions:

After you choose your Google account, you'll see a warning that "Google hasn't verified this app":

This warning looks alarming, so read it carefully. It says the app hasn't been verified by Google "until the developer" verifies it, and it names the developer's email address. The developer is you. You wrote the script, so the address shown is your own. Google shows this warning for any script that hasn't been through its review process, which is meant for apps shared with the public, not for code you wrote for yourself.

If the address shown isn't yours, stop. That means you're running someone else's script, and you should only continue if you know and trust them and understand what the code does.

To continue with your own script, click Advanced, then the link that says Go to Untitled project (unsafe). (The name changes if you've renamed your project.)

Finally, Google lists what the script will be allowed to do:

This is the screen that matters. Read the list and ask: does this match what I expect the code to do? Code that writes "Hello" into a cell needs to change a spreadsheet, so a permission to edit spreadsheets makes sense. A permission to send email or delete files from your Drive would not, and would be a reason to stop and find out why.

If the list matches what you expect, click Continue. Apps Script remembers your approval for this project, so you won't be asked again unless you add code that needs a different permission. Sometimes the script runs right after you approve it, and sometimes it doesn't. If you don't see the result, click Run again.

When you're not sure why code needs a permission, ask your assistant. This question was a follow-up in the same chat as the request above:

The main answer matches the permission screen: "see, edit, create, and delete" your spreadsheets. The reason is sound too. The code writes to a sheet, so it needs permission to edit, not just to view, and the approval step makes sure you're choosing to let it.

Now compare the rest with the screens you just went through. The assistant says you'll see buttons marked Allow or Cancel, but the real screen says Continue. It doesn't mention the "Google hasn't verified this app" warning at all, which is the most startling screen of the lot. It says you'll be asked again if you "edit the script significantly," when what matters is whether your changes need a different permission. And it doesn't point out that the permission covers all of your spreadsheets, not just the one this code writes to.

None of these slips would cause harm, and the explanation of why is good. But an assistant describing a screen it can't see is working from memory, and details drift. That's why you read the permission screen yourself.

4.8 Your Learner Profile

Your code now runs in a different place, so your learner profile needs to say so. You've also learned what a function is, and there's one new rule:

Here's what changed:

• Where your code runs. The first paragraph now says you're writing Apps Script in the editor attached to a Google Sheet. That tells the assistant which tools your code can use, such as SpreadsheetApp, and where to look for output.
• A new rule about functions. You know what a function is, but not yet how functions pass values to each other. Assistants sometimes split even a small task into several functions that hand values back and forth, which is harder to follow. This rule keeps all the code in one function you can run from the menu. You'll learn the rest in the lesson on functions, and that lesson will remove this rule.
• Four new items under "What I know so far," including reading a line of Apps Script like the one that writes Hello into cell A1. With this profile, the request for that code should now get an answer.

4.9 Summary

Google Apps Script is JavaScript with tools for working with Google's apps, and in this book you'll use it mostly with Google Sheets. Each spreadsheet can have an Apps Script project, which you open through Extensions, then Apps Script. Code in Apps Script goes inside functions, which are named groups of steps written as function name() { }. You run one by choosing it in the toolbar's menu and clicking Run, and its console.log output appears in the Execution log. The first time a script works with your data, Google asks you to approve its permissions. The "unverified app" warning names you as the developer, because you wrote the script. Approve permissions only when they match what you expect the code to do.