I am trying to concatenate two columns delimited by , and post back the results to a third column Data looks like:

colA     colb      concatenated
aa bb    ww ww     aa bb, ww ww
mm
         qq        qq
zz oo              zz oo

ss       vv zz     ss, vv zz

I get the error Cannot read property "0" from undefined.

Thanks for any help

function ConCat() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Elements");
    var lr = sheet.getLastRow();
    var values1 = sheet.getRange(1, 1, lr,1).getValues();  //Get vales for Column1
    var values2 = sheet.getRange(1, 2, lr,1).getValues();  //Get values for column 2
    var results = [];                               //Create a empty array to be filled concatenated elements

    //Add items to results
    for(var i=0; i<lr; i++){
        results[i] = (values1[i][0]+", "+values2[i][0]);
    }

    //Post back to column 3 starting on row 2
    sheet.getRange(2, 3, lr, 1).setValues([results]);
}

I am trying to concatenate two columns delimited by , and post back the results to a third column Data looks like:

colA     colb      concatenated
aa bb    ww ww     aa bb, ww ww
mm
         qq        qq
zz oo              zz oo

ss       vv zz     ss, vv zz

I get the error Cannot read property "0" from undefined.

Thanks for any help

function ConCat() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Elements");
    var lr = sheet.getLastRow();
    var values1 = sheet.getRange(1, 1, lr,1).getValues();  //Get vales for Column1
    var values2 = sheet.getRange(1, 2, lr,1).getValues();  //Get values for column 2
    var results = [];                               //Create a empty array to be filled concatenated elements

    //Add items to results
    for(var i=0; i<lr; i++){
        results[i] = (values1[i][0]+", "+values2[i][0]);
    }

    //Post back to column 3 starting on row 2
    sheet.getRange(2, 3, lr, 1).setValues([results]);
}

• javascript
• google-apps-script
• google-sheets

2 Answers 2

setValues wants a 2d array. You have a 1d array. Change

results[i] = (values1[i][0]+", "+values2[i][0]); to

results[i] = [values1[i][0]+", "+values2[i][0]];

and change

sheet.getRange(2, 3, lr, 1).setValues([results]); to

sheet.getRange(2, 3, lr, 1).setValues(results);

try this: results.addItem(values[i][0]+' '+values[i][1]); or results.push(values[i][0],values[i][1]);