How to input data from another sheet based off a checkbox

Question

I am trying to input columns A,B,C from sheet1 into sheet2 based of whether column H is true or false with no gaps in the rows. The issue is if a higher row on sheet1 gets checked off any data inputted into sheet2 on the other columns is no longer matching with the data in column A. Is there a way to achieve this result using the built in formulas or will I need to make a custom formula to get the desired outcome?

I have tried all kinds of variations using arrayformula, query formulas, filter, if, etc. The best result so far has been

=ArrayFormula(IF('sheet1'!H2:H=TRUE, {'sheet1'!A2:B, ROW('sheet1'!A2:A)-1}, ""))

but I need to not have the gaps in the rows in sheet2. However any time I try using a formula that includes some way of getting rid of those gaps like the following

=ArrayFormula(QUERY({IF('sheet1'!H:H=TRUE, 'sheet1'!A:B, ""), IF('sheet1'!H:H=TRUE, ROW('sheet1'!A:A), "")}, "select * where Col1 is not null order by Col2 asc", 1)) 

then the data in the other columns on sheet2 does not move to match the corresponding data in column A like I need them to.

EDIT: here is an example sheet: https://docs.google.com/spreadsheets/d/1gfXOtPprIYmAvCsgGBTWPCQWowpZl07aXIhN145bcBo/edit?usp=sharing

Answer 1

I found a way to achieve what I was looking for using the Apps Script extension. I have posted the code below, and it is in the sample spreadsheet that is in the question to see how it works. This script will sort the active sheet via the checkbox, and transfer any relevant information onto the next sheet!

Code is as follows:

/**
* Sorts the sheet when a cell is edited in a magic column.
* Any frozen rows at the top of the sheet are left in place.
*
* This script will also copy data from one sheet to the next when the magic checkbox is changed to TRUE
*/

var sheetsToSort = 'sheet1'; // use //i to sort all sheets
var sortByColumn = 8; // column A = 1, B = 2, etc.
var sheetsToSort1 = 'sheet2|sheet3';
var sortByColumn1 = 11;
var sortAscending = false; // true for ascending, false for descending
/**
* Shows a message in a pop-up.
*
* @param {String} message The message to show.
* @param {Number} timeoutSeconds Optional. The number of seconds before the message goes away. Defaults to 5.
*/
function showMessage_(message, timeoutSeconds) {
  SpreadsheetApp.getActive().toast(message, 'Auto Tracker System', timeoutSeconds || 5);
}

/**
* Sorts the active sheet by sortByColumn.
*
* @param {Object} e The onEdit() event object.
*/
function autoSortSheet_(e) {
  var sheet = e.range.getSheet();
  if (!sheet.getName().match(sheetsToSort)
      || e.range.columnStart > sortByColumn
      || sortByColumn > e.range.columnEnd
      || e.range.rowEnd <= sheet.getFrozenRows()) {
    return;
  }
  showMessage_('Sorting sheet...', 2);
  var value = e.range.getDisplayValue();
  sheet.sort(sortByColumn, sortAscending);
  var columnValues = sheet.getRange(sheet.getFrozenRows() + 1, sortByColumn, sheet.getLastRow(), 1).getDisplayValues();
  for (var row = 0, numRows = columnValues.length; row < numRows; row++) {
    if (columnValues[row][0] === value) {
      sheet.getRange(sheet.getFrozenRows() + 1 + row, sortByColumn).activate();
      break;
    }
  }
}

/**
* Sorts the active sheet by sortByColumn1.
*
* @param {Object} e The onEdit() event object.
*/
function autoSortSheet1_(e) {
  var sheet = e.range.getSheet();
  if (!sheet.getName().match(sheetsToSort1)
      || e.range.columnStart > sortByColumn1
      || sortByColumn1 > e.range.columnEnd
      || e.range.rowEnd <= sheet.getFrozenRows()) {
    return;
  }
  showMessage_('Sorting sheet...', 2);
  var value = e.range.getDisplayValue();
  sheet.sort(sortByColumn1, sortAscending);
  var columnValues = sheet.getRange(sheet.getFrozenRows() + 1, sortByColumn1, sheet.getLastRow(), 1).getDisplayValues();
  for (var row = 0, numRows = columnValues.length; row < numRows; row++) {
    if (columnValues[row][0] === value) {
      sheet.getRange(sheet.getFrozenRows() + 1 + row, sortByColumn1).activate();
      break;
    }
  }
}

/**
 * This function Auto populates data from NEW LEADS to WHERE TO SUBMIT based on the checkbox in column H
 * 
 * @param {Object} e The onEdit() event object.
 */
