How to add two arrays together in Google Apps Script

Question

We have a daily and monthly tracker for my work and I am trying to add the data column from the daily tracker to the same column in the monthly tracker.

This is my code below it currently spits out the error "The parameters (String) don't match the method signature for SpreadsheetApp.Range.setValues." which I assume is because it sees both paste and source as strings instead of numbers, I've spent a few hours reading through google however I'm fairly new to JS so having trouble trying to decipher some of the solutions online.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var copySheet = ss.getSheetByName("Daily");
var pasteSheet = ss.getSheetByName("Monthly");
var paste = pasteSheet.getRange(3, 2 , 10);


// get source range
var source = copySheet.getRange(3, 2 , 10).getValues();
// set destination range
var paste = paste.setValues(source + paste);

Tried to add data from a column on one sheet to a column on a serperate sheet.

score 0 · Answer 1 · answered Feb 22 '23 at 23:26

The heart of the problem is this line: var paste = paste.setValues(source + paste);

paste is simply a range; you didn't getValues() for that range
even if you had got the values, simply "adding" the two arrays together (source + paste) won't work.

Try this change to your script. This is really "old school"; a simple loop where the values from each row are added together and pushed onto a temporary array. Others might propose far more elaborate answers but this is the essence of any solution.

DELETE

// set destination range
var paste = paste.setValues(source + paste);

REPLACE WITH

  var tempArray = [] // to hold the row values of summing the two arrays

  // get the data from Monthly
  var pasteData = paste.getValues()

  // loop though the first array and add the values on each line
  for (var i=0;i<pasteData.length;i++){
    tempArray.push([pasteData[i][0]+source[i][0]])
    //Logger.log("DEBUG: i:"+i+" "+tempArray)
  }
  // Logger.log(tempArray) // DEBUG
  // set destination range
  paste.setValues(tempArray);

score 0 · Accepted Answer · answered Feb 23 '23 at 00:47

This answer use more advanced methods, Array.flat and Array.map, to get the same result.

h/t @Cooper

StackOverflow: Sum two columns and display on third column
StackOverflow: Merging two arrays into and array of arrays

function myFunction02() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Daily");
  var pasteSheet = ss.getSheetByName("Monthly");
  
  // get the paste range and values and flatten to 1D
  var paste = pasteSheet.getRange(3, 2 , 10);
  var pasteData = paste.getValues().flat()
  // Logger.log(pasteData) // DEBUG

  // get source range and values and flatten to 1D
  var source = copySheet.getRange(3, 2 , 10)
  var sourceData = source.getValues().flat()
  // Logger.log(sourceData) // DEBUG

  // combine the ranges
  // StackOverflow: Merging two arrays into and array of arrays
  // https://stackoverflow.com/a/69982025/1330560
  var sumData = pasteData.map((e,i) => {return [e,sourceData[i]];})
  // Logger.log(sumData) // DEBUG

  // sum array by by row
  var vs = sumData.map(r => [r[0]+r[1]]);
  // Logger.log(vs) // DEBUG

  // update values to Monthly
  paste.setValues(vs)
}

How to add two arrays together in Google Apps Script

2 Answers2