getLastRow but ignore column A and Logger assistance

Question

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.

Link to my test sheet is here

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 think that I can understand your 1st question. But I cannot understand your 2nd question. I apologize for my poor English skill. Can I ask you about the detail of your 2nd question? — Tanaike, Sep 23 '21 at 01:01
@Tanaike ty for the reply. For the second question, I'm basically looking for a way to "undo" what this code does. So right now this code will copy data from one row on one sheet to an open row on another sheet when a box is checked on the first sheet. I also want it to clear the data it added to that open row on the second sheet if I uncheck the box on the first sheet. — Chaz, Sep 23 '21 at 02:23
Thank you for replying. In your script, for example, when `onEdit1` is run continuously, the same values are put. Is this situation the result you expected? Because when the checkbox is unchecked, I'm not sure which is the data you want to clear. I apologize for my poor understanding. — Tanaike, Sep 23 '21 at 02:39
@Tanaike no worries at all. That's why I'm thinking I'd need a logger to log the first change that is made, so that I can reference that log to somehow undo it. Right now when I check the checkbox on the first page, it does copy the data from that row the checkbox is on to an open row on the second page. My hope is to add a logger function to that somehow (unless there is a better way), that would log that change that was made, so that if I uncheck the box on the first page it knows which row to clear on the second page (the row it added when the checkbox was checked initially) — Chaz, Sep 23 '21 at 02:44
Thank you for replying. In that case, you want to reverse to only the last situation operation by unchecking the checkbox. Is my understanding correct? — Tanaike, Sep 23 '21 at 02:48
@Tanaike I may still be explaining it poorly. Right now when I check the box on the first sheet, it copies the row that the checkbox is on to sheet 2 on the next open row. I want to keep that functionality, but if I uncheck the box on the first sheet, I want it to clear/delete the row it created on sheet2. — Chaz, Sep 23 '21 at 02:48
I have to apologize for my poor English skill again. For example, when the checkbox is checked at the 1st tab and the script is run 2 times, I think that your script adds 2 rows to the 2nd tab. Under this situation, when the checkbox was unchecked, how will you want to do it? Do you want to clear only the last row? Or do you want to clear all 2 rows? — Tanaike, Sep 23 '21 at 02:53
@Tanaike no need to apologize you're doing great. Scenario is this: On sheet1 when I check the box for A5 it copies B5, C5, and D5 to the next open row on sheet2. When I check the box for A6 it copies B6, C6, and D6 to the next open row on sheet2. When I uncheck A5 on sheet1, I want it to look on sheet2 for the data it copied previously and know to clear that row and only that row (the data from B5, C5, D5 that was copied over). — Chaz, Sep 23 '21 at 02:59
Thank you for replying. In your goal, when the checkbox of "A5" is unchecked, you want to clear all rows of `Stuff 5` in the 2nd tab when several rows of `Stuff 5` are existing. Is my understanding correct? — Tanaike, Sep 23 '21 at 03:02
By the way, when the rows of the 2nd tab are cleared, do you want to keep the empty row? — Tanaike, Sep 23 '21 at 03:09
@Tanaike pretty close. So if you're looking at my test sheet, if you click the checkbox for "A5" on the Video Course Checklist tab, it copies B5, C5, and D5 only to the next open row on sheet6 tab. That next open row is technically A11 (because I still haven't solved my first problem yet). It only copies B5, C5, and D5 when the checkbox is checked. So there will only be one row of `Stuff 1`. Every row will be unique if that helps. When I uncheck A5 on Video Course List, I want it to remember where it copied that data on Sheet6, and know to clear B11, C11, and D11. — Chaz, Sep 23 '21 at 03:11
@Tanaike I just realized it is copying multiple rows... I've made a huge mistake here. — Chaz, Sep 23 '21 at 03:13
@Tanaike I just changed to this to an "onChange" type trigger so that it only copies when I change that field, vs on Edit. I don't know if that will resolve my problem of duplicate rows but it seems to have for now. — Chaz, Sep 23 '21 at 03:17
Now I have 2 questions. 1. When the checkbox is checked and the script is run 2 times, you want to add only one row. You want to avoid the duplicated rows. Is my understanding correct? If my understanding is correct, what values do you want to check the duplicated rows? 3 values of "Course,KB Article,Due Date"? 2. When the rows of the 2nd tab are cleared, do you want to keep the empty row? — Tanaike, Sep 23 '21 at 03:20
@Tanaike 1.) I changed it from an onEdit, so the trigger is now a change. There will be no duplicate to worry about now. I fixed that. The values it should clear are under Course, KB Article, and Due Date. 2.) I was hoping it would do a `.clearContent() on the correct row in sheet2 so that it removes the content but keeps the formatting in sheet2 if that makes sense. — Chaz, Sep 23 '21 at 03:30
Thank you for replying. I proposed an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. — Tanaike, Sep 23 '21 at 04:37

score 2 · Accepted Answer · answered Sep 23 '21 at 04:37

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.
- Google Script - get last row of specific column

you're an amazing person! Thank you so much for your help. This works EXACTLY as I needed it to! Thank you so much! much appreciated! — Chaz, Sep 23 '21 at 14:51
I noticed one undesired side-effect, if you can tell me if there is any way around it or if not, that's okay I can try to figure something else out. As I uncheck boxes on the first sheet and items are removed and shifted up on the second sheet, the content that is shifted up loses its formatting (IE links to websites). — Chaz, Sep 23 '21 at 15:16
@Chaz Thank you for replying. I'm glad your issue was resolved. About your new question, in your sample Spreadsheet, the hyperlinks are not included in the cell. So my proposed script can be used for it. When you want to use the values including the hyperlinks at the cells, it is required to largely change the script. If you want the sample script, can you post it as a new question including a sample Spreadsheet? Because I think that in that case, the question is different from your this question and when the new question is created, it is also useful for other users. How about this? — Tanaike, Sep 24 '21 at 01:55

getLastRow but ignore column A and Logger assistance

1 Answers1

Modified script:

References: