How to Write Scripts in Google Sheets?


Did you know you can put in custom scripts in Google Sheets that allow you to create functions you normally have no access to on the standard app, like say create a custom Gantt chart maker or a script that creates calendars for you automatically?

The script can be as complex or simple as you want. However, you need to first be aware of how to access the script editor of Google Sheets first before you can do such things!

What is Google Apps Script (GAS)?

The Google Apps Script or GAS is a coding language you can script out on Google Sheets in order to create functions and features for your use with Google Apps. So it works not only with Google Sheets but also with the rest of the apps like Google Docs, Google Drive, Maps, Calendar, Forms, and so forth.

This tutorial will focus on using Google Sheets scripts with GAS. Yes, you can also use it for other Google Apps, but the script we’ve availed of is specific for Google Sheets and you’ll have to script another custom function for the other apps.

How to Write Scripts in Google Sheets

Do the following in order to access GAS and write a custom function.

Step 1: Open an existing spreadsheet or create a new spreadsheet on Google Sheets.

Write Scripts Step 1 - Open spreadsheet

We’ve put in a short table we could use for our custom script later on. It’s Amazon’s revenue from 2009 to 2021 or how much money the company has been making for 12 years.

Step 2: Go to the “Extensions” menu on the menu bar then select the “Apps script” command.

Write Scripts Step 2 - Go to Extensions and Apps script

Step 3: You’ve now opened the “Apps Script” editor in a new tab as an “Untitled project“. Here’s where the magic happens. The scripting language here works kind of like JavaScript so if you’re versed in JS, you can customize all sorts of GAS functions from here!

Write Scripts Step 3 -You_re now on the Apps Script editor window on a new tab

Step 4: Delete the code found on the script editor and just paste the following sample script:

<em>function SalesChart()</em> <em>{ </em> <em>var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); </em> <em>var sheet = spreadsheet.getSheets()[0]; </em> <em>var SalesChart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A2:B10')) .setPosition(5, 5, 0, 0) .build(); </em> <em>sheet.insertChart(SalesChart); </em> <em>}</em>
Code language: HTML, XML (xml)

Just copy that and paste it on the script editor window. Don’t forget to name the project and save everything. At the top, click “Save” to apply changes on your project.

Write Scripts Step 4A - Write and paste a script, save changes, then run the script

Now run the script and see if it works on the other tab where your spreadsheet is.

You’ll also be given a prompt telling you that the GAS is an “unverified app” requiring verification. Just go to “Advanced Settings” in order to bypass this prompt and permit the script to run.

Write Scripts Step 4B - Verify the app by clicking advanced and running the script

The Execution log should look like this when all is said and done.

Write Scripts Step 4C - Execution completed on the Execution log

Step 5: Admire the results of your script on the other tab. It’s a script to turn your data into a line graph.

Write Scripts Step 4C - Go back to the spreadsheet and see the results of the script

Step 6: Experiment with other GAS for good measure. Here’s one that can convert Celsius or centigrade temperatures to Fahrenheit.

<em>function CTOFH (input) </em> <em>{</em> <em>return input * 1.8 + 32;</em> }
Code language: HTML, XML (xml)

Write Scripts Step 6A - Create new script for use as function

Now you can use this function to convert Celsius temperatures to Fahrenheit like so.

Write Scripts Step 6B - You can now use the custom script as a normal Google Sheets function

You can also learn the JavaScript or GAS coding language specifically in order to come up with your own custom scripts in the future instead of copy-pasting ready-made templates.

Some Final Words

GAS or Google App Scripts allow the advanced programmers to make custom scripts or the ordinary users to copy and paste useful scripts on their Google Sheets, particularly when it comes to functions not natively present with the app itself.

They work like app extensions in that they expand the capabilities of Google Sheets. If you’re a half-decent programmer, you can use the JS-like programming language to do all sort of neat conversion table features and whatnot on a typical spreadsheet with the GAS feature.

References:

  1. Extending Google Sheets“, Google.com Developers, July 22, 2022
  2. Saperis, “Google Apps Script Tutorial for Beginners“, YouTube, May 14, 2020
  3. Custom Functions in Google Sheets “, Google.com Developers, July 22, 2022
  4. Google Sheets Script 101: Easy Beginner’s Guide“, SpreadsheetPoint.com, February 6, 2022
  5. Learn Google Spreadsheets, “Script Editor (NEW) – Google Apps Script, Syntax Autocomplete Secrets, V8 Runtime – Google Sheets“, YouTube, December 22, 2020

Apps Script: Open Here

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts