CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
jarelcorpuz
jarelcorpuz

Reputation: 3

Is there a way to merge multiple range into a single range for sheets appscript?

I was trying to merge 2 ranges(WITH values) into 1 range(empty)

I was trying to do it like this

function regressionMaker(){

  var app = SpreadsheetApp;
  var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

  var name = activeSheet.getRange ("E2:E").getValues();
  var title = activeSheet.getRange ("F2:F").getValues();

  var x = " - ";

  activeSheet.getRange("T2:T").setValues(name + x + title);
}

All I get is an error saying "Cannot find method setValues(string)"

I was expecting of having a result of

Column 1     Column 2              Column3

Apple           Banana                 Apple - Banana

Updates


  var name = activeSheet.getRange ("E2:E").getValues();
  var title = activeSheet.getRange ("F2:F").getValues();


  var merged = name.map(

    function(nameValues,rowIndex){

    var titleValues = title[rowIndex];
    return [nameValues[0] + ' - ' + titleValues[0]];
    }
  );

  activeSheet.getRange("V2:V").setValues(merged);

Tho nothing happened :(

Upvotes: 0

Views: 1564

Answers (1)

0Valt
0Valt

Reputation: 10345

Problem 1.1

You are trying to pass a String to the setValues() method which can only be passed a two-dimensional Array (that is, Array of Arrays). Error message states just that: "there is no method setValues() such that it accepts String as an argument".

Problem 1.2

getValues() method yields an Array of Arrays as well, so your names and title variables actually contain [['Apple']] and [['Banana']] respectively (assuming your ranges include values only).

Problem 1.3

JavaScript is dynamically typed, so it performs type coercion for you and it often leads to issues like you have. In the case of +, if any argument is of type String, JS will try to coerce the other one to a String (e.g. '11' + 2 = '112').

Solution

The abovementioned problems should lead you to an algorithm: "map each row of data in column 1 to a column 2 such that the result is a string concatenation of both values separated by a hyphen with two whitespaces".

Application

Since you do a one-column merge, all you need is to get the first value from column 1, the first value from column 2, concatenate them and wrap into an Array representing new cell.

var merged = names.map(function(nameValues,rowIndex){
  var titleValues = titles[rowIndex];
  return [nameValues[0] + ' - ' + titleValues[0]];
});

Upvotes: 2

Related Questions