function moveSheet1_(e) {
  var sheetsToCopy = 'sheet1';
  var sheet = e.range.getSheet();
  var editedRange = e.range;
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet2");
  
  if (!sheet.getName().match(sheetsToCopy)
      || e.range.columnStart > 8
      || 8 > e.range.columnEnd
      || e.range.rowEnd <= sheet.getFrozenRows()
      || e.range.getValue() !== true) {
    return;
  }
  showMessage_('Moving Data...', 2)
  var rowValues = sheet.getRange(editedRange.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
  var valuesToCopy = [rowValues[0], rowValues[1], rowValues[2]];
  var lastRow = targetSheet.getLastRow();
  var targetRange = targetSheet.getRange(lastRow + 1, 1, 1, 3);targetRange.setValues([valuesToCopy]);

  var checkboxesRange = targetSheet.getRange(lastRow+1, 4, 1, 4);
  var checkboxesRange1 = targetSheet.getRange(lastRow+1, 10, 1, 2)
  var formulaRange = targetSheet.getRange(lastRow+1, 13, 1, 1);
  checkboxesRange.insertCheckboxes();
  checkboxesRange1.insertCheckboxes();
  formulaRange.setFormula('=IF(L2:L<>"",IF(M2:M<>"",M2:M,NOW()),"")');
}

/**
 * This function Auto populates data from sheet2 to sheet3 based on the checkbox in column K
 * 
 * @param {Object} e The onEdit() event object.
 */
function moveSheet2_(e) {
  var sheetsToCopy = 'sheet2';
  var sheet = e.range.getSheet();
  var editedRange = e.range;
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet3");
  
  if (!sheet.getName().match(sheetsToCopy)
      || e.range.columnStart > 11
      || 11 > e.range.columnEnd
      || e.range.rowEnd <= sheet.getFrozenRows()
      || e.range.getValue() !== true) {
    return;
  }
  showMessage_('Moving Data...', 2)
  var rowValues = sheet.getRange(editedRange.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
  var valuesToCopy = [rowValues[0], rowValues[1], rowValues[2], rowValues[3], rowValues[4], rowValues[5], rowValues[6], rowValues[7], rowValues[8]];
  var lastRow = targetSheet.getLastRow();
  var targetRange = targetSheet.getRange(lastRow + 1, 1, 1, 9);targetRange.setValues([valuesToCopy]);

  var checkboxesRange = targetSheet.getRange(lastRow+1, 4, 1, 4);
  var checkboxesRange1 = targetSheet.getRange(lastRow+1, 10, 1, 2)
  var formulaRange = targetSheet.getRange(lastRow+1, 13, 1, 1);
  checkboxesRange.insertCheckboxes();
  checkboxesRange1.insertCheckboxes();
  formulaRange.setFormula('=IF(L2:L<>"",IF(M2:M<>"",M2:M,NOW()),"")');
}

/**
 * This function Auto populates data from sheet3 to sheet4 based on the checkbox in column H
 * 
 * @param {Object} e The onEdit() event object.
 */
function moveSheet3_(e) {
  var sheetsToCopy = 'sheet3';
  var sheet = e.range.getSheet();
  var editedRange = e.range;
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet4");
  
  if (!sheet.getName().match(sheetsToCopy)
      || e.range.columnStart > 11
      || 11 > e.range.columnEnd
      || e.range.rowEnd <= sheet.getFrozenRows()
      || e.range.getValue() !== true) {
    return;
  }
  showMessage_('Moving Data...', 2)
  var rowValues = sheet.getRange(editedRange.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
  var valuesToCopy = [rowValues[0], rowValues[1], rowValues[2]];
  var lastRow = targetSheet.getLastRow();
  var targetRange = targetSheet.getRange(lastRow + 1, 1, 1, 3);targetRange.setValues([valuesToCopy]);

  var checkboxesRange = targetSheet.getRange(lastRow+1, 4, 1, 4);
  var checkboxesRange1 = targetSheet.getRange(lastRow+1, 11, 1, 2)
  var formulaRange = targetSheet.getRange(lastRow+1, 14, 1, 1);
  checkboxesRange.insertCheckboxes();
  checkboxesRange1.insertCheckboxes();
  formulaRange.setFormula('=IF(M2:M<>"",IF(N2:N<>"",N2:N,NOW()),"")');
}

/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
  }
  moveSheet1_(e);
  moveSheet2_(e);
  moveSheet3_(e);
  autoSortSheet_(e);
  autoSortSheet1_(e);
}