Boost Productivity With These Excellent Google Spreadsheet Scripts

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 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

google spreadsheet scripts manager   Boost Productivity With These Excellent Google Spreadsheet Scripts
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. 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 you can use.

Remove Duplicates

google spreadsheet scripts remove duplicates   Boost Productivity With These Excellent Google Spreadsheet Scripts
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;
}
}
if(!duplicate){
newData.push(row);
}
}
sheet.clearContents();
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

google spreadsheet scripts send email   Boost Productivity With These Excellent Google Spreadsheet Scripts
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

google spreadsheet scripts conditional formatting   Boost Productivity With These Excellent Google Spreadsheet Scripts
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') {
rowRange.setBackgroundColor("#99CC99");
} else if (status == 'In Progress') {
rowRange.setBackgroundColor("#FFDD88");
} else if (status == 'Not Started') {
rowRange.setBackgroundColor("#CC6666");
}
}
}
//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.

Conclusion

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. Using Google Forms, Google Spreadsheet, and scripts is a power-task worth learning.

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

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

6 Comments -

0 votes

Seb

Where can we get more of these kinds of scripts?

0 votes

Danny S

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: https://developers.google.com/apps-script/

0 votes

Venkat

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 http://gdocsapi.wordpress.com

0 votes

Jsmith

I use these to manage class grades for my students.

0 votes

Phúc N

Google App Script is very powerful!

0 votes

joe

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?