javascript - Concatenate two columns in google sheets script - Stack Overflow

admin管理员组

文章数量:1426014

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

Share Improve this question Follow edited Jan 7, 2018 at 19:21 xyz asked Jan 7, 2018 at 18:48 xyzxyz 2,2981111 gold badges4949 silver badges7171 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 5

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]);

本文标签： javascriptConcatenate two columns in google sheets scriptStack Overflow