I found this excellent script by --Hyde on support.google.com called moveRowsFromSpreadsheetToSpreadsheet, which enables the transfer of Google Sheet rows between a target sheet and a destination sheet, based on a value set in a dropdown cell for each row in the target sheet.
The challenge i'm facing is that the script is copying & pasting values only for each cell, not the formulas inside those cells.
My spreadsheet contains cells with =IMAGE() formulas and =HYPERLINK() formulas and these cells are transferred either empty (in cells where I have =IMAGE() formulas) or non-hyperlinked values only (in cells where I have =HYPERLINK() formulas).
My hope is, if at all possible, to find a way to modulate the script so it will copy & paste the rows as they are, with formulas and all, and not just values only for each cell. Similar to the manual copying & pasting of cell ranges.
I'm guessing it has to do with the targetRange.setValues([values]); class in line 269 which probably should have used the targetRange.copyTo(destination, options); class. I tried to change the class but it returned the following error: "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.copyTo. Code line 270"
Here is the script:
try {
// START Modifiable parameters
var sheetsToWatch = ['Incoming'];
var columnsToWatch = ['Review'];
var valuesToWatch = ['ignore','threat','defamatory'];
var targetSheets = ['Ignored','Threats','Defamation'];
var targetSpreadheets = ['18Cj0oSroQJ_c_4exPK03ubqUd3Fpr8o1M1lzebsyD9g','18Cj0oSroQJ_c_4exPK03ubqUd3Fpr8o1M1lzebsyD9g','18Cj0oSroQJ_c_4exPK03ubqUd3Fpr8o1M1lzebsyD9g'];
var targetIdColumn = ['Review'];
var targetValues = [];
var copyInsteadOfMove = [false, false, false];
var numColumnsToMove = [];
var changeColumnOrderTo = [];
var sheetsToSort = [];
var columnToSortBy = [];
var sortAscending = [];
// END modifiable parameters
} catch (error) {
showAndThrow_(error);
}
function moveRowsFromSpreadsheetToSpreadsheet_(e) {
if (!e || !e.range) {
return;
}
var sourceSheet = e.range.getSheet();
var sourceSheetName = sourceSheet.getName();
if (sheetsToWatch.indexOf(sourceSheetName) === -1) {
return;
}
var columnLabelRow = sourceSheet.getFrozenRows() || 1;
var numSheetColumns = sourceSheet.getLastColumn();
var columnLabels = sourceSheet.getRange(columnLabelRow, 1, 1, numSheetColumns).getValues()[0];
if (targetIdColumn.length && targetValues.length) {
var targetIdColumnNumber = columnLabels.indexOf(targetIdColumn[0]) + 1;
if (!targetIdColumnNumber) {
throw new Error('Could not find a column named "' + String(targetIdColumn[0]) + '".');
}
}
for (var i = 0, numValuesToWatch = valuesToWatch.length; i < numValuesToWatch; i++) {
valuesToWatch[i] = String(valuesToWatch[i]).toLowerCase();
}
var values = e.range.getDisplayValues();
var rowsToDelete = [];
var numRowsMoved = 0;
var messageOnDisplay = false;
for (var row = 0, numRows = values.length; row < numRows; row++) {
for (var column = 0, numColumns = values[row].length; column < numColumns; column++) {
if (e.range.rowStart + row <= columnLabelRow) {
continue;
}
var columnLabel = columnLabels[e.range.columnStart - 1 + column];
if (columnsToWatch.indexOf(columnLabel) === -1) {
continue;
}
var valueLowerCase = values[row][column].toLowerCase();
var valuesToWatchIndex = valuesToWatch.indexOf(valueLowerCase);
if (valuesToWatchIndex === -1) {
continue;
}
if (targetIdColumn.length && targetValues.length) {
var valueInTargetIdColumn = sourceSheet.getRange(e.range.rowStart + row, targetIdColumnNumber).getValue();
var targetIndex = targetValues.indexOf(valueInTargetIdColumn);
} else {
targetIndex = valuesToWatchIndex;
}
if (targetIndex === -1) {
continue;
}
// all checks done, there is at least one row to move
var movedRowIndex = e.range.rowStart + row;
if (!messageOnDisplay) {
showMessage_('Moving rows...', 30);
messageOnDisplay = true;
}
var sourceRange = sourceSheet.getRange(e.range.rowStart + row, 1, 1, numSheetColumns);
var rowValuesInOriginalOrder = sourceRange.getValues()[0];
if (numColumnsToMove[targetIndex] !== undefined) {
var rowValues = rowValuesInOriginalOrder.slice(0, numColumnsToMove[targetIndex]);
} else {
rowValues = rowValuesInOriginalOrder.slice();
}
for (var changeIndex = 0; changeIndex < changeColumnOrderTo.length; changeIndex++) {
if (changeColumnOrderTo[changeIndex] !== undefined) {
rowValues[changeIndex] = rowValuesInOriginalOrder[changeColumnOrderTo[changeIndex]];
} else if (rowValues[changeIndex] === undefined) {
rowValues[changeIndex] = null;
}
}
if (targetSpreadheets[targetIndex]) {
try {
var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadheets[targetIndex]);
} catch (error) {
var ssIdShortened = String(targetSpreadheets[targetIndex]).slice(0, 5) + '...' + String(targetSpreadheets[targetIndex]).slice(-5);
throw new Error("Could not find the target spreadsheet with ID '" + ssIdShortened + "'.");
}
} else {
targetSpreadsheet = sourceSheet.getParent();
}
var targetSheet = targetSpreadsheet.getSheetByName(targetSheets[targetIndex]);
if (!targetSheet) {
throw new Error("Could not find the target sheet '" + targetSheets[targetIndex] + "'.");
}
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, rowValues.length);
targetRange.setValues([rowValues]);
numRowsMoved += 1;
if (!copyInsteadOfMove[targetIndex]) {
rowsToDelete.push(e.range.rowStart + row);
}
} // column
} // row
if (messageOnDisplay) {
var message = (rowsToDelete.length ? 'Moved ' : 'Copied ') + numRowsMoved + (numRowsMoved === 1 ? ' row.' : ' rows.');
showMessage_('Moving rows... done. ' + message);
}
if (rowsToDelete.length) {
for (var i = rowsToDelete.length - 1; i >= 0; i--) {
sourceSheet.deleteRow(rowsToDelete[i]);
}
}
return { sourceSheet: sourceSheet, targetSheet: targetSheet };
}
function installOnEditTrigger() {
ScriptApp.newTrigger('moveRowsAndSortSheet_')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
