Pinterest Stumbleupon Whatsapp
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 SkyDrive vs Google Drive - Which Is Best for Office Productivity? SkyDrive 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


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 What is JavaScript and How Does It Work? [Technology Explained] 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 Start Coding JavaScript Right Now With These 5 Great Free Resources Read More 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 Perform Amazing Feats With These Useful 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 Tips & Tricks For Google Forms 10 Advanced Tips & Tricks For Google Forms If Google Forms is not yet your go-to form maker, it's time for us to change your mind. You may think you know everything there is to know about Google Forms, but thanks to Google's... Read More is a power-task worth learning.

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

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

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

Leave a Reply

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