getValues is not a function : How to add multiple ranges in google app script? How to implement it to the code?

Question

I am trying to sum data from different sheets to one that will make the total of this. When I run it, that make a sum of numbers and a sum of string :

function myFunction() {

  var sheet_origin1=SpreadsheetApp
                      .openById("ID1")
                      .getSheetByName("Calcul/Machine");
  var sheet_origin2=SpreadsheetApp
                      .openById("ID2")
                      .getSheetByName("Calcul/Machine"); 
  var sheet_destination=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  //define range as a string in A1 notation
  var range="BS4:FR71"  
  var values1=sheet_origin1.getRange(range).getValues();
  var values2=sheet_origin2.getRange(range).getValues(); 
  var destinationrange=sheet_destination.getRange("A4:CZ71");

  //Here you sum the values of equivalent cells from different sheets
  for(var i=0; i<values1.length;i++)
  {
    for(var j=0; j<values1[0].length;j++)
    {
      sum=values1[i][j]+values2[i][j];
      destinationrange.getCell(i+1,j+1).setValue(sum);
    }
  } 
}

So to make a sum of cells that i want and to avoid to sum strings i wanted to add different ranges in my "range" variable :

function myFunction() {

  var sheet_origin1=SpreadsheetApp
                      .openById("ID1")
                      .getSheetByName("Calcul/Machine");
  var sheet_origin2=SpreadsheetApp
                      .openById("ID2")
                      .getSheetByName("Calcul/Machine"); 
  var sheet_destination=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  //define range as a string in A1 notation
  var range=(["BU4:CF71", "CJ4:CU71"])  
  var values1=sheet_origin1.getRangeList(range).getValues();
  var values2=sheet_origin2.getRangeList(range).getValues(); 
  var destinationrange=sheet_destination.getRange("A4:CZ71");

  //Here you sum the values of equivalent cells from different sheets
  for(var i=0; i<values1.length;i++)
  {
    for(var j=0; j<values1[0].length;j++)
    {
      sum=values1[i][j]+values2[i][j];
      destinationrange.getCell(i+1,j+1).setValue(sum);
    }
  } 
}

Now I have this error : getValues is not a function.

So how can i use the .getRangeList() function in this situation ? And by what i have to replace getValues() ?

Thx for your help

Answer 1

Description

I've taken the liberty of modifying your script using a single spreadsheet multiple sheets and the getRangeList() method. You will need to examine and learn from this script and implement in your code.

getRangeList() returns a RangeList object from which you need to getRanges() which returns an Array of ranges.

Code.gs

function myTest() {

  let spread = SpreadsheetApp.getActiveSpreadsheet();
  let sheet_origin1 = spread.getSheetByName("Sheet1");
  let sheet_origin2 = spread.getSheetByName("Sheet2");
  let sheet_destination = sheet_origin2;

  //define range as a string in A1 notation
  var ranges=["A1:C4", "A6:C9"]; 
  var destinationrange=sheet_destination.getRange("E1:G4");
  var sum = [];
  var ranges1 = sheet_origin1.getRangeList(ranges).getRanges();
  var ranges2 = sheet_origin2.getRangeList(ranges).getRanges();
  
  ranges1.forEach(
    function(range,index) {
      var values1=range.getValues();
      var values2=ranges2[index].getValues(); 

      //Here you sum the values of equivalent cells from different sheets
      for(var i=0; i<values1.length; i++) {
        if( sum[i] ===  undefined ) sum[i] = [];
        for(var j=0; j<values1[0].length; j++) {
          if( sum[i][j] === undefined ) sum[i][j]=0;
          sum[i][j]=sum[i][j]+values1[i][j]+values2[i][j];
        }
      }
    }
  );
  destinationrange.setValues(sum);
}

Reference

• RangeList

Debug for comment

  let dum = sheet_origin1.getRange("BU4:CF71");
  console.log("BU4:CF71: rows = "+dum.getNumRows()+" cols = "+dum.getNumColumns());
  dum = sheet_origin1.getRange("CJ4:CU71");
  console.log("CJ4:CU71: rows = "+dum.getNumRows()+" cols = "+dum.getNumColumns());
  dum = sheet_origin1.getRange("C4:Z71");
  console.log("C4:Z71: rows = "+dum.getNumRows()+" cols = "+dum.getNumColumns());

4:57:51 AM  Notice  Execution started
4:57:54 AM  Info    BU4:CF71: rows = 68 cols = 12
4:57:54 AM  Info    CJ4:CU71: rows = 68 cols = 12
4:57:54 AM  Info    C4:Z71: rows = 68 cols = 24
4:57:51 AM  Notice  Execution completed