Google Sheets: How to Write a Custom Function

Posted January 19, 2015 by Walker Rowe in Computer programming

Untitled

Google Sheets (spreadsheets) lets you write custom functions using JavaScript.  JavaScript is perhaps the world’s easiest programming language, and there are many examples on the internet, making it easy to copy. There are lots of reasons to write your own functions.  For example, you could convert a temperature reading from Celsius to Fahrenheit. You could make a cell change color if your budget shows a shortage.  You could program the commission you pay your employees based on the formula you use.  Some of these things you can do with the functions that are already in Google Sheets.  However, if you write your own function, then people who share documents with you can reference those and not have to recreate that logic. Here’s how to write a custom function in a Google Spreadsheet.

Suppose you are an American living in a foreign country, and you have no understanding of the metric system. You want to write a function to convert Celsius to Fahrenheit.  Proceed as follows to write a function for this conversion in Google Sheets.

1.Create a sheet in Google Docs. Select Tools>Script Editor.

create formula in google sheets

 

2.The first time you use the Script Editor, Google shows a wizard where you can take tutorials and so forth.  Click Close to pass through that.
create formula in google sheets

3.Then, the empty Code Editor will be shown.

create formula in google sheets

 

4.Now, write and save your function. For this example, the function is saved under the name ctof, as in Celcius to Farenheit. Below is the code to convert from Celcius to Fahrenheit.

 

create formula in google sheets

5.If you click the arrow on the toolbar to run the function, Google Sheets will first ask you if you want to save it.

create formula in google sheets

 

6.You can give it any name.

 

create formula in google sheets

 

7.Now, Google is asking for permissions to run.

create formula in google sheets

 

8.Here is the screen where you give it permission.

create formula in google sheets

 

9.Now, you are done.  You can reference the function ctof in your spreadsheet just like any other function.

 

 

create formula in google sheets

 

About Walker Rowe

Walker Rowe writes a blog about living in rural Chile called 'The Avocado Republic.'
View more articles by Walker Rowe

The Conversation

Follow the reactions below and share your own thoughts.