Google Sheets Concatenate Google Apps Script

0
votes

I'm new to javascript and a novice coder. I have been trying to write a simple script for a Google Sheet to concatenate two columns of data into another column:
google sheet

My script below runs fine but only produces Aaron Ward as line 1 of Desired Column C. I'm sure it is really simple what I am missing, but if anyone has a minute to help a rookie, I'd really appreciate it.

function concatenate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var lr = sheet.getLastRow()
  var values1 = sheet.getSheetValues(1, 1, lr, 1);
  var values2 = sheet.getSheetValues(1, 2, lr, 1);
  var results = [];
  for (var i = 0; i < lr; i++) {
    results = [values1[i] + " " + values2[i]];
    Logger.log(results);
    sheet.getRange(1, 3, results.length).setValues([results]);
  }
}
2
google-apps-scriptgoogle-sheets
This happens because 1) you always grab row 1, column 3, with length of 1 row, and 2) you write within the loop, rather than collecting the row data into the output array and then writing once done looping. - tehhowch
Thank you for your help. Writing inside loop was main problem, I got it working like this (not sure if cleanest): function concatenate() { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues(); var lr = sheet.getLastRow() var values1 = sheet.getSheetValues(1,1,lr,2); Logger.log(values1); var results = []; for (var i=0; i<lr; i++) { results[i] = [values1[i][0]+" "+values1[i][1]]; Logger.log(results); } sheet.getRange(1,3,results.length).setValues(results); } - B.J. Simpson
Consider using Array#map and/or Array#join to simplify your code / increase its expressiveness. Feel free to also submit your fixed code as an answer if you feel it will be useful to others, or delete the question if you think it will not - tehhowch

2 Answers

0
votes

Why not using a formula? In C1 place the formula =JOIN(“ “,A1:B1) and drag it to the last row.

0
votes

You're very close. In your for-loop, you need to push() the concatenated string into your results array. Then you can setValues(results) after the for-loop.

function concatenate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var lr = sheet.getLastRow()
  var values1 = sheet.getSheetValues(1, 1, lr, 1);
  var values2 = sheet.getSheetValues(1, 2, lr, 1);
  var results = [];
  for (var i = 0; i < lr; i++) {
    results.push([values1[i] + " " + values2[i]]);
  }
  sheet.getRange(1, 3, results.length).setValues(results);
}