A google script for suming two columns from spreadsheet and display back to spreadsheet

Question

I wrote a google script to sum two columns of max 50 numbers in a google spreadsheet. however, I cannot get a correct result. The codes are attached below.

function DailyCreditCalculation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var values_old = sheet.getSheetValues(7,2,50,1);
  var values_new = sheet.getSheetValues(7,3,50,1);
  for(i = 0; i <= values_new.length-1; i++){
    values_old[i] = +values_old[i];
    values_new[i] = +values_new[i];
    values_old[i] = values_old[i] + values_new[i];
  }
  var cell = sheet.getRange(7,2,50,1);
  cell.setValues(values_old);
}

When I run the script, it shows error that

"Cannot convert Array to Object[][]"

How can I modify the script so that it can run successfully?

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2017-07-18T11:18:22

The data you are retrieving are two column Vectors which are represented as arrays of arrays for consistency, e.g.

[[1],
 [2],
 [3]]

which is why you have to do the +values_old[i] conversion.

The output of your loop makes the result an Array as specified in the error message so if you have two identical arrays the result will be e.g. [2,4,6].

setValues needs an array of array to write data (Object[][]).
Since you want it to be a column vector you can say values_old[i] = [values_old[i] + values_new[i]]; which will make the result

[[2],
 [4],
 [6]]

a format setValues can work with.

A google script for suming two columns from spreadsheet and display back to spreadsheet

1 Answers