Google Apps Script for Educators

Go from beginner to developer in the Google Apps Script development environment, and start coding solutions from scratch
0.0 (0 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
16 students enrolled
$50
Take This Course
  • Lectures 25
  • Contents Video: 3 hours
    Other: 8 mins
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 6/2015 English

Course Description

This course is designed to teach educators how to get started coding in Google Apps Script. Other courses may teach you how to code, but this course is set out with teachers in mind, and every task is relevant to the work we do every day.

Google Apps for Education is a powerful tool for educators, both in teaching and in optimizing their own workflow. Learning to code in Google Apps Script will let you start to build customized solutions to problems you face in your professional practice. This course will take you step-by-step through the process of creating an automated grade book spreadsheet that will collect all your spreadsheets of graded form responses, and put all the information in one place. And the best part is that you'll finish the course with an understanding of the code that makes the magic happen!

What are the requirements?

  • Participants will require a Google Apps (Gmail) account. A Google Apps for Education account is recommended
  • Experience using the Google Apps tools (docs, spreadsheets)

What am I going to get from this course?

  • Understand Apps Script architecture
  • Demonstrate programmatic and systems-design thinking
  • Build a custom add-on to accomplish a task
  • Troubleshoot code written by other developers

What is the target audience?

  • Educators who are interested in developing solutions to automate tasks in the Google Apps environment
  • Teachers who want to begin using Google Apps Script as a platform for teaching programming to students

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Spreadsheet Logic
01:35

Welcome to Apps Script for Educators. This is a short video introduction to the course.

03:35

Recommended Prior Knowledge:

  1. Familiarity with basic spreadsheet formulas, such as =SUM(), =AVERAGE(), etc.
  2. Understanding of different data formats available in a spreadsheet, such as number, text, date, etc.
Learning Outcomes:

After completing this lesson, students will be able to create logical statements within a spreadsheet that will test conditions, count values that meet a criteria, and expand to match the size of a changing data set.

Required Materials:
  1. An active Google Apps account. Students may use a regular Gmail account or a Google Apps for Education Account.
  2. A copy of the lesson spreadsheet, available from this link.
    https://docs.google.com/a/amplifiedit.com/spreadsheets/d/1UrDtXZWBsoKVB-u9ANjB23ncLUz9y-UlEbpOWknppvQ/copy

Part 1: Conditional Formatting

Conditional formatting is a feature of Google Sheets that will allow you to take an action on cells that meet a set criteria. The actions you can take are to change the background color or the text color in the matching cells, giving you the ability to quickly highlight information that you want to see.

While conditional formatting does not affect the actual contents of the cell, it is a good place to start building your understanding of logic conditions and taking actions based on the output. Data validation is a tool that will ensure your spreadsheet cells contain the type of information you're expecting, which is an important consideration when applying logical statements and conditional formatting.

Vocabulary
  • Data: Information that is organized in a systematic way. Generally, the contents of a spreadsheet that is used to contain information can be considered to be data.
  • Logic Condition: A statement that can be either “true" or “false," but nothing else. Also known as a “boolean"
  • Data Type: The type of content contained in a spreadsheet cell. For example, text, integers, dates, percents, and times are types of data.

Instructions

To begin with, ensure you've made a copy of the Lesson 1 Spreadsheet. You will see tabs at the bottom of the spreadsheet. To begin conditionally formatting a spreadsheet, you need to have some data contained in the spreadsheet. The Lesson 1 spreadsheet contains multiple “sheets," or tabs that separate data sets. For the conditional formatting section of this lesson, we will be working on the “Raw Data" sheet. Once you have made a copy of the spreadsheet, watch the video lectures and follow along in your own spreadsheet to learn about spreadsheet logic.
04:24

Logic can be combined using AND and OR statements. The output of these logical operators can be visualized using what's called a “truth table,” which lists the output for all the possible input combinations.

This lesson will take you through building simple combined logical operators, and show you how to create a simple truth table for two inputs. Both the AND and OR formulas can take more than two inputs, letting you create powerful logical statements that combine multiple input conditions.

04:55

Now that we understand logic, we can start using the conditions to give certain output. Instead of having either “TRUE” or “FALSE,” we can use an IF statement to determine the output of a cell based on a logical input.

If statements can also take the result of an AND or an OR as an argument (input). Because AND and OR are logical statements that evaluate to either true or false, our IF statement will accept the result as valid input and use it to determine the output.

04:40

Spreadsheets are wonderful tools for dealing with large amounts of information. You can use the menus to sort and filter information, but you can also do this using spreadsheet formulas. The advantage of using a formula is that as the information changes, you do not need to sort or filter manually: everything will happen automatically. After all, that's the point of learning to write code!

08:45

Once you have a filtered, sorted set of data, you can use a formula to perform a lookup, where the spreadsheet will look for a match, and return it when it's found. It's important to use a sorted range, since the lookup formula depends on information being sorted alphabetically (or numerically). By using a sort formula, we can ensure that the table of information will always be sorted, even if it changes.


A lookup is useful for one cell, but it can be tedious to work with expanding sets of data. What happens when your data isn't all packaged up neatly, but instead can change over time? We can use an array formula to account for expanding data by working dynamically. That is, we can ensure that the formulas will automatically expand down to the last row in the cell, even if we add to the data.
10:25

Instead of copying formulas down manually, we can use an additional formula called arrayformula. This formula will wrap around the instructions we have already written, and will expand to handle all the rows in our spreadsheet with one single cell of input.

Using an array formula is usually the final step in assembling a complex nested formula. The idea is to make sure that everything works properly in one row first, then use the array formula command to automatically fill all the rows underneath with the same instructions, moving the cell references as necessary. This works especially well with form responses and open-ended ranges. An array formula will apply to each new row of data as it comes in from a Google Form without requiring any work on your part.


One important part of using the array formula with some of the nested formulas we've already learned (for example, using a vlookup) is that once the array formula expands down to an empty row, it will return an error. We can handle this with another wrapping formula. The end result looks very long and complicated, but building it up piece by piece helps us understand each component, what it does, and how it contributes to the final resulting behavior.
Spreadsheet Logic Quiz
8 questions
Section 2: Custom Formulas
05:12

Google Sheets is a robust spreadsheet tool, and the functionality can be augmented through Google Apps Script. By using the script editor to define a set of instructions, users can create custom formulas to accomplish a task without using complicated formulas that are difficult to decode.

Vocabulary

  • Log: A virtual “scratch pad” where your code can write notes which you can view. This is a very useful tool for debugging code (that is, understanding exactly what your code is doing).
  • Function: a block of code that performs a defined operation.
  • String: A data type that is human-readable text.
  • Open or Close Bracket: a parenthesis that groups some code together.
    • { }: Used to enclose a block of code. This defines the start and end of a set of instructions.
    • ( ): Used to enclose a series of variables or parameters. After a function declaration, it is important to use the syntax () to tell Apps Script that the function does not have any arguments.
05:30

In this lesson, we'll go through some of the tutorials Google provides for getting started with your own custom functions. You can see the full tutorial at https://developers.google.com/apps-script/guides/sheets/functions which has some helpful documentation.

Vocabulary

  • Input: Information that is passed to the custom function as an argument.
  • Argument: Also known as parameters, arguments are the name of the variables used to store input and pass it into a function.
  • Equals Symbol (=): an assignment operator. It takes the right-hand side, and stores it in the variable on the left side of the equals symbol. For example, to store the value 4 in a variable name “number” you would enter this code:
    var number = 4;
  • Return: The output of the function which will be “returned” to the spreadsheet.
  • Comments: Human-readable text that is ignored by the machine when executing code.
    • Line Comment: A comment that only occupies a single line. Begins with a double forward-slash //
    • Block Comment: A block of one or more lines of comments. The comment block is opened with a forward-slash and an asterisk /* and is close with an asterisk and forward slash */. Everything between these two pieces of code will be a comment, and thus ignored by the machine.

Code

/**

* Multiplies the input value by 2.

*

* @param {number} input The value to multiply.

* @return The input multiplied by 2.

* @customfunction

*/

function DOUBLE(input) {

var result = input*2;

return result;

}

07:00

When using custom functions, we can take more than one argument (or parameter) as input. This makes our custom functions especially powerful, since we can start working with multiple pieces of information at once.

We may also need to check the value of our parameters before we perform any operations. Just like in lesson 1, we can use an if statement to perform our check. However, unlike in spreadsheet operations, they're a lot more simple to use in code.

If statements have a specific syntax (just like every other piece of code). Make sure your if statement includes each of the pieces necessary, or it won't work as expected.

  1. After the if statement, you need to enclose the code to be executed when the condition is true in brace brackets. This defines a block of code. If the condition is false, your code will skip over all of the commands in this code block.
  2. You don't need to include an else statement, but if you do, you also need brace brackets to define the second code block. These instructions will only be performed if your condition is false.
  3. Your condition should evaluate clearly to either true or false. It's possible to use if statements in other cases, but that's a concept for a later lesson. We'll get to it, I promise.
Vocabulary
  • Argument: An input parameter for a function.
  • Parameter: Another name for an argument.
  • If Statement: A piece of code that will test a condition, and execute a set of commands if the condition is true. The condition must be a boolean statement, that is, a condition that evaluates to either true or false.

Code

if (condition) {

// Do these commands if the condition is true

} else {

// Do these commands if the condition is false

}

07:56

When we want to perform an operation on a set of cells, we need to use what's called an array. You can think of an array as a simple list of variables (each called an element). Each variable within the array an address where it can be access, known as the index. You can work with the array as a whole, which has properties and methods associated with it, or with individual elements within the array. The individual elements will have their own properties and methods, depending on the type of information stored in that element (known as a variable type).

Vocabulary

  • Array: A set of variables grouped together into a single object. Each variable within the array is known as an element.
  • Element: An individual variable that's stored somewhere in the array. Each element has an associated data type. It's important to know the type of information stored within the element, since the type determines the methods and properties that can be accessed.
  • Index: The address of an element within an array. Array indices start at 0, so the first element in the array is located at index 0. This can be confusing, so it's important to keep this in mind!
  • Property: A characteristic that's associated with an object. Arrays have some useful properties, such as length. Properties are accessed by typing the variable name, a dot, then the property name without an opening and closing bracket.
    • Length: A property of an array. This is 1 indexed, so an array's length property will always return a value that is one greater than the index of the last element contained in the array.
  • Method: A function that can be run (or “called”) by an object. Methods are "called" (executed) by typing the variable name, a dot, and the name of the method followed by an open and close bracket.
    • Join: A method used to join all the elements of an array together into a string. This can be used with a “delimiter,” which will separate each of the initial elements in the returned string.
  • Delimiter: A value used to separate things.
Code

function myFunction() {

var array = ['First', 'Second','Third','Fourth','Fifth'];

Logger.log(array.length);

array[0] = 'This is the new first element.';

Logger.log(array);

var emptyArray = [];

emptyArray = ['not empty anymore'];

Logger.log(emptyArray);

var arrayString = array.join(' ');

Logger.log(arrayString);

}

05:53

Loops are a great way of working with all the elements in an array, one at a time. A loop will execute a block of code over and over, and we can keep track of how many times we've looped through. This comes in handy, since a loop that starts at zero and executes for every element in an array is an easy way to do the same operation on every element. This lesson will introduce the “for” loop, which is an easy structure to understand, and automatically gives us our counter variable.

For Loop Structure

The syntax of a for loop follows a specific pattern. It can be thought of in the following manner:

for (declare a counter variable and set initial value; logical statement that will evaluate true for all iterations of the loop; amount to increment in each loop) {

code block of instructions to execute;

important to include the counter variable somewhere in here;

}

Code

function myFunction() {

var array = [50,80,20,77,32,56,99,78,86,94];

var sum = 0;

for (var i = 0; i < array.length; i++) {

sum += array[i];

Logger.log(array[i]);

}

Logger.log('Sum is ' + sum);

var average = sum / array.length;

Logger.log(average);

}

08:15

Coding is as much about understanding what your code is doing as it is about giving instructions. Something as simple as a misplaced comma can cause unexpected behavior, so having a way to look at what the code is doing while it's running is invaluable. Google Apps Script offers the ability to use the “debugger” to pause the code at various points;

Vocabulary
  • Debugger: The tool used to run code and pause at various points, either automatically when an error is encountered, or at a defined “debug point.”
  • Pause: Pausing the program during execution, allowing the code author to view the status of the program, including variables, functions that have been called, and values of the data associated with the program.
  • Error: When the code encounters a problem that prevents the successful operation of the given instructions.
  • Debug Point: A user-defined point in the code to pause the execution when run with the “debug” option.
  • Stack: the memory used to store the functions necessary to the current point of operation of the code. It can be thought of as similar to a stack of physical objects, where each relies on the preceding it.
Code

function myFunction() {

var array = [50,80,20,77,32,56,99,78,86,94];

var sum = 0;

for (var i = 0; i < array.length; i++) {

sum += array[i];

Logger.log(array[i]);

}

Logger.log('Sum is ' + sum);

var average = sum / array.length;

Logger.log(average);

}

05:59

When working with custom formulas, it's important to think carefully about the type of information that a function returns. Returning information can also be a useful way to debug custom formulas, since the debugger will not run from a spreadsheet formula itself.

Additionally, the information returned to the spreadsheet cell offers the ability to provide users with feedback. For example, it can be helpful to prompt users to input data in a specific format (using the autocomplete JDoc commenting we learned earlier), but there will be times where users make a mistake. Returning an error message that contains more information can help a user quickly understand and correct any mistakes.


We also need to make sure that our code is working with the data type it expects. JavaScript (and, by extension, Google Apps Script) has handy functions built in that will extract numbers from a string. Using these parse functions, our code will be able to handle various data types by converting them before performing the operations we need.
07:58

For a custom function that accepts a range as a parameter, we'll need to understand how sheets send that information to our code. A range of values is passed as what is known as a two-dimensional array. While it sounds scary, you can think of it as a list of lists - imagine a fridge with lists of groceries to buy, chores to do, and so on. A 2-D array is similar: it can keep track of many things all in the same variable.


Working with a 2-D array means that we'll need to use some new strategies. Fortunately, it's a pretty simple thing once you understand what we're working with, since we now know how to handle a regular array using loops. If you put one loop inside another, you can handle 2-D arrays with only a few extra lines of code.


Syntax

Declaring a two-dimensional array is done in a similar fashion to declaring an array with only one dimension. The difference is the number of opening and closing square brackets. Here are some examples:

  • A two dimensional array with multiple rows:
    • var array = [['row 1 element 1', 'row 1 element 2'], ['row 2 element 1', 'row 2 element 2']];
  • A two dimensional array with a single row:
    • var array = [['row 1 element 1', 'row 1 element 2']];
  • An empty two dimensional array:
    • var array = [[]];
11:45

The big day has arrived: it's finally time to create a custom spreadsheet formula that will perform a useful task! Using everything we've learned, we can put together a custom formula to calculate a weighted average mark. There is already a weight formula in sheets, but it will only accept one value for each parameter. We can do better - our script will work with horizontal ranges of marks and weights, and do everything for us in one simple formula.

The code can even handle typical user errors, such as mismatched range lengths, and weights that do not add up to 100. With a little math (don't worry, it's not much), we can even manage to accommodate data that's given as whole numbers or percentage, all in the same custom formula. Isn't that better than trying to do it all manually every time?


Syntax

A simple for loop to iterate through every element in an array has a “shorthand” declaration. This operates in the same manner as a fully described for loop that starts at zero and iterates through every element in an array.

for (var i in array) {

// Loop code block

}

is equivalent to

for (var i = 0; i < array.length; i++) {

// Loop code block

}


Code

/**

* Calculates a weighted average, given a horizontal array of marks and a horizontal array of weights

*

* @param {Horizontal Range} marks A horizontal range of marks to be weighted and averaged.

* @param {Horizontal Range} weights A horizontal range of weights to be applied. Must sum to 100%

* @return Calculated weighted average mark.

* @customfunction

*/

function WEIGHTED_AVERAGE(marks, weights) {

//marks = [['86','78','80','67','90']];

//weights = [['30','20','10','15','25']];

marks = marks[0];

weights = weights[0];

if (marks.length != weights.length) {

return 'ERR: Mismatched Range Length';

}

var sum = 0;

for (i in weights) {

sum += parseFloat(weights[i]);

}

if (sum != 1 && sum != 100) {

return 'ERR: Weight Sum Error ';

}

var weightedAvg = 0;

for (var i = 0; i < marks.length; i++) {

if (weights[i] == 0) {

continue;

}

var currMark = parseFloat(marks[i]);

if (currMark < 1) {

weightedAvg += currMark * parseFloat(weights[i]);

} else {

weightedAvg += currMark / 100 * parseFloat(weights[i]);

}

}

return weightedAvg / sum;

}

4 pages

This is the source code for all lesson 2 modules.

Section 3: Loops
02:46

We have used the for loop in our code, but there are a few different ways to use it. We know the basic constructor (the syntax used to define a for loop), now we go deeper into how they can be used. In certain cases, it may be easier to use a different shorthand declaration.

In the case where you want a loop to begin with the first element of an array and iterate through all elements, we can use the for… in… declaration. In this example, our array (let's declare the variable array) can have as many elements as we want, and the loop will execute for every element within the array.

for (var i in array) {

// code block

}

We can also skip defining our counter variable. This will have an impact on our code in two ways. First, the variable won't persist (remain accessible by the code) once our loop has finished iterating. This can be useful if we're using many for loops in our code (and you will probably end up with quite a few), since we can use the counter only where we need it. It's important to know that in this case, the type of the counter variable isn't reliable

for (i in array) {

// code block

}

03:20

There are times when it's necessary to skip an iteration (that is, have the code not execute for an iteration, but instead continue with the next iteration). It is possible to do this using an if statement to wrap the entire block of code in the loop, but this solution can get messy, especially if there are multiple conditions where it would be necessary to continue looping, while at the same time skipping over one iteration.

To do this, we use the continue function. This will instruct the program to skip the remainder of the code in the loop for the current iteration, and proceed with the next iteration. We used this in our code to skip calculating a weighted average for any assignment that was weighted at zero, but this can come in handy in many cases.

The complement to the continue function is break. Instead of continuing with the loop, a break will instruct the code to exit the loop and continue the program. One example of when it would be valuable to use a break would be when searching through an array of values for a specific value. Once the value is found, it is unnecessary to continue with the search performed within the loop, and the code can “break out” of the loop. Breaks are used in other ways throughout programming, but always for the same purpose: to stop executing the sequence of instructions, and resume running the program after the last line of the current block of code.
08:52

While loops differ from for loops in one major way: they will continue to iterate until a condition is met. This can be useful when you want to loop, but you're not sure how many iterations you will need. The while loop checks a condition, and if it is true, it will iterate. Once it finishes an iteration, it will check the condition again, and proceed. Once the condition is false, the loop will end.

Syntax

While (boolean logic statement) {

// code block

}

Another loop we can use is called a do-while loop. This type of loop will execute the block of code once, then check a condition. If the condition is true, the loop will iterate; if it's false, the loop will exit. Using a loop of this form in combination with a boolean variable (a boolean variable is either true or false) can be a good way to loop through a set of instructions until a condition is met. Using a do-while loop in combination with an if statement is only one of the possible applications.

We will be using do-while loops more when we've connected to some of the other core services, such as the drive app. For now, it's enough to understand the structure of the loop with an example.

Syntax

Do {

// code block

} while (boolean logic statement

)

4 pages

This is all the source code from module 3: loops.

Section 4: Drive App
14:05

Until this point, we have only worked directly within the spreadsheet where our script resides. This is very useful for manipulating information that exists within the spreadsheet, but there is a lot more that we can do within Google Apps Script. We can connect to other services through Google Apps Script by calling up their “app.” In this lesson, we will use the DriveApp to connect to our Google Drive and find files.

When using the Drive App, it is critical to pay attention to the return type of the method we're using. Many times the return type will be an iterator - that is, a javascript object that functions similar to an array. In the same way that a two dimensional array is an array of arrays, an iterator can be thought of as an object containing multiple objects. Don't worry if this sounds confusing - we will work through it line-by-line so that you can get a handle on how to manage using iterator objects.

Code

function navigateDrive() {

var files = DriveApp.getFiles();

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var fileArray = [['File Name','File ID', 'Link']];

var i = 0;

while (files.hasNext() && i < 100) {

var currentFile = files.next();

var fileName = currentFile.getName();

var fileId = currentFile.getId();

var fileLink = currentFile.getUrl();

fileArray.push([fileName, fileId, fileLink]);

i++;

}

var continuationToken = files.getContinuationToken();

sheet.getRange(1,1,fileArray.length,3).setValues(fileArray);

while (continuationToken) {

continuationToken = keepGoing(continuationToken);

}

Logger.log('debug point');

}

function keepGoing(continuationToken) {

var files = DriveApp.continueFileIterator(continuationToken);

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var fileArray = [];

var i = 0;

while (files.hasNext() && i < 100) {

var currentFile = files.next();

fileArray.push([currentFile.getName(), currentFile.getId(), currentFile.getUrl()]);

i++;

}

sheet.getRange(sheet.getLastRow() + 1,1,fileArray.length,3).setValues(fileArray);

return files.getContinuationToken();

}

08:51

In the last lesson, you might have been surprised by how many files you can access through your Google Drive. In some cases, there might be more files than can be process in the six minute maximum run time that Google Apps Script allows. In order to handle operations that take longer than we're allowed to run at one time, we can store information for use later.This is done through writing to the script's properties, using the Properties Service. Script properties are pieces of information stored that relate to the script which created them. They are stored in what's called a key-value pair: the key is used to identify the property, and the value is the stored property itself. This lets us give our properties meaningful names, and work with values that may change over time.

13:41

With our properties stored for use at a later time, we can now think about how to run our code. Up to this point, we have been running the code through a direct instruction. This can be thought of as manually “triggering” the code to run. We can also set up triggers to run our code automatically through a simple user interface, or by writing a few lines of code.

There are different events that can trigger a script to run. Document-based triggers are fired when something happens with the document containing the script. For example, you can run some code whenever the document is opened by using the onOpen simple trigger.

You can also set up triggers based on a time or date. These are known as clock-based triggers, and will run independently of you being in the development environment. By using triggers, we can build applications that will run without any need for user input. From simple instructions to add a custom menu to our spreadsheet, all the way up to running an audit of all the files you can access through Google Drive, we can combine loops, properties, if statements and triggers to handle large, complicated tasks without the need to “babysit” the code.

Code:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('DriveApp').addItem('Begin data collection','navigateDrive')

.addItem('Continue collection','keepGoing').addToUi();

}

function getTriggers() {

var triggerArray = ScriptApp.getProjectTriggers();

for (i in triggerArray) {

Logger.log('%s type trigger to execute %s function',triggerArray[i].getEventType(),triggerArray[i].getHandlerFunction());

}

}

function deleteTriggers() {

var triggerArray = ScriptApp.getProjectTriggers();

for (i in triggerArray) {

ScriptApp.deleteTrigger(triggerArray[i]);

}

}

function createTrigger() {

var newTrigger = ScriptApp.newTrigger('lastChanged').forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onChange().create();

PropertiesService.getScriptProperties().setProperty('changeTrigger',newTrigger.getUniqueId());

}

function lastChanged() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var cell = sheet.getRange(1,4);

cell.setValue(new Date());

}

function navigateDrive() {

var files = DriveApp.getFiles();

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var fileArray = [['File Name','File ID', 'Link']];

var i = 0;

while (files.hasNext() && i < 1000) {

var currentFile = files.next();

var fileName = currentFile.getName();

var fileId = currentFile.getId();

var fileLink = currentFile.getUrl();

fileArray.push([fileName, fileId, fileLink]);

i++;

}

var continuationToken = files.getContinuationToken();

sheet.getRange(1,1,fileArray.length,3).setValues(fileArray);

PropertiesService.getScriptProperties().setProperty('token', files.getContinuationToken());

Logger.log('debug point');

}

function keepGoing() {

var continuationToken = PropertiesService.getScriptProperties().getProperty('token');

var files = DriveApp.continueFileIterator(continuationToken);

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var fileArray = [];

var i = 0;

while (files.hasNext() && i < 10000) {

var currentFile = files.next();

fileArray.push([currentFile.getName(), currentFile.getId(), currentFile.getUrl()]);

i++;

}

sheet.getRange(sheet.getLastRow() + 1,1,fileArray.length,3).setValues(fileArray);

PropertiesService.getScriptProperties().setProperty('token', files.getContinuationToken());

if (!files.getContinuationToken()) {

deleteTriggers();

}

//return files.getContinuationToken();

}

