2

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

Stephanie
  • 55
  • 6
  • So just to confirm, cell D13 should be static, and the same value should be pasted below D13 (meaning on D14), and if another value is pasted, it will change the value on D13, and the next value should be pasted on D15, is that correct? – Century Tuna Sep 22 '22 at 23:41
  • Hello @DiegoSanchez, thanks for your reply. The formula input in J3 returns different values and is the only cell that will change. After the first paste to D13 the value in J3 changes and that will be pasted to D14. Once again, the value in J3 will change and be pasted to D15 and so on. Totalling in 6 copy and pasting sequences for the above example. Hope this answers your query. – Stephanie Sep 23 '22 at 00:40
  • See [Should I use tags in titles?](/help/tagging) – TheMaster Sep 23 '22 at 14:21

1 Answers1

2

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hello @Tanaike, thank you for your reply and your answer does steps 2 and 3 of the question above; 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? – Stephanie Sep 23 '22 at 01:06
  • @SCTJ Thank you for replying. From your reply, I updated my sample script. Could you please confirm it? In order to set the initial row number, I added `lastRowOfColumnD = lastRowOfColumnD > 12 ? lastRowOfColumnD : 12;lastRowOfColumnD = lastRowOfColumnD > 12 ? lastRowOfColumnD : 12;`. – Tanaike Sep 23 '22 at 01:11
  • this is what I was looking for. Thank you for your time and additional information to look into. Still a beginner-ish, so much appreciated. – Stephanie Sep 23 '22 at 01:20
  • @SCTJ Thank you for replying and testing it, again. I'm glad your issue was resolved. Thank you, too. – Tanaike Sep 23 '22 at 01:21