4 scripts to make Google Sheets stronger

Google Sheets or Google Sheets is one of the most powerful free tools, allowing you to track, analyze or take notes of everything you can imagine. Not stopping there, you can enhance its capabilities with Googe Scripts (Google Script).

Download Google Sheets for iOS

Download Google Sheets for Android

Google Scrips sounds complicated and confusing. You may think it involves advanced programming knowledge but it really isn't. This article will introduce you to four simple scripts that help you customize functions, automatically create charts based on any data, customize your menu in Google Sheets, and even automate it. monthly email delivery.

1. Create your own custom functions

Creating custom functionality is one of the easiest ways to create a Google Scripts that can enhance your Google Sheets experience. Google Sheets has provided a long list of available functions. You can view the most popular ones by clicking on the function icon on the menu.

4 scripts to make Google Sheets stronger

Then click on More functions ... to open a list of algorithms, finances, techniques and more. However, Google Scripts gives you the flexibility to create your own personal formulas.
To create your first custom function, you'll first need to open the script editor by clicking Tools> Script Editor.

4 scripts to make Google Sheets stronger

You should see a window appear similar or similar to below.

4 scripts to make Google Sheets stronger

What you need to do is replace what is in this window with your own custom functionality. The function name is the same as the one you will start typing into a cell in Google Sheets after the "=" symbol to call your formula. A function to convert Celsius to Fahrenheit would look like this:

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

Paste the above functions into the code window and then select File> Save , name the project "CelsiusConverter" and click OK.

That is all you have to do. You start using your new function by typing "=" followed by your function, with the number entered to convert:

4 scripts to make Google Sheets stronger

Hit enter and you will get results.

4 scripts to make Google Sheets stronger

2. Automatically create charts

You can have a new spreadsheet every month with new data and you want to automatically create a new chart without having to recreate it every month. You can do this by creating a function that will create a new chart for you based on the data in the current workbook you have opened.

Suppose 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 scripts to make Google Sheets stronger

What you want to do is run a single function on this table that will automatically generate a chart in seconds. The script will look like this:

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);
}

All you have to do is open each student's spreadsheet and click the run icon in the menu in Google Scripts to automatically create the chart.

4 scripts to make Google Sheets stronger

Whenever you click the run icon, it will run the script that you created on the "active" spreadsheet (a workbook you have opened in your current browser tab).

4 scripts to make Google Sheets stronger

For reports that you have to create regularly, like weekly or monthly, this type of auto-charting function can really save you a lot of time, because you don't have to recreate from scratch. When you want to import chart data on a new worksheet.

3. Create custom Menu

If you don't want a script to automatically generate charts and want that functionality right at your fingertips on the menu system in Google Sheets, you can make that happen.

To create a custom menu, you need to give the spreadsheet a new menu item every time it opens. You can do this by creating an onOpen () function in the Script Editor window above 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 code and then reload your spreadsheet. You will discover that a new menu item appears with the name you specify it in your script. Click on the menu and you will see the menu item for that function.

4 scripts to make Google Sheets stronger

Click the menu item and it will run the same functions as it did when you click the run icon from within the Google Scripts editor.

4. Send the report automatically

The last script is a script that will email from within Google Sheets. This can be useful if you are managing a large group of people and you have to send multiple emails on the same subject. You may have performed a performance review with each team member and have recorded your review comments for each person in Google Spreadsheets. It is very convenient and time-saving to just run a single script and the reviews will be automatically sent to 50 or 60 people at the same time without having to create each email and send it individually. That is the power of Google Scripting.

Similar to the way you created the scripts above in this article, you will create a script for automatic report submission by going to the script editor and creating a function called sendEmails () , as follows: :

function sendEmails () {
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);
}
}

So, suppose you have the worksheet organized as shown here.

4 scripts to make Google Sheets stronger

The above script will do everything through each row in the spreadsheet and send an email to the address in the second column with the message you entered 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 you have never considered before.

If in reality you have someone else responsible for importing data into your Google spreadsheet, and if you automatically deliver emails based on the entered data, you can do something like send a monthly report to Boss, that never really needs to open his customer email. The script can automatically do all the work for you.

All these features of Google Scripts will show you that with just a few simple lines of code, Google Scripts has the right to automate part or all of Google Sheets. These automation can be set to run on schedule or run whenever you want to enable them. Either way, they provide you with a way to automate a lot of work without spending a lot of effort and time.

Sign up and earn $1000 a day ⋙

Leave a Comment

5 Best Google Sheets Add-ons to Make Data Analysis Easier

5 Best Google Sheets Add-ons to Make Data Analysis Easier

Google Sheets is a powerful tool for data analysis, but sometimes you need more power. There are plenty of add-ons that can speed up your spreadsheets and take your data processing to the next level.

How to Add Gemini to Improve Your Google Sheets Experience

How to Add Gemini to Improve Your Google Sheets Experience

Tired of spending hours creating spreadsheets and entering data manually? You'll be glad to know that you can use Gemini's creation features with Google Sheets to automate tasks and improve your overall experience.

How to Export Airtable to Excel and Google Sheets

How to Export Airtable to Excel and Google Sheets

Airtable lets you create and share relational databases easily. Export your Airtable data to Google Sheets or Excel to enjoy the benefits of advanced charts and data visualization.

How to Create a Monthly Spending Tracker on Google Sheets

How to Create a Monthly Spending Tracker on Google Sheets

