4 Google Script makes Google Sheets more powerful

Google Sheets is part of Google Suite and is by far one of the most powerful free tools. It allows you to track, analyze or log anything you can imagine. What makes this tool even more powerful is the use of Google Script to enhance the capabilities of Google Sheets.

Writing code in Google Sheets can sound a bit scary. You may think that anything related to the word 'script' will require advanced programming knowledge. But that is not true in this case.

Let's take a look at some interesting Google Sheets scripts that can help you increase your productivity.

What is Google Sheets script?

Google Sheets scripts are pieces of code you can write inside Google Sheets, to power your sheets. Google Sheets scripts are written in JavaScript and because JavaScript is becoming more and more popular, you may already be familiar with it.

Writing Google Script is very similar to writing VBA in Microsoft Excel to create programs. The 'soul' of scripts in Google Sheets is the Google Apps Script and also works with other Google services. Here are 4 scripts that really prove their power in Google Sheets.

1. Create your own custom function

One of the easiest ways to create Google Script that can significantly improve your Google Sheets experience is to create custom functions. Google Sheets has provided a long list of functions. You can see the most popular ones by clicking the Menu icon > Functions .

4 Google Script makes Google Sheets more powerful Picture 1

Clicking More functions gives you a long list of mathematical, statistics, financial, text, engineering, and so on. However, Google Script gives you the flexibility to create personalized formulas. personalization of their own.

For example, suppose that you usually enter information from a digital thermostat when working, but the thermostat is set in Celsius (° C) units. You can create your own custom formula to convert Celsius to Fahrenheit (° F), so with just one click, you can automatically convert all those entered values.

To create the first custom function, you will need to open the Script editor. To do this, click Tools> Script Editor .

4 Google Script makes Google Sheets more powerful Picture 2

You will see the project screen where you can write your JavaScript code.

4 Google Script makes Google Sheets more powerful Picture 3

Here, replace what's in this window with your own custom function. The function name is the same as the name you will start typing into a cell in Google Sheets after the '=' symbol to call the formula. A function that converts Celsius into Fahrenheit will look like this:

 function CSTOFH (input) { return input * 1.8 + 32; } 

Paste the above function into the code window, then select File> Save , give a name of type 'CelsiusConverter' and click OK.

That is all there is to it! Now, when you want to use your new function, you enter the = sign followed by the function, with the number of inputs to convert:

4 Google Script makes Google Sheets more powerful Picture 4

Press Enter to see results.

4 Google Script makes Google Sheets more powerful Picture 5

All operations are completed! You can quickly see how to write any custom formula you need to fit Google Sheets.

2. The auto-generated chart

Suppose you have to work with a new data table every month. If you want to automatically create a chart using data in a spreadsheet, you can do this by creating a function, which will create a new chart based on the data in the currently open workbook.

For example, you are a teacher and at the end of the year, you have a spreadsheet for each student with a list of monthly test scores:

4 Google Script makes Google Sheets more powerful Picture 6

What you want to do is run a single function on this worksheet and create a chart in seconds. The script to do this is as follows:

 function GradeChart() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheets()[0]; var gradechart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A1:B11')) .setPosition(5, 5, 0, 0) .build(); sheet.insertChart(gradechart); } 

Now, open each student's spreadsheet and click the Run icon in the Google Scripts menu to automatically generate the chart.

4 Google Script makes Google Sheets more powerful Picture 7

Whenever you click the Run icon, the script you have created on the spreadsheet (opens in the current browser tab) will be triggered.

4 Google Script makes Google Sheets more powerful Picture 8

For reports that you have to generate regularly, such as weekly or monthly, the type of auto-generating function graph can really save you a lot of time.

3. Create custom menus

What if you don't want to open the script to automatically generate that chart? What if you liked the convenience of having that function right at your fingertips, on the menu system, right inside Google Sheets? Luckily you can do that too.

To create a custom menu, you need to 'tell' the spreadsheet to add a new menu item every time it opens. You do this by creating an onOpen () function in the Script Editor window on the Gradechart function you just created:

 function onOpen() { var spreadsheet = SpreadsheetApp.getActive(); var menuItems = [ { name: 'Create Grade Chart.', functionName: 'GradeChart' } ]; spreadsheet.addMenu('Charts', menuItems); } 

Save the script and then reload the spreadsheet. You will discover that the new menu item now displays with the name you specified it as in the script. Click menu and you will see the menu item for your function.

4 Google Script makes Google Sheets more powerful Picture 9

Click on the menu item and it will run the same function as if you clicked the Run icon from within the Google Scripts editor!

4. Send the report automatically

The final script example in this article will email from within Google Sheets.

This method can be useful if you are managing a large group of people and have many emails to send about the same topic.

You may have performed a performance review for each team member and recorded your review comments for each person on the Google sheet.

It would be nice to just have to run a script and have those comments automatically emailed to 50 or 60 employees at the same time without you having to create all those individual emails, right? That is the power of Google Scripting.

Similar to the way you created the scripts above, you will create a script by going to the Script editor and creating a function named sendEmails () , as follows:

 var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // First row of data to process var numRows = 7; // Number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, 3) var data = dataRange.getValues(); for (i in data) { var row = data[i]; var emailAddress = row[1]; // Second column var message = row[2]; // Third column var subject = "My review notes"; MailApp.sendEmail(emailAddress, subject, message); } } 

Suppose you have a spreadsheet organized like this.

4 Google Script makes Google Sheets more powerful Picture 10

The above script will work across each row in the spreadsheet and send an email to the address in the second column with the message you typed in the third column.

The sendEmail function in Google Scripts is one of the most powerful functions in Google Scripts, because it opens up a world of email automation that saves you time.

This script shows you the true power of Google Apps Scripting, combining Gmail with Google Sheets Scripts to automate a task. Although you have seen scripts working on Google Sheets, it's best to take advantage of the power of script creation across Google Suite. For more detailed implementation, please refer to the article: How to Send Email Automatically in Google Sheet with Google Script.

What all of these automated Google Scripts show you is that with just a few simple lines of code, Google Script has the ability to partially or completely automate Google Sheets.

These functions can be set to run on schedule or you can run them manually whenever you want to activate them. Google Script can automate boring tasks like emailing or sending invoices from Google Sheets.

Hope you are succesful.