Boost Productivity With These Excellent Google Spreadsheet Scripts

Danny Stieben 03-12-2013

Did you know that the suite of products on Google Drive, especially Google Spreadsheet, can have their functionality extended by custom scripts? These scripts can dramatically improve your productivity OneDrive vs Google Drive - Which Is Best for Office Productivity? As the push towards web apps has evolved, we're now met with two powerful cloud solutions -- SkyDrive and Google Drive. These two options offer all the tools needed for file synchronization and office productivity.... Read More while using Google Spreadsheet, and it’s relatively easy to alter existing scripts or even create your own!


If you use a spreadsheet application to crunch data, then custom scripts could be the master key. Start rolling with these excellent Google Spreadsheet scripts and make use of your data in new ways.

How To Use Scripts

Boost Productivity With These Excellent Google Spreadsheet Scripts google spreadsheet scripts manager
Before you begin drooling over the following Google Spreadsheet scripts, it’s important to know how to add and use them.

1. To add a script, you’ll need to log into Google Drive, go to a spreadsheet, choose Tools — Script Editor.

2. Copy and paste the script code, and then click on Save.

3. To run the script, just go to Tools — Script Manager and choose the function you want. The name of the function corresponds to the name in the first line of the script, i.e. function removeDuplicates() results in the script being called removeDuplicates.


For all of you scripting and programming nerds, the scripts are written in JavaScript What is JavaScript and How Does It Work? [Technology Explained] Read More . Google has various resources that describe the API you can use in your script code. If you need to catch up on your JavaScript, there are plenty of free JavaScript resources Start Coding JavaScript Right Now With These 5 Great Free Resources Read More you can use.

Remove Duplicates

Boost Productivity With These Excellent Google Spreadsheet Scripts google spreadsheet scripts remove duplicates
If you’re working with a large spreadsheet that may have repeat information, it may be advantageous to remove those duplicate entries (depending on the context of your work). This way, you have a “perfect” dataset to work with that won’t confuse you with repeat information. The script code for this is the following:

function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
if(row.join() == newData[j].join()){
duplicate = true;
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

With a bit of research, I am sure that this script can also be tweaked to count the number of times an entry is duplicated, or even count the entries first and then delete the duplicates.


Send Email From Spreadsheet

Boost Productivity With These Excellent Google Spreadsheet Scripts google spreadsheet scripts send email
Did you know that you can also send emails from a spreadsheet? Absolutely! For this specific script, you can change the recipient and the message body, but the subject line is fixed. You can change it in the script code, or you can modify the script to accept a third column between the recipient and the message body for a subject. Then, just modify the number of items to be processed in the script, and run it. The script code for this is:

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 2; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var subject = "Sending emails from a Spreadsheet";
MailApp.sendEmail(emailAddress, subject, message);

Expanded Conditional Formatting

Boost Productivity With These Excellent Google Spreadsheet Scripts google spreadsheet scripts conditional formatting
One of the most useful features of spreadsheets is conditional formatting — a custom rule on a per-cell basis that changes its formatting (such as fill color) depending on the content of the cell. It works well, but it is also limited for single cells. If you want to expand the conditional formatting to an entire row, for example, then you’ll need to use a script.

This here is an example script that should do the job. This script sets the row color depending on the value in the “Status” column.


function setRowColors() {
var range = SpreadsheetApp.getActiveSheet().getDataRange();
var statusColumnOffset = getStatusColumnOffset();
for (var i = range.getRow(); i < range.getLastRow(); i++) {
rowRange = range.offset(i, 0, 1);
status = rowRange.offset(0, statusColumnOffset).getValue();
if (status == 'Completed') {
} else if (status == 'In Progress') {
} else if (status == 'Not Started') {
//Returns the offset value of the column titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getStatusColumnOffset() {
lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);
for (var i = 0; i < range.getLastColumn(); i++) {
if (range.offset(0, i, 1, 1).getValue() == "Status") {
return i;

However, note that the script is hard-coded, so you’ll need to change the test values (the content in the cell), the colors for the fill color, and maybe even add or remove cases as necessary for your spreadsheet.


As you can see, scripts can be extremely useful in Google Spreadsheet. They tend to be very specialized for specific requirements, so if you plan on using one that doesn’t come from the Script Gallery, there’s a high chance that you’ll need to edit portions of it yourself.

However, Google has plenty of resources and tutorials on how to edit scripts, so you should have everything you need to do the job.

That being said, don’t be afraid to check out the Script Gallery found under the Tools menu. There are plenty of great ones available that can do a lot for your day to day productivity. Ryan also showed us some amazing Google Spreadsheet functions Perform Amazing Feats With These Useful Google Spreadsheet Functions I am ashamed to admit that I've only recently decided to start experimenting with Google Spreadsheets, and what I've discovered is that whatever allure or power Excel had over me due to the amazing things... Read More . Using Google Forms, Google Spreadsheet, and scripts 10 Advanced Google Forms Tips and Tricks If Google Forms isn't your go-to form maker yet, these advanced Google Forms tips and tricks may change your mind. Read More is a power-task worth learning.


What’s your favorite script in Google Spreadsheet? Let us know in the comments!

Related topics: Google Drive, JavaScript, Programming, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. joe
    May 6, 2014 at 3:29 am

    Could you create a script for me that counts the number of cells in a row that are colored red and inputs that number to a cell?

  2. Phúc N
    December 25, 2013 at 7:09 am

    Google App Script is very powerful!

  3. Jsmith
    December 4, 2013 at 3:45 am

    I use these to manage class grades for my students.

  4. Seb
    December 4, 2013 at 2:25 am

    Where can we get more of these kinds of scripts?

    • Danny S
      December 31, 2013 at 11:54 pm

      If you go to Tools --> Script Library (can't remember the exact name, and I don't have access to it right now), you should find more there.

      This can help you code your own:

    • Venkat
      February 22, 2014 at 7:20 am

      Also sometimes it helps to try experimenting and looking at examples.

      Try developers forum, or look for some programmers who specialize in developing google scripts.

      check out this blog