Finances can be a daunting task, but thanks to Google Sheets, keeping track of your spending doesn't have to be a hassle. You don't have to invest in another software either.

How to Track Registrations Using Google Sheets

How to Track Registrations Using Google Sheets

Google Sheets is the only tool you need to record and manage these subscriptions.

Set location in Google Sheets

Set location in Google Sheets

Google Sheets has location settings that are easy to use and useful. Here's what you need to know about using settings in Google Sheets.

Why are so many people stopping using Google Sheets and going back to Excel?

Why are so many people stopping using Google Sheets and going back to Excel?

For a while, Google Sheets lured people away from Excel. But many people came back after realizing that Google Sheets is far from beating Excel.

Is Gemini in Google Sheets as advertised?

Is Gemini in Google Sheets as advertised?

Since Gemini in Google Sheets provides textboxes, you can ask it to do anything. But what can Gemini in Google Sheets actually do and where does it fall short?

How to use the FILTER function in Google Sheets

How to use the FILTER function in Google Sheets

How to use the FILTER function in Google Sheets, The FILTER function in Google Sheets allows you to filter data and return rows that meet specific criteria. Here's how to use it

How to turn a Google Sheets spreadsheet into a colorful rainbow

How to turn a Google Sheets spreadsheet into a colorful rainbow

To celebrate the International Pride Month, Google allows users to turn a dry, monotonous spreadsheet into a colorful rainbow extremely quickly.

Instructions to insert Checkbox on Google Sheets

Instructions to insert Checkbox on Google Sheets

The following article will show you how to insert checkboxes into a cell in Google Sheets (Google Sheets).

Common keyboard shortcuts for Google Sheets

Common keyboard shortcuts for Google Sheets

Google Sheets is an online spreadsheet creator that allows users to create and edit spreadsheet files quickly and conveniently.

How to open Excel file on iPhone and iPad?

How to open Excel file on iPhone and iPad?

Looking for an application that allows you to view and edit Excel sheets and files on your iPhone and iPad? So do not miss the following tools.

How to read five/five, four/four, one/one… correctly in the sequence of natural numbers?

How to read five/five, four/four, one/one… correctly in the sequence of natural numbers?

Five or five? Are you wondering whether reading two thousand twenty-five is correct? This article will give you the answer.

How to store avocado in the freezer

How to store avocado in the freezer

Freezing can help keep avocados fresher longer, but it can reduce their vitamin content over time. Here's how to best store avocados in the freezer.

Instructions to change the default File Explorer folder

Instructions to change the default File Explorer folder

By default, File Explorer opens to the Home folder, which contains recently used folders and files. If you want to change the default File Explorer folder, follow the instructions below.

Ways to reduce the risk of birth defects in the fetus

Ways to reduce the risk of birth defects in the fetus

Birth defects are something no one wants. Although they cannot be completely prevented, you can take the following steps to reduce the risk of birth defects in your baby.

What planets are Venus and Venus?

What planets are Venus and Venus?

What are the evening star and the morning star? Here's what you need to know about the evening star and the morning star.

Huawei Mate XT Ultimate screen repair price is as expensive as an iPhone 16 Pro Max

Huawei Mate XT Ultimate screen repair price is as expensive as an iPhone 16 Pro Max

According to the official price list announced by Huawei itself, repairing the Mate XT Ultimate screen will cost up to 7,999 CNY, equivalent to 1,123 USD or nearly 28 million VND, equal to the price of an iPhone 16 Pro Max.

Summit supercomputer is about to retire

Summit supercomputer is about to retire

Oak Ridge National Laboratory (ORNL) announced that Summit, the world's most powerful supercomputer in 2018 and 2019, will be shut down in November after nearly six years of operation.

How to connect wifi on Panasonic Smart TV

How to connect wifi on Panasonic Smart TV

Most Panasonic smart TVs support wifi connection so that users can use and experience many useful features, programs and information not only domestically but also abroad.

How to connect wifi for LG Smart TV

How to connect wifi for LG Smart TV

Connecting wifi to LG Smart TV is not too difficult, you just need to follow 4 steps according to the instructions in our article to succeed.

How to connect wifi for Sony Smart TV

How to connect wifi for Sony Smart TV

If you are looking for a way to connect wifi to your Sony Smart TV, please refer to this article of ours!

What order should I watch Attack on Titan?

What order should I watch Attack on Titan?

What is the order to watch Attack on Titan is a question that many people are interested in. This article will tell you how many parts Attack on Titan has and how to watch it.

Thai Alphabet

Thai Alphabet

If you love Thailand and want to learn Thai, the first thing you need to do is get acquainted with and memorize the Thai alphabet.

OpenAI Announces GPT-4.1 - The Smartest Model for Complex Tasks

OpenAI Announces GPT-4.1 - The Smartest Model for Complex Tasks

OpenAI has officially introduced three new models: GPT-4.1, GPT-4.1 mini, and GPT-4.1 nano. These models come with a massive context capacity of up to 1 million tokens and a knowledge limit updated until June 2024.

How to present survey results in PowerPoint or Google Slides

How to present survey results in PowerPoint or Google Slides

You have the survey results in hand but don't know how to present them? Don't worry, this article will guide you how to present the survey in PowerPoint, Google Slides.

200+ first sight words for children learning English

200+ first sight words for children learning English

Sight words are short, common, “easy” words that young children can learn to recognize by seeing them (rather than sounding them out). Here are the first English sight words that children should learn.