Google Apps Script - Introduction With Some Tips and Tricks

Google Apps Script allows you to write and deploy code scripts through Google Drive. These scripts (written in JavaScript) run server-side and have full access to Google's APIs. This means you can use them to automate all sorts of things, from sending emails to editing spreadsheets.

I've added some examples I use regularly to this repository on GitHub.

Before using GAS for larger projects, note that different types of Google accounts will get different quotas saying how much it can be used. Free gmail accounts have a quota high enough for personal use but be sure to check the limits if you have a larger scale use case in mind.

Google Apps Script - Setup

First I'll explain how to setup a Google Apps Script project.

Login to your Google Account and head over to Google Drive.

If you haven't used Google Apps Script before, you'll need to connect the extension to your Google Drive account. Press the 'New' button, select 'More' and press '+ Connect More Apps'. Then enter 'Google Apps Script' into the search field and select '+ Connect'.


 In Google Drive press 'New', then 'More', and select 'Google Apps Script'. In the top left, select 'Untitled Project' and enter a new name for the project e.g. 'Send Email Script'. (The name just appears in Google Drive and does not matter).

How does it work?

Now you have your Google Apps Script setup, you can write your code in JavaScript using the Google Apps Script APIs, and deploy it.

But, what deployment options are there?

1. As a person who regularily develops web services, I find the most useful is deploying the code as a HTTP endpoint. It can accept both GET and POST requests (and supports other methods), it can receive URL or Form parameters, and provide responses in JSON format. This means it can be used to build a complete REST API service ... but I wouldn't recommend it for large applications. I'll explain how to create basic individual HTTP endpoints in the next section.

Small Disclaimer: Using this deployment option the response will always have status code 301, i.e. it will redirect you to another URL which will give you the actual response. This means you'll have to enable automatic HTTP Redirects in your HTTP Client or code (or add this functionality yourself).

2. You can set your code to run at regular intervals such as every hour (a bit like a Cron Job would). You can also set the code to run on a specific date. These can be achieved by adding 'Trigger's to your code. They can also be setup to notify you if a run fails.

3. You can also deploy your Apps Script as an 'Add-on'. This allows you to create other web apps which can connect to Google Drive. I won't cover it in this post, mainly because I don't know much about it :)

All of the above can also be configured using the Apps Script manifest file directly (a JSON configuration file), but I find it easier to ignore it and use the Apps Script interface.

1. Deploying as HTTP Endpoint

Note: If you are want to use Google Cloud services to call your Apps Script code then it may be easier to deploy as an 'API Executable' and then use Google Cloud APIs to call the script. This may be better since your App Script wouldn't be public facing. But we won't cover that here.

Firstly if you want your code to execute when receiving a HTTP GET request, your code should contain a 'doGet' function which will be the entry point. Similarly to handle POST requests include a 'doPost' function, and 'doUpdate', 'doDelete' and others will work the same. You can use more than one of these functions to accept multiple HTTP Methods.

function doGet(e) {
  // GET request entry point
}

function doPost(e) {
  // POST request entry point
}

To retreive URL or Form encoded parameters in your code you'll need to make use of the parameter provided by these methods (in the code above we name it 'e').

For example, the following code will give you the value for the parameter with key 'parameterName':

var parameterValue = e.parameter.parameterName; 

The next most important thing is how to return information in the HTTP response. Apps Script provides a ContentService class for this. To return a string "Hello World!" in the response body you can use:

return ContentService.createTextOutput("Hello World!");

And if you have a JavaScript object, you can return the object in JSON format like this:

var obj = {
  myString: "Hello World!"
};
return ContentService.createTextOutput(JSON.stringify(obj));

All this needs is some Google API calls, and we might be able to make something useful! I'll come back to API calls later. N ext I'll explain how to actually deploy this endpoint.

In the Google Apps Script web view Select 'Publish' then 'Deploy as web app...'. In the publishing window, change 'Who has access to the app' to 'Anyone, even anonymous'. This allows us to trigger the endpoint without needing to provide any Google account authentication.

Finally press deploy and you should be given a URL starting with 'https://script.google.com/macros/s/...', this is the URL which acts as the HTTP endpoint. Send a HTTP GET/POST request here and your code withing doGet/doPost will be executed.

You can try this in a HTTP Client like Postman, or for GET and POST requests you can use my online tool Curl It.

2. Setup as Scheduled Task

Another way to execute your code is to set it up as a scheduled task. The entry point can be any function you like, so lets create a function 'myFunction' which we'll use:

function myFunction() {
  // Entry point for scheduled task
}

Now again let's suppose we've written the body of the function (and you have saved it). To deploy it press the button for your scripts triggers as can be seen in the screenshot below:



 Then you will be taken to a page on the 'G Suite Developer Hub'. Here you can press 'Create a new trigger' where you can select the function to invoke, along with a schedule/date of when to invoke it. For example, to call your function every 10 minutes use the following:



Now the trigger is setup, your code will execute when specified.

In the remainder of this post, I'll explore what kind of things you can achieve with the various APIs available.

