-2

After the result of Checkbox to Paste by last row in Column D would like to attempt to do the same though for last column in a row.

The following script put together using Google Developers - copyTo(destination, options):

function onEdit(event) {
  // assumes source data in sheet named main
  // target sheet of move to named Completed
  // getColumn with check-boxes is currently set to column 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Sh1" && r.getColumn() == 2 && r.getValue() == true) {
    var rangeToCopy = s.getRange(4, 2, s.getMaxRows(), 2);
    rangeToCopy.copyTo(s.getRange(4, 5), {contentsOnly:true});
  }
}

returns this:
First Copy and Paste

.

Is there a way to have the same checkbox copy the maxRows (depending on the results in B4:C14) and paste them in the next available section as shown below for 2nd and 3rd Paste (etc): Second Copy and Paste Third Copy and Paste

.

.

Have tried to alter the formula provided in Checkbox to Paste by last row in Column D but have not been able to.

The Sample Sheet

Thank you

Stephanie
  • 55
  • 6
  • 1
    It looks like you copied this code from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, you can [edit], include a [link](/editing-help#links) to the source, mention the author's name, and [quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). – TheMaster Oct 04 '22 at 18:26
  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! – TheMaster Oct 04 '22 at 18:27
  • If I understand correctly you want that every time there is a change in C1 and you press the button, it pretty much copies the new values into a different range, correct? – Fernando Lara Oct 04 '22 at 19:30
  • Hello @FernandoLara, yes correct, though it would be the next available range. – Stephanie Oct 04 '22 at 19:44
  • Are you planning to use an endless amount of ranges every time, like with every copy you just use the next available range and so on, or do you have like a specific limit? – Fernando Lara Oct 04 '22 at 19:50
  • @FernandoLara Ideally not looking for more than around 35 ranges. – Stephanie Oct 04 '22 at 19:57
  • Around 35 is not a good answer for a programmer. We need to know exactly what you want. So far it is clear to me that you have a considerable amount of difficult explaining yourself. Please provide us with specific example of what your starting with and what you wish the final outcome to look like. Also please provide the same for your other question – Cooper Oct 04 '22 at 20:06
  • @SCTJ Do you have any specific criteria on how the range will be selected with every run? Like, is it just going to move automatically from one range to the next one, or are you planning to reset the range at some point based on a specific criteria? – Fernando Lara Oct 04 '22 at 20:54
  • @FernandoLara, yes - it would automatically move from one range to the next. Once the desired range is reached (35 or less) the range in use will clear to start over from fresh as shown above. – Stephanie Oct 04 '22 at 22:12

1 Answers1

1

The solution for your problem could be the following:

Script:

//*****
function onEdit(event) {
  // assumes source data in sheet named main
  // target sheet of move to named Completed
  // getColumn with check-boxes is currently set to column 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Sh1" && r.getColumn() == 2 && r.getValue() == true) {
    var rangeToCopy = s.getRange(4, 2, s.getMaxRows(), 2);
    var c = s.getRange("F1").getValue();
    rangeToCopy.copyTo(s.getRange(4, c), {contentsOnly:true});
    if(c+3<=11) // 11 was my maximum column number for my testing
                //but you can use any other value depending on
                //how many copies you want to have.
                //For each copy you must add 3 columns.
    {
      s.getRange("F1").setValue(c+3);
    }
    else
    {
      s.getRange("F1").setValue(5); // Resets the value to column E
    }
  }
}

What the script does is that it uses a helper value in cell F1 (you can use any other cell that you prefer) to read the starting cell of the range you will copy the values to. Every time you click the box it reads the range, then copies the values, and automatically changes the value of the helper cell to the next starting column. It also checks if you have reached the column limit to reset the value of the helper cell.

Fernando Lara
  • 2,263
  • 2
  • 4
  • 14