CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Ahan Sahu
Ahan Sahu

Reputation: 1

Google Sheets Script: Getting values from each range in a rangelist in the same variable

I'm trying to get ranges from two different parts of the sheet and get their values to paste onto another sheet. The method I'm using works for the first range but not the next. I need values from both ranges to remain in the same variable since I need to filter it later.

var fromsh = SpreadsheetApp.openById(id).getSheetByName("Sheet1");
  var fromval = fromsh.getRangeList(['A3170:O', 'Y3170:Y']).getRanges()
  var values = [];
  for(var i = 0; i < fromval.length; i++){
    values = [].concat(values, fromval[i].getValues());
  }
  tosh = SpreadsheetApp.openById(id).getSheetByName("Sheet1");
  tosh.getRange(1, 1, values.length, values[0].length).setValues(values);

The error shown is: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 15.

Upvotes: 0

Views: 2581

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to put the values from the range of A3170:O and Y3170:Y.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Issue:

In your script, the number of columns is different between A3170:O and Y3170:Y. By this, an error occurs at tosh.getRange(1, 1, values.length, values[0].length).setValues(values).

Solution:

In order to avoid above issue, how about the following modifications?

Pattern 1:

In this pattern, your script was modified using Spreadsheet service. Pleas modify as follows.

From: 
var values = [];
for(var i = 0; i < fromval.length; i++){
  values = [].concat(values, fromval[i].getValues());
}
To: 
var obj = fromval.reduce(function(o, r) {
  var v = r.getValues();
  if (o.cols < v[0].length) o.cols = v[0].length;
  o.values = [].concat(o.values, v);
  return o;
}, {cols: 0, values: []});
var values = obj.values.map(function(e) {
  return e.length == obj.cols ? e : e = [].concat(e, Array.apply(null, new Array(obj.cols - e.length)).map(function(e) {return ""}));
});
  • Retrieve the max column number, and the length of each element of array is adjusted by adding "". Then, the values are put to the Spreadsheet.

Pattern 2:

In this pattern, your script was modified using Sheets API. Pleas modify as follows. Before you run this script, please enable Sheets API at Advanced Google services.

From: 
tosh = SpreadsheetApp.openById(id).getSheetByName("Sheet1");
tosh.getRange(1, 1, values.length, values[0].length).setValues(values);
To: 
Sheets.Spreadsheets.Values.update({values: values}, id, "Sheet1!A1", {valueInputOption: "USER_ENTERED"});
  • When Sheets API is used, the values can be put without adjusting the length of each element.
  • When you use this, please set id.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

From TheMaster's suggestion, I added one more sample script. In this sample script,

The values from the range A3170:O and Y3170:Y are summed for the column direction. For example, A3170:O3170 and Y3170.

For this situation, please modify as follows.

Pattern 3:

In this pattern, your script was modified using Spreadsheet service. Please modify as follows.

From: 
values = [].concat(values, fromval[i].getValues());
To: 
var temp = fromval[i].getValues();
values = i === 0 ? temp : values.map(function(e, i) {return [].concat(e, temp[i])});

Upvotes: 2

Related Questions