Debugging

Firstly, debugging GAS code isn't easy. You can use the 'Run' button in the GAS web view with the function you wish to run selected on the menu bar. You need to make sure your function doesn't require any inputs, and doesn't rely on any URL parameters. If you are using URL parameter's, I advise using the following structure:

function doPost(e) {
  var valueOne = e.parameter.paramOne;
  var valueOne = e.parameter.paramTwo;
  return ContentService.createTextOutput(myFunction(valueOne, valueTwo));
}

function test() {
  // Test your code works by providing a few different cases
  Logger.log("Response 1: " + myFunction("value1", "value2"));
  Logger.log("Response 2: " + myFunction("value3", "value2"));
  Logger.log("Response 3: " + myFunction("value3", "value1"));
}

function myFunction(paramOne, paramTwo) {
  // Do stuff here 
  // Return a string
}

Then you can run the 'test' function from the web view to try different test cases and you don't need to change your code when you deploy.

Next, how do you see the response from your code? This is especially relavent if you are deploying as a HTTP endpoint and want to check the response you are outputting using 'ContentService' is correct.

The best way of doing this is using Logging. This is also used in the example above where the response is written to logs during testing. GAS has a built in logger and you can write to the logs using the function 'Logger.log'.

Once you've run your code you can check the logs using 'View -> Logs' or using the shortcut Ctrl+Enter.

Note: Whenever you add an API such as the Logger to your code, you may need to give the app permission to run under your Google Account the first time you run/deploy.

Sending an Email using the MailApp

One of the most useful things you can do with GAS is manage your Gmail account inbox, which includes the ability to send automated emails from your gmail address. You can also send emails from other email addresses if you have them setup as aliases in gmail (I wrote a post about email aliases here).

For example to send a simple plaintext email, you can use the following code:

MailApp.sendEmail("recipient@gmail.com", "A Great Subject", "Hi, \n\nI emailed you! Isn't that great?");

You can also add a reply-to address if you don't want your recipients to reply to the address you are sending from:

MailApp.sendEmail(recipientAddress, replyToAddress, subject, message);

And you can send HTML email as well as just plain text, and also change the Sender Name (the name which is displayed to the recipient as the source of the email).

MailApp.sendEmail(recipientAddress, replyToAddress, subject, message, {
    name: senderName,
    htmlBody: htmlMessage,
});

To see more cool things you can do with emails, take a look at the GAS documentation here for the MailApp (mainly just for sending emails), and look at the documentation here for the GmailApp (for Gmail inbox management).

You might want to check the limits on how many emails you can send with the MailApp because it might be too low for all your automated emailing needs.

Managing files using the DriveApp

Google also provides us with the 'DriveApp' for managing files in Google Drive. I only tend to use this to get a reference to a file in order to use another service like the Spreadsheet Service (see next section). But, it might be useful if you use a lot of files in Google Drive and need to move them around regularly.

Here is a short list of some of the things you can do with the DriveApp:

var files = DriveApp.getFiles(); // Get a list of all files in your Google Drive
var files = DriveApp.getFilesByName(searchFileName); // Gets list of files with a name. You can iterate through these and perform operations on each file
var file = DriveApp.getFileById(searchFileId); // Gets a file with the given id
DriveApp.addFile(fileName, content); // Creates a new text file
DriveApp.addFolder(folderName).addFolder(anotherFolderName); // Adds a new folder to the root folder, then adds a new folder within that folder
var numBytesUsed = DriveApp.getStorageUsed(); // Returns the number of bytes used in your Google Drive

To understand the full capabilities of the DriveApp take a look at its documentation here. To see what you can do with file and folder objects (e.g. renaming/deleting), take a look at the File documentation and Folder documentation.

Note: If you are trying to find file objects, it is better practice (and probably more efficient) to search by their id, than by their name. From within Google Drive, you can find the id of any file by temporarily sharing it and looking at the sharing URL. The string after the last '/' in the URL is the id of the file.

Editing a Spreadsheet using the Spreadsheet Service

You can use the SpreadsheetApp to create a spreadsheet. This gives you an object of type 'Spreadsheet':

var spreadsheet = SpreadsheetApp.create("My New Speadsheet");

Or if you have an existing spreadsheet you can open it by Id or by Name using the DriveApp to find the file. For example to open a spreadsheet by it's Id (read the note above to get the Id).

var file = DriveApp.getFileById(spreadsheetFileId);
var spreadsheet = SpreadsheetApp.open(file);

Using the SpreadsheetApp you can actually do this in a single line:

var spreadsheet = SpreadsheetApp.openById(spreadsheetFileId);

Spreadsheet objects are made up of multiple 'Sheets', each with it's own name. You can get a Sheet object in multiple ways:

var sheet = spreadsheet.getSheets()[0]; // Get the first sheet in the spreadsheet
var anotherSheet = spreadsheet.getActiveSheet(); // Gets the sheet which is 'active' (normally the sheet which has been used mosted recently)
var thirdSheet = spreadsheet.getSheetByName("MySheetName"); // Gets the sheet with the given name

