CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Robert Ross
Robert Ross

Reputation: 1189

Getting Incorrect range height for seemingly no reason?

I am writing a script to copy and paste a range from one sheet to another. The pasted range size should be reduced by using two functions : one to delete rows with specific values and the other is an aggregate function.

I started getting this error after I introduced the aggregate function The function is basically reducing the array size using the reduce JS function.

I have replicated my problem here and the code is accessible in the script editor.

When I run the script I am getting the following error :

Incorrect range height was 28 but should be 7 (line 36, file "test")

I have no idea why am I getting this error. My aggregate function returns a properly formatted array with the right length.

 function append_range(){    


       var origin_sheet = SpreadsheetApp.openById('1-2ZheMz1p01qwtwY3ghbNjJedYfGXeylnLEjDMCLpMw');//open the file
       origin_sheet = origin_sheet.getSheetByName('test');
       var rangeStart = 2;

       var range = origin_sheet.getRange('A'+ (rangeStart.toString())+':T'+ (origin_sheet.getLastRow()).toString());


      var dataFromRange = range.getValues();
      var dataFromRangeLength = dataFromRange.length;


      var destination_sheet = SpreadsheetApp.openById('1-2ZheMz1p01qwtwY3ghbNjJedYfGXeylnLEjDMCLpMw');
       destination_sheet = destination_sheet.getSheetByName('append');

      var rowLast = destination_sheet.getLastRow()+1;
      Logger.log("row last" + rowLast);
      var formattedRange = deleteRows(dataFromRange);

       var groups = aggregate(formattedRange);

       var aggregates = [];

       for(var group in groups)
       {
        aggregates.push(groups[group]);
       }    

           Logger.log(aggregates);
      var formattedRangeLength = aggregates.length;
         Logger.log("formattedRangeLength" + formattedRangeLength);


      destination_sheet.getRange(rowLast,1,formattedRangeLength, 20).setValues(deleteRows(dataFromRange));





function isDate(sDate) {
  if (isValidDate(sDate)) {
    sDate = Utilities.formatDate(new Date(sDate), "PST", "yyyy-MM-dd");
  }
  return sDate;
}

function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}    
//
function deleteRows(dataRange){//just pass the range in an array and this method will return another array with filtered range

  var formatted = dataRange.filter(function(e) {
    return e[8]||e[9]||e[10]||e[11]||e[12]||e[13]||e[14]||e[15]||e[16]||e[17]||e[18]||e[19];
  });
 return formatted;

}

   function aggregate(data)
    {

       var groups = data.reduce(
            function(accumulator, previous){
            {
                var key = previous[1] + previous[3] + previous[5] + previous[6];
                var group = accumulator[key];
                if(group == null || typeof group == 'undefined')
                {
                    accumulator[key] = previous;
                }
                else {
                    var startIndex = 8;
                    for(var i = startIndex; i < previous.length;i++)
                    {
                        group[i] += previous[i];
                    }
                }

               return accumulator;
            }},
            {});
        return groups;
    }



}

Upvotes: 0

Views: 254

Answers (2)

Cooper
Cooper

Reputation: 64082

I think this might work:

var output=deleteRows(dataFromRange));
destination_sheet.getRange(rowLast,1,output.length, output[0].length).setValues(deleteRows(output));

This assumes a non jagged array.

Upvotes: 0

random-parts
random-parts

Reputation: 2225

The .setValues() is not setting your aggregates array it is trying to set deleteRows(dataFromRange)

// Change the setValues() to your reduced array
 destination_sheet.getRange(rowLast,1,formattedRangeLength, 20).setValues(aggregates);

Upvotes: 1

Related Questions