Checkbox to Paste by last row in Column D

SCTJ 0
Viewed 31

Is it possible to:

  1. have a checkbox (J2) copy a result (J3) and paste contentsOnly into another cell (D13)
  2. to then have the same checkbox copy a different result (shown in J3) and paste contentsOnly into the cell below the previous (D14).
  3. lastly, loop step 2

.

Some of the searching and tried modifications:

var Col="A"; //Column that will find the last row not empty, can be assigned as parameter
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2');
var yourRange = sheet.getRange(Col + 1).offset(sheet.getLastRow()-1, 0); //+1 spare 1 row after last row

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 4);

Also looking at this link to try and Determining the last row in a single column and continue.

.

Please find editable sheet below: Trial - Checkbox to Paste by last row in Column D Picture Preview of Sheet

google-sheets google-apps-script
1 Answers

Modification points:

  • In your situation, how about using this sample script? Ref When this sample script is used, the last row of the specific column can be retrieved. By reflecting this script in your script, I thought that your goal might be able to be achieved.

  • And, about your script, I thought that when the event object is used, the process cost can be reduced. Ref

When these points are reflected in your script, how about the following modification?

Modified script:

function onEdit(e) {
  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var {range} = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != "Trail - Copy & Paste" || range.getA1Notation() != "J2" || !range.isChecked()) return;
  var lastRowOfColumnD = sheet.get1stNonEmptyRowFromBottom(4);
  lastRowOfColumnD = lastRowOfColumnD > 12 ? lastRowOfColumnD : 12;lastRowOfColumnD = lastRowOfColumnD > 12 ? lastRowOfColumnD : 12;
  sheet.getRange("J3").copyTo(sheet.getRange(lastRowOfColumnD + 1, 4), { contentsOnly: true });
}

  • In this modification, when the checkbox of "J2" of "Trail - Copy & Paste" sheet is checked, the script works. And, the value of "J3" is copied to the next row of the last row of column "D".

  • From your reply of however, once all the data in cell D13:D are cleared, having a hard time doing the first copy and paste into D13. Do you know of any workaround for this?, I understood that you wanted to set the initial row number of 13. So, I modified abve script.

Tanaike0
Related