function storeToken(token) {

//token = 25;

PropertiesService.getScriptProperties().setProperty('token', token);

var properties = PropertiesService.getScriptProperties().getProperties();

Logger.log('debug point');

}

function getToken() {

var token = PropertiesService.getScriptProperties().getProperty('token');

Logger.log(token);

}

18:05

Instead of listing every file we can access through drive, we can find files using different criteria. It is possible to filter to only those items we own, items of a specific type, or items located in a specific folder. We can use a loop to find files that match our criteria in a given folder, and use recursion to loop through all subfolders, looking through each for matching items.

Vocabulary:

  • Recursion: an action which repeats itself as necessary. It is a useful strategy for building code that can iterate through any number of times, taking action as necessary.
  • Enum: A special data type that incorporates a predefined set of properties, methods, and variables. Google Apps Script services and apps may contain ENUMs, which can be used to test an object against to determine its type.


Code

function demoLoop() {

var folder = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next();

var allSheets = recursiveLoop(folder);

Logger.log(allSheets);

}

function getParentFolders() {

var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId());

var folders = file.getParents();

return folders;

}

// Parameter folder: a DriveApp folder object

function getSheetsFromFolder(folder) {

// The line below is for testing. Comment it out when the code is ready to deploy.

//folder = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next();

// Begin real code here

var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);

