If I have to do something twice, I usually try to find a way to automate or streamline it. Why? Because if I spent five minutes every single day sorting or moving rows from one spreadsheet to another then that adds up to 30 hours a year. If you have a few different spreadsheets that you’re trying to keep organized, think about how quickly that 30 hours a year could multiply.
How to add a custom script to Google Sheets
In the top navigation, select Tools > Script Editor to open the script editor window, then click File > New > Script File to open a new script file.
⚠️WARNING: If you directly copy & paste formulas from this post – make sure you doublecheck the formatting on quotes and doublequotes - copying from a webpage could alter the formatting and cause the script to break. You may need to replace quotes and doublequotes while you're in the script editor.⚠️
Auto-sorting by date or priority
I use this script in my to-do lists, client status sheets, and review sheets to automatically organize rows by priority or due date, ensuring that the most time-sensitive item is at the top of the sheet.
This function auto-sorts the given range A2:H30 within the sheet name Review Tracker by column 5 (the column where I keep due dates).
function myFunction(event){
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() == 'Review Tracker') {
var editedCell = sheet.getActiveCell();
var columnToSortBy = 5;
var tableRange = "A2:H30"; // What to sort
// column A = 1, B = 2, etc...
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: true } );
}
}
}
Moving rows to a separate tab upon completion
This is a script I use for client status sheets or review sheets to automatically move one row from an in-progress sheet to a completed sheet.
This function watches column 7 in the sheet Review Tracker for the value Complete and then moves the row to the sheet Completed Reviews when that value appears.
function myFunction() {
// moves a row from a sheet to another when a magic value is entered in a column
// adjust the following variables to fit your needs
// see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
var sheetNameToWatch = "Review Tracker";
var columnNumberToWatch = 7;
// column A = 1, B = 2, etc...
var valueToWatch = "Complete";
var sheetNameToMoveTheRowTo = "Completed Reviews";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();
if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(range.getRow());
}
}
Automated email updates for new rows
This is a script I use for reviews. Whenever a team member adds a new row into my review tracker spreadsheet and marks it as Ready to Review, an email is automatically sent to my inbox that includes: Name, Client Name, Item Being Reviewed, Link to the Item Being Reviewed, Deadline, Notes, and Status.
This function watches the status for the value Ready to Review. When that value is found, it adds the value sent to column 8 and sends out an email with specified row information (found in the set HTML template for information message section). If the sent value is already in column 8, the function skips to the next row that features both Ready to Review and a blank column 8.
function sendEmail() {
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (ActiveSheet.getName() == 'Review Tracker') {
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,7);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues) {
//set current row
var CurrentRow = AllValues[i];
if (CurrentRow[6] == "Ready for Review" && CurrentRow[7] != "sent") {
//define column to check if sent
// column A = 1, B = 2, etc...
//var EmailSent = CurrentRow[8];
//if row has been sent, then continue to next iteration
//if (EmailSent == "sent") {
//continue;}
//set HTML template for information
message +=
"<p><b>Name: </b>" + CurrentRow[0] + "</p>" +
"<p><b>Client: </b>" + CurrentRow[1] + "</p>" +
"<p><b>Item to Review: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Link to Item: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Deadline for Review: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Notes: </b>" + CurrentRow[5] + "</p>" +
"<p><b>Status: </b>" + CurrentRow[6] + "</p>" + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
// column A = 1, B = 2, etc...
ActiveSheet.getRange(setRow, 8).setValue("sent");
}//if review ready
}//For loop close
//define who to send emails to
var SendTo = "example@email.com";
//set subject line
var Subject = "New Deliverable to Review";
//send the actual email if message is not empty
if (message) {
MailApp.sendEmail({
to: SendTo,
subject: Subject,
htmlBody: message,
});
}//if message
}//if sheetName Review
}//End Func
Using multiple scripts
If you’re running multiple scripts you’ll need to adjust so that they all have unique names. In my example below, the function onEdit calls each function, titled myFunction1 and myFunction2. If you have multiple functions with the same name, Google will only run the last function unless you differentiate.
Below is an example of combining the Auto-Sort and Move Upon Completion functions above into a single script.
function onEdit(event) {
myFunction1(event);
myFunction2();
}
function myFunction1(event){
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() == 'Review Tracker') {
var editedCell = sheet.getActiveCell();
var columnToSortBy = 5;
var tableRange = "A2:H30"; // What to sort
// column A = 1, B = 2, etc...
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: true } );
}
}
}
function myFunction2() {
// moves a row from a sheet to another when a magic value is entered in a column
// adjust the following variables to fit your needs
// see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
var sheetNameToWatch = "Review Tracker";
var columnNumberToWatch = 7;
// column A = 1, B = 2, etc...
var valueToWatch = "Complete";
var sheetNameToMoveTheRowTo = "Completed Reviews";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();
if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(range.getRow());
}
}
Adding project triggers
In order for your scripts to run, you’ll need to add a trigger. Triggers allow your scripts to execute upon certain events like opening the spreadsheet, editing the spreadsheet, etc.
To add a project trigger, click on the trigger (clock) icon to open the project trigger popup. From here you can add a new trigger and select which function should run and what event should trigger the function to execute.
Sign up for our newsletter for more posts like this - delivered straight to your inbox!