Adding a new row to a sheet is really easy:

sheet.appendRow(["value1", "value2", "value3"]);

But if you want to get or set the value of a specific cell, you'll need to retrieve a 'Range' first. A range is basically a block of cells with a top left cell and a bottom right cell. Retreiving large ranges can take time to process so it is better to work with the smallest ranges possible. Here are a few ways you can get ranges from a sheet:

var rangeOne = sheet.getActiveRange(); // Gets the 'active' range (normally the range used most recently). Doesn't necessarily include all non-empty cells
var rangeTwo = sheet.getDataRange(); // Gets the range containing all non-empty cells in the spreadsheet
var rangeThree = sheet.getRange(rowNum, colNum); // Gets range containing a single cell
var rangeFour = sheet.getRange(rowNum, colNum, numRows, numCols); // Gets block of cells with the given top left cell, range width and range height

If you want to retrieve the value of a cell in the range, you'll need to call 'getValues()' first:

var values = range.getValues();

Then you can get the value of a particular cell like this:

var cellValue = values[rowNum][colNum];

You can use this to iterate through all the rows/columns/cells in you sheet. For this your range would come from 'getDataRange()' and you can check the number of rows and columns in the range using 'range.getNumRows()' and 'range.getNumColumns()'.

Now to set the value of cells you can just set the values of a range of cells:

range.setValue(myValue);

Or for a specific cell in a range, you just get the range consisting of the single cell and use the same function. Note here that the 'rowIndex' and 'colIndex' are not simply the row number and column number of the cell, since your range might not start in the top left cell. These indices should be relative to the top left cell in the range:

range.getCell(rowIndex, colIndex).setValue(myValue);

There are many more things you can do with the Spreadsheet App, but you can find it all in the documentation. Here are the documentation pages for Spreadsheets, Sheets and Ranges.

Example Use Case - Putting a Contact Form on your Website

I'm taking this example from my GitHub repository to suggest a use case of GAS. If you are interested, there are a couple of other examples in the repository to look at.

Suppose you are creating a very simple, low traffic, static website and you want to avoid adding a backend/database. Perhaps you are considering hosting it on a basic service which will only serve static files (i.e. there is no 'backend' to run code on). One example of such a service is GitHub Pages. Now suppose you want a contact form on your website. This means you need somewhere (a database) to store the values people enter. Instead of setting up a backend/database why not just use GAS?

Just add some JavaScript to your site which sends a HTTP Post request to a GAS app deployed as a HTTP endpoint. The app can log the values in a Google Spreadsheet and even send you an email notification. It is quick to setup, and free! The following GAS code is exactly what you need:

// Set the following variables:

// Use this to disable sending emails to yourself on every contact form submission if you want
var SEND_EMAILS = true;

// This is email address where emails will be sent when the contact form is submitted
var YOUR_EMAIL_ADDRESS = "Your Name";

// This must be set to the ID of a Google Spreadsheet where you want contact form entries to be added.
//   You can find the ID of a spreadsheet by temporarily enabling a sharing link (the id is the string after the last "/" in the link url)
var SPREADSHEET_ID = "SPREADSHEET_ID";


/*
  When deployed as a Google Script Web App this code will wait for a HTTP POST request.
  When a HTTP POST request is received the code will be executed.
  The request will provide it with a full_name, email, and message (meant to be from a contact form).
  These values will be added to a spreadsheet (with the ID provided above).
    And if SEND_EMAILS is true, an email will be sent to your address with the provided information.
  For more info see:
  https://github.com/James231/GoogleAppScript-UsefulScripts/tree/master/ContactForm
*/

function doPost (e) {
  var full_name = "" + e.parameters.full_name;
  var email = "" + e.parameters.email;
  var message = "" + e.parameters.message;

  // Check none of the data is empty
  if ((full_name) && (email) && (message)) {

    // Send email if enabled
    if (SEND_EMAILS) {
      var emailMessage = "Someone has submitted a contact form on your website!\n\nFull Name: " + full_name + "\n\nEmail: " + email + "\n\nMessage:\n" + message;
      MailApp.sendEmail(YOUR_EMAIL_ADDRESS, "Contact Form Submitted", emailMessage);
    }

    // Add to spreadsheet
    var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
    var sheet = spreadsheet.getSheets()[0];
    sheet.appendRow([full_name, email, message]);

    return ContentService.createTextOutput("Success");
  }

  return ContentService.createTextOutput("Error");
}

This code is actually very simple. It gets the submitted values of 'full_name', 'email' and 'message' from HTTP Post form parameters. Then uses the MailApp to send you (the owner) an email to notify you that someone has submitted an entry. And it adds them to a row of a Google Spreadsheet.

Thanks for Reading!

I hope you learnt something interesting about GAS!

If you liked this article, consider subscribing to my blog and/or commenting below.

And if you find the GitHub repo useful, please star it. If it gets some attention I might continue adding to it. Thanks!