var sheetIdArray = [];

while (files.hasNext()) {

var currentFile = files.next();

sheetIdArray.push(currentFile.getId());

}

return sheetIdArray;

}

function getSubFolders(folder) {

// The line below is for testing. Comment it out when the code is ready to deploy.

//folder = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next();

// Begin real code here

var subFolders = folder.getFolders();

return subFolders;

}

function recursiveLoop(folder) {

// The line below is for testing. Comment it out when the code is ready to deploy.

//folder = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next();

// Begin real code here

var subFolders = folder.getFolders();

var sheetIds = [];

var sheetIdArray = getSheetsFromFolder(folder);

for (i in sheetIdArray) {

sheetIds.push(sheetIdArray[i]);

}

while (subFolders.hasNext()) {

var subFolder = subFolders.next();

sheetIdArray = getSheetsFromFolder(subFolder);

for (i in sheetIdArray) {

sheetIds.push(sheetIdArray[i]);

}

sheetIdArray = recursiveLoop(subFolder);

for (i in sheetIdArray) {

sheetIds.push(sheetIdArray[i]);

}

}

return sheetIds;

}

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

James Petersen, Consultant at Amplified IT

James Petersen is a pedagogical and data consultant with Amplified IT, a high-school math teacher, the leader of the Montreal Google Educator Group and a Google Education Trainer and Google Certified Teacher. He has taught mathematics in a 1:1 program, and actively develops techniques to integrate Google Apps for Education and other technology tools to support learning both in and out of the classroom. He has trained educators all over North America to shift their pedagogy and use technology to create learning opportunities that technology provides.

James is also an active contributor and supporter of the Apps Script for Education - Builders and Users community on Google Plus. As an enthusiastic coder, he develops custom solutions to problems that teachers and schools face, creating connections between services and users to facilitate learning. He has hosted webinars focusing on empowering teachers to create their own solutions, and to share them with the global community.

Ready to start learning?
Take This Course