How do I fix this Macro to CONCATENATE and then paste values of two columns

user_19717543 0
Viewed 27

I'm trying to write a macro in gsheets that will concatenate two columns and then copy the values back over to the first column. But everything I try the copy to column just gets blanked out.

function CopyandPasteValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('H2').activate();
  spreadsheet.getCurrentCell().setFormula('=CONCATENATE(E2,"",G2)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('H2:H249'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('H2:H249').activate();
  spreadsheet.getRange('E2').activate();
  spreadsheet.getRange('H2:H249').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush();
};
google-apps-script google-sheets copy-paste
1 Answers

If you only need the values, try the the following:

function CopyandPasteValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  var values = spreadsheet.getRange('E2:G249').getDisplayValues();
  var output = values.map(row => row[0] + row[2]);
  spreadsheet.getRange('H2:H249').setValues(output);
};
user_15954510
Related