Your First Google Apps Script

Google Apps Script is a JavaScript-based scripting language that you can use to automate tasks in Google Docs, Sheets, and Forms. There’s no extra software to be installed, as there’s a code editor provided in Google Drive. Your completed scripts run on Google’s servers, rather than in your browser like regular JavaScript programs.

If you’re new to JavaScript, you might want to find a primer. Here’s a great one, which by sheer coincidence (ahem!) I happen to have written …

 

Apps Script is powerful – it can add sidebars and menus to Docs, add spreadsheets and new functions to Sheets, send email, and interact with other Google Apps and services such as Analytics, Calendar, Maps and more.

Today we’ll write the simplest of scripts – to convert Celsius temperatures to Fahrenheit – and we’ll write this as a custom function to call within a cell of a spreadsheet created in Google Sheets.

 

Make a new spreadsheet and fill in the following few cells:

Click image to enlarge

Click image to enlarge 

From within your spreadsheet, use Tools > Script editor to open the editor. If you are presented with a dialog, click on Blank Project.

In the code editor, enter the following code:

/**
 * A custom function that converts Celsius to Fahrenheit.
 *
 * @param {Number} c = the temp in C.
 * @return {Number} = the temp in F.
 */
function tempConvertCtoF(c) {
   if (typeof c != 'number') {
      return null;
   }
   return (c * 1.8) + 32;
}

The top block, inside /* and */ is known as a comment, and does nothing except make your code easier to read (for you or for others). It’s good coding practice to comment your code.

The next item is a function definition; we’re designing a custom function tempConvertCtoF() to convert Celsius to Fahrenheit.

Click image to enlarge

Click image to enlarge

OK, save and name your script using File > Save from the menu.

Returning to the spreadsheet, we can now call the custom function in cell B3 by using:

=tempConvertCtoF(B1)

to convert the temperature:

Click image to enlarge

Click image to enlarge

… and there’s our Celsius temperature converted to Fahrenheit.