Boost Productivity With These Excellent Google Spreadsheet Scripts

Ads by Google

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


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.

Ads by Google

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


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


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


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!

Join live MakeUseOf Groups on Grouvi App Join live Groups on Grouvi
Awesome Websites
Awesome Websites
137 Members
Deep Web Communities
Deep Web Communities
80 Members
Best Anonymity Tools
Best Anonymity Tools
73 Members
Tips for Privacy Obsessed
Tips for Privacy Obsessed
44 Members
Best Music Services
Best Music Services
43 Members
Online Security Tips
Online Security Tips
41 Members
Web for Kids
Web for Kids
32 Members
Ads by Google
Comments (6)
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.