In this post, I demonstrate how to use the onSelectionChange(e) trigger function in an application that copies Rows from one sheet to another.
How to Video:
Video Notes:
- onSelectionChange(e) Trigger Documentation
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
Code in Video:
function onSelectionChange(e) {
var range = e.range;
var spreadSheet = e.source;
var sheetName = spreadSheet.getActiveSheet().getName();
var column = range.getColumn();
var row = range.getRow();
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if(row > 1 && column === 6 && sheetName === 'DATA')
{
if(dataSheet.getRange(row,6).getValue() === 'X')
{
dataSheet.getRange(row,6).setValue('');
}
else
{
dataSheet.getRange(row,6).setValue('X');
}
}
}
function copyRows()
{
var copySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('COPY TO SHEET');
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATA');
var dataRange = dataSheet.getDataRange();
var dataValues = dataRange.getValues();
for(var i = 1; i < dataValues.length; i++)
{
if(dataValues[i][5] === 'X')
{
copySheet.appendRow([dataValues[i][0],
dataValues[i][1],
dataValues[i][2],
dataValues[i][3],
dataValues[i][4]]);
}
}
for(var i = 1; i < dataValues.length; i++)
{
if(dataValues[i][5] === 'X')
{
var clearRow = i+1;
dataSheet.getRange('A' + clearRow + ':F' + clearRow).clear();
}
}
}
function addMenu()
{
var menu = SpreadsheetApp.getUi().createMenu('Custom');
menu.addItem('Copy Rows', 'copyRows');
menu.addToUi();
}
function onOpen(e)
{
addMenu();
}
Related Posts
How to use the onOpen(e) Function on Google Sheets - In this post, I show how to use the Google Apps Script onOpen(e) Trigger Function on Google Sheets. I show two examples of how the function can be used.
How to use the onEdit(e) Function on Google Sheets - In the Post, I demonstrate how to use the onEdit(e) Function on Google Sheets. This video focuses on the editing of one cell.
How to Use doGet(e) Function to Start HTML Google Web App - In this post, I show three different examples of how to use the doGet(e) function to start the HTML Google Web Application.