I had assistance building this trigger a while back, where "When you select a check box on 'sheet 2', cut all associated rows into 'sheet 1'. Code for this below in the first block. This stopped working when I added the second snippet below that is in a separate script file, not tied within the first one but needs to work actively on the same sheet. Any ideas why this is happening and what the fix would be?
Here is a sample sheet for reference if needed - Sample Sheet
/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
if (!e) {
throw new Error(
'Please do not run the onEdit(e) function in the script editor window. '
+ 'It runs automatically when you hand edit the spreadsheet.'
+ 'See https://stackoverflow.com/a/63851123/13045193.'
);
}
moveRowsByUniqueId_(e);
}
/**
* Triggers on a checkbox click and moves rows that match a unique ID.
*
* @param {Object} e The onEdit() event object.
*/
function moveRowsByUniqueId_(e) {
let sheet;
if (e.value !== 'TRUE'
|| e.range.rowStart <= 3
|| e.range.columnStart !== 12
|| (sheet = e.range.getSheet()).getName() !== 'Advance') {
return;
}
e.source.toast('Moving rows...');
const uniqueId = e.range.offset(0, -1).getValue();
const range = sheet.getRange('A4:H');
const values = range.getValues();
const targetSheet = e.source.getSheetByName('Active Processing');
const _matchWithId = (row) => row[0] === uniqueId;
const valuesToAppend = values.filter(_matchWithId);
if (uniqueId && valuesToAppend.length) {
appendRows_(targetSheet, valuesToAppend, 1);
range.clearContent();
const remainingValues = values.filter((row) => !_matchWithId(row));
range.offset(0, 0, remainingValues.length, remainingValues[0].length)
.setValues(remainingValues);
e.source.toast(`Done. Moved ${valuesToAppend.length} rows.`);
} else {
e.source.toast('Done. Found no rows to move.');
}
e.range.setValue(false);
}
/**
* Appends an array or a value to the bottom of a sheet starting in
* the given column after the last row where the column is blank.
*
* When optColumn is provided, appends data starting in that column
* after the last row where that column has visible content.
*
* When optColumn is not provided, appends data starting in column A
* after the last row that has visible content in any column.
*
* Use Range.getRichTextValues() to preserve text formatting such as bold,
* italic, underlined, font and text colors when appending textual data.
* To append numbers, dates and plain text, use Range.getValues().
*
* @param {SpreadsheetApp.Sheet} sheet A spreadsheet sheet to append the data to.
* @param {Object[][]} data The plain or formatted data to append: a 2D array, a 1D array or a single value.
* @param {Number} optColumn Optional. The column number where to start appending the data. Defaults to 1.
* @return {Object} An object with fields {Range} range, {Number} rowStart, columnStart, numRows, numColumns.
*/
function appendRows_(sheet, data, optColumn) {
'use strict';
// version 1.10, written by --Hyde, 15 June 2022
if (!Array.isArray(data)) {
data = [[data]];
} else if (!Array.isArray(data[0])) {
data = [data];
}
let action = 'setValues';
if (data.some(row => row.some(value => value.getRuns))) {
action = 'setRichTextValues';
data = data.map(row => row.map(value => value.getRuns
? value
: SpreadsheetApp.newRichTextValue().setText(value).build()
));
}
const rowStart = getLastRow_(sheet, optColumn) + 1;
const columnStart = Number(optColumn) || 1;
const numRows = data.length;
const numColumns = data[0].length;
const range = sheet.getRange(rowStart, columnStart, numRows, numColumns);
range[action](data);
return { range: range, rowStart: rowStart, columnStart: columnStart, numRows: numRows, numColumns: numColumns };
}
/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
// version 1.5, written by --Hyde, 4 April 2021
const values = (
columnNumber
? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
: sheet.getDataRange()
).getDisplayValues();
let row = values.length - 1;
while (row && !values[row].join('')) row--;
return row + 1;
}function onEdit(e) {
var range = e.range;
var editor = e.user.getEmail();
const CURRENT_DATE= new Date();
if (editor == '' || editor == null) {
editor = 'Unknown';
}
editor = editor.split("@")[0];
if (range.getValue() >= 1111111 & range.getValue() <= 9999999) {
if (range.getA1Notation().search('L') != -1) {
range.offset(0, -2).setValue(editor);
range.offset(0, 1).setValue(CURRENT_DATE);
};
};
}
