Ive been messing with this google apps script for far too long and need some help.
I have a table on a sheet called options that starts on col A line 31 and is 3 col wide. Col a is all checkboxes. I was able to write a script that checks to see which checkboxes are checked.

For each checked box it copies that rows data in b:c into an array. Then opens an existing tab called Worksheet and is supposed to paste them in the first empty cell it finds in column b.

   function createNamedRanges() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Worksheet");
    var range = sheet.getRange("B2:C");
    var namedRange = ss.setNamedRange("outputRange", range);}
    
    function processSelectedRows() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prompt Options");
    var data = sheet.getDataRange().getValues();
    var checkedRows = [];
    for (var i = 30; i < data.length; i  ) {
     var row = data[i];
     var checkbox = sheet.getRange(i   1, 1).getValue() == true;
     if (checkbox){
      checkedRows.push([row[1], row[2]]);
     } }
    var worksheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Worksheet");
    var pasteRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("outputRange");
    pasteRange.offset(worksheet.getLastRow(), 0).setValues(checkedRows);
    }

The first row on the worksheet tab are headers. The first array to copy over is 11 rows. When I ran the script. I got an error that sat there was only 1 row in the range and I had 11 rows of data. Ok, I figured I neeeded to name a range. This table will be a different size every time. So I named this range outoutRange and no matter what size i make it I get error messages.

This is my latest error message and it is hitting the very last line of code Exception: The number of rows in the data does not match the number of rows in the range. The data has 11 but the range has 1007.

You assistance is appreciated

CodePudding user response：

Modification points:

• If your Worksheet is the default grid like 1000 rows and 26 columns, I think that pasteRange is all rows like 1000. I thought that this might be the reason for your current issue.

In order to retrieve the last row of the columns "B" and "C" of "Worksheet" sheet, how about the following modification?

From:

pasteRange.offset(worksheet.getLastRow(), 0).setValues(checkedRows);

To:

var lastRow = pasteRange.getLastRow() - pasteRange.getDisplayValues().reverse().findIndex(([b, c]) => b && c);
worksheet.getRange(lastRow   1, 2, checkedRows.length, checkedRows[0].length).setValues(checkedRows);

• By this modification, the values of checkedRows is put to the next row of the last row of columns "B" and "C" of "Worksheet" sheet.