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

Zooper Widget for Android 2.36 - Latest Version Updates for 2024 and 2025

Zooper Widget for Android 2.36 - Latest Version Updates for 2024 and 2025

Discover the latest updates in Zooper Widget for Android. Zooper Widget is a compact, highly customizable, and battery-friendly widget with endless possibilities.

Lipp for iOS 4.4 - Make lip sync and voiceover videos on iPhone / iPad 2024, 2025

Lipp for iOS 4.4 - Make lip sync and voiceover videos on iPhone / iPad 2024, 2025

Lipp for iOS, alongside Dubsmash and Musical.ly, provides iPhone and iPad users with a new platform to create lip sync videos and dub videos readily.

How to Share Audio with TeamViewer - Latest Version Updates 2024, 2025

How to Share Audio with TeamViewer - Latest Version Updates 2024, 2025

Learn how to share audio using TeamViewer, including the latest updates for 2024 and 2025, making remote communication more effective.

Adorable Home: Latest Tips for Petting Your Cat in 2024 and 2025

Adorable Home: Latest Tips for Petting Your Cat in 2024 and 2025

Learn the latest tips on how to pet a cat in Adorable Home for optimal gameplay in 2024 and 2025. Discover what positions give you hearts.

OrangeHRM Open Source 3.2.1 - Latest Features for 2024 and Beyond

OrangeHRM Open Source 3.2.1 - Latest Features for 2024 and Beyond

Explore the latest features of OrangeHRM Open Source, a free HR management system tailored to meet evolving business needs in 2024 and 2025.

How to Fix IDM Error Not Catching Download Links - Updates for 2024 and 2025

How to Fix IDM Error Not Catching Download Links - Updates for 2024 and 2025

Fix IDM not catching file download links. Discover solutions for downloading videos, music, and more with the latest IDM updates in 2024 and 2025.

The File Converter for Android - Convert files to other formats - Updated Versions 2024, 2025

The File Converter for Android - Convert files to other formats - Updated Versions 2024, 2025

Discover the latest version of The File Converter for Android, an application that converts various file formats efficiently.

Updated Zalo Web Login Features for 2024-2025

Updated Zalo Web Login Features for 2024-2025

Explore the latest features of Zalo Web, allowing users to chat and connect seamlessly on various browsers without installation.

PDF Password Remover 6.0 - The Ultimate Solution for Password Removal in PDF Files

PDF Password Remover 6.0 - The Ultimate Solution for Password Removal in PDF Files

PDF Password Remover is a powerful tool for removing passwords and restrictions from PDF files. Download the latest version for 2024 to experience enhanced features.

Latest Instructions for Completely Removing Internet Download Manager (IDM) - Updated for 2024 and 2025

Latest Instructions for Completely Removing Internet Download Manager (IDM) - Updated for 2024 and 2025

Step-by-step instructions to completely remove Internet Download Manager (IDM) from your system in 2024 and 2025. Learn to uninstall IDM effectively and cleanly.

Mindjet MindManager 2024 & 2025 - Empower Your Mind Mapping

Mindjet MindManager 2024 & 2025 - Empower Your Mind Mapping

Discover Mindjet MindManager, an innovative software that helps individuals and teams streamline their work processes through effective mind mapping and project management.

Su Doku 2.0.0 - Latest Updates 2024 & 2025

Su Doku 2.0.0 - Latest Updates 2024 & 2025

Su DoKu 2.0.0 is an advanced Sudoku game design software that captivates enthusiasts with its simplicity and functionality. Download now and challenge yourself with Sudoku puzzles.

Google Photos for Android - Unlimited Photo and Video Storage with Latest Features in 2024, 2025

Google Photos for Android - Unlimited Photo and Video Storage with Latest Features in 2024, 2025

Discover the new features of Google Photos for Android in 2024 and 2025, including faster backup and advanced search capabilities.

Updated IQmango DVD Ripper 2024 - New Features and Enhancements

Updated IQmango DVD Ripper 2024 - New Features and Enhancements

Discover the latest version of IQmango DVD Ripper in 2024, a powerful software to easily rip, copy, convert, and back up DVDs.

When2meet - Tools to Arrange Group Meetings (Updated 2024, 2025)

When2meet - Tools to Arrange Group Meetings (Updated 2024, 2025)

When2meet is an easy-to-use group meeting scheduling tool for organizing collaborative meetings. Discover the latest features of When2meet for 2024 and 2025.

Sign up and Earn ⋙
Sign up and Earn ⋙