Is it possible to:
- have a checkbox (J2) copy a result (J3) and paste
contentsOnly
into another cell (D13) - to then have the same checkbox copy a different result (shown in J3) and paste
contentsOnly
into the cell below the previous (D14). - 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
CodePudding user response:
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.