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.
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.
3.Then, the empty Code Editor will be shown.
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.
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.
6.You can give it any name.
7.Now, Google is asking for permissions to run.
8.Here is the screen where you give it permission.
9.Now, you are done. You can reference the function ctof in your spreadsheet just like any other function.
