I'm trying to achieve two goals with the code below, but I'm running into a hurdle with one item, and not sure where to begin with the next.
Goal #1: I'm trying to find a way so that getLastRow ignores column A on my destination sheet. Column A will always be filled with a checkbox so I'm hoping to just copy data from the source sheet into the destination sheet starting at column 2. No matter what I do, it either prints starting in column A, or with the current setup if I get it to print starting in Column B, I can only do that if I remove the checkbox from A. I've tried several of the solutions listed here in Stackoverflow but none of them provided a result that skipped column A when using getLastRow so I must be doing something wrong.
Goal #2: Need tips on the best way to set up Logger in a way that would allow me to record which data is copied to which destination row, so that if the checkbox on the source page is unchecked, it clears the line on the destination page that this data was copied to.
I was going to duplicate my code below a bit and simply change some of the variables but realized that would likely always delete rows during an edit if it sees those checkboxes as false. That's why I figure I need to use a Logger but super novice to that. Any thoughts? Suggestions?
// Names of sheets
var sourceSheet = "Video Course Checklist"
var destinationSheet = "Sheet6"
/* col: the column to watch,
* changeVal: what value you want to change,
* del: do you want to delete after the change?
*/
var check = {
"col":1,
"changeVal": true,
"del": false
};
/* What you want to paste into the other sheet.
* start: start column
* cols: how many columns you want to copy
*/
var pasteRange = {
"start": 2,
"cols": 3
};
function onEdit1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
if(sheet.getName() === sourceSheet){
//Get active cell
var cell = sheet.getActiveCell();
var cellCol = cell.getColumn();
var cellRow = cell.getRow();
if(cellCol === check.col){
if(cell.getValue() === check.changeVal){
//Select the range you want to export
var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
//Select the past destination
var pasteDestination = ss.getSheetByName(destinationSheet);
var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;
//Copy the row to the new destination
exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,2),
SpreadsheetApp.CopyPasteType.PASTE_NORMAL);
//If delete is true delete after copying
if(check.del){
sheet.deleteRow(cellRow);
};
};
};
};
};
I believe your goal is as follows.
In this case, when your script is modified, how about the following modification?
// Names of sheets
var sourceSheet = "Video Course Checklist"
var destinationSheet = "Sheet6"
/* col: the column to watch,
* changeVal: what value you want to change,
* del: do you want to delete after the change?
*/
var check = {
"col": 1,
"changeVal": true,
"del": false
};
/* What you want to paste into the other sheet.
* start: start column
* cols: how many columns you want to copy
*/
var pasteRange = {
"start": 2,
"cols": 3
};
// This sample script is from https://stackoverflow.com/a/44563639/7108653
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
const range = this.getRange(offsetRow, columnNumber, 2);
const values = range.getDisplayValues();
if (values[0][0] && values[1][0]) {
return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
} else if (values[0][0] && !values[1][0]) {
return offsetRow + 1;
}
return offsetRow;
};
function onEdit1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
if (sheet.getName() === sourceSheet) {
//Get active cell
var cell = sheet.getActiveCell();
var cellCol = cell.getColumn();
var cellRow = cell.getRow();
if (cellCol === check.col) {
var cellValue = cell.getValue();
//Select the range you want to export
var exportRange = sheet.getRange(cellRow, pasteRange.start, 1, pasteRange.cols);
//Select the past destination
var pasteDestination = ss.getSheetByName(destinationSheet);
if (cellValue === check.changeVal) {
// var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;
var pasteEmptyBottomRow = pasteDestination.get1stEmptyRowFromTop(2);
//Copy the row to the new destination
exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow, 2),
SpreadsheetApp.CopyPasteType.PASTE_NORMAL);
//If delete is true delete after copying
if (check.del) {
sheet.deleteRow(cellRow);
};
} else if (cellValue !== check.changeVal) {
var [a, b, c] = exportRange.getValues()[0];
var existingDataRange = pasteDestination.getRange("B2:D" + pasteDestination.getLastRow());
var newData = existingDataRange.getValues().filter(([aa, bb, cc]) => !(aa == a && bb == b && cc == c));
existingDataRange.clearContent();
pasteDestination.getRange(2, 2, newData.length, 3).setValues(newData);
};
};
};
};