Google Apps Script. Cannot set value in range because number of rows in the data does not match the number of rows in the range

Question

Task was to find word 'Request' in a specific range (variable "range"), copy information from nearest column on rows which match 'Request', then paste it to another range.

Cannot figure out how to insert value of variable "offsetval1" in multiple cells. Seems like it returns 9 values, but if I check using Logger.log(offsetval1.length); it returns 1.0. However, there are 9 rows with different values (check screenshot).

How to input values of "offsetval1" in range with these 9 rows?

function DocReq() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please, enter Line Count"); // Prompt Input
  var line_count = result.getResponseText(); // Prompt Result (Number) as VAR
  var int_line_count = (+line_count); // Number as Integer
  var range_bottom = int_line_count-1; // Integer - 1 (Total Amount of Rows)

  var spreadsheet = SpreadsheetApp.getActive();
  var currect_cell = spreadsheet.getActiveCell().getRow(); // Row of Active Cell
  var int_current_cell = (+currect_cell); // Row as Integer (Top First Row)

  var main_range_bottom = int_current_cell+range_bottom; // Bottom Last Row
 
  var range = spreadsheet.getRange('L' + int_current_cell + ':' + 'L'+ main_range_bottom); // Get Range All !Requested!

  var find_rqst = range.createTextFinder('Requested').findAll(); // Creating Text Finder !Requested!

  for (var i = 0; i < find_rqst.length; i++) { // Finding Exceptionally !Requested!
    var arrayofreq = find_rqst[i];

    var offsetval1 = arrayofreq.offset(0, -4).getValues(); // Getting Values of !H! Column of All !Requested!
    Logger.log(offsetval1);
    }
    Logger.log(offsetval1.length);
    Logger.log(find_rqst.length);

  spreadsheet.getRange('K131:K139').setValues(offsetval1);
}

Answer 1

Issue:

The issue with the code is that offsetval1 is defined iteratively inside the for loop and therefore it always contains one value, this is why the length is 1. You are actually overwrite the previous content with the new one.

• Try to store each value to an array and then set the array instead.

Solution:

function DocReq() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please, enter Line Count"); // Prompt Input
  var line_count = result.getResponseText(); // Prompt Result (Number) as VAR
  var int_line_count = (+line_count); // Number as Integer
  var range_bottom = int_line_count-1; // Integer - 1 (Total Amount of Rows)

  var spreadsheet = SpreadsheetApp.getActive();
  var currect_cell = spreadsheet.getActiveCell().getRow(); // Row of Active Cell
  var int_current_cell = (+currect_cell); // Row as Integer (Top First Row)

  var main_range_bottom = int_current_cell+range_bottom; // Bottom Last Row
 
  var range = spreadsheet.getRange('L' + int_current_cell + ':' + 'L'+ main_range_bottom); // Get Range All !Requested!

  var find_rqst = range.createTextFinder('Requested').findAll(); // Creating Text Finder !Requested!
  var offsetval1 = []; // Modified code

  for (var i = 0; i < find_rqst.length; i++) { // Finding Exceptionally !Requested!
    var arrayofreq = find_rqst[i];
    offsetval1.push([arrayofreq.offset(0, -4).getValue()]); // Modified code
    }

  spreadsheet.getRange('K131:K139').setValues(offsetval1);
}