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);
};
};
};
};
};
CodePudding user response：
I believe your goal is as follows.
- When the checkbox of column "A" of the 1st tab is checked, you want to copy the row of columns "B, C, D" to the 1st empty row of column "B" on the 2nd tab.
- When the checkbox of column "A" of the 1st tab is unchecked, you want to clear the rows on the 2nd tab. You want to check the values of the columns "B, C, D" on the 2nd tab.
In this case, when your script is modified, how about the following modification?
Modified script:
// 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);
};
};
};
};
- In order to retrieve the 1st empty row of column "B" of the 2nd sheet, I used the sample script from https://stackoverflow.com/a/44563639/7108653
- In order to clear the rows when the checkbox is unchecked, at first, retrieve the existing values and check the duplicated rows, and overwrite the existing sheet with the new values.
References:
- filter()
- Related thread.