Stop Wasting Time And Automate Google Sheets With Three Scripts

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.

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.

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 } );

}

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());

}

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

“Name: ” + CurrentRow[0] + “” +

“Client: ” + CurrentRow[1] + “” +

“Item to Review: ” + CurrentRow[2] + “” +

“Link to Item: ” + CurrentRow[3] + “” +

“Deadline for Review: ” + CurrentRow[4] + “” +

“Notes: ” + CurrentRow[5] + “” +

“Status: ” + CurrentRow[6] + “” + “ ”;

//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 = “[email protected]“;

//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());

}

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.

I’ve created a quick example sheet with all of the scripts in this post for reference. Feel free to make a copy and personalize the sheet! Just make sure that if you add or remove columns that you update the script accordingly!

Source link