Copy Row Using onSelectionChange(e) Trigger on Google Sheets

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:

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.
Google onEdit(e) Function 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.
doGet(e) Function Google Script 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.

Leave a Reply