Google-sheets – How to use the Concatenate function across worksheets in Google Sheets

google sheetsgoogle-apps-script

To delete certain columns in my spreadsheet I use:

function removeColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].deleteColumns(12, 4);
  }
}

This loops through all my worksheets and deletes the columns I specify.

I'm looking to modify the code a bit to instead concatenate the values in columns D & E on each worksheet. I can't seem to find a solution or function for this.

If concatenating columns isn't possible – I suppose I could concatenate cells instead although I would need it to be work across all worksheets.

Best Answer

One can concatenate the content of text columns without a script, using =arrayformula(D:D & E:E) in another column. But apparently you want D and E to just be replaced by a single column that has concatenated values. This can be done with a script like this:

function joinColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var data = sheets[i].getRange("D:E").getValues();
    data = data.map(function(row) {
      return [row[0] + "" + row[1]];
    });
    sheets[i].getRange("D:D").setValues(data);                    
    sheets[i].deleteColumn(5);
  }
}

The main step is 

    data = data.map(function(row) {
      return [row[0] + "" + row[1]];
    });

which loops over the rows in D:E, joining the content of cells. The empty string "" in between is to ensure we get string concatenation and not addition; so, 3 and 6 become 36 rather than 9.

The script deletes column E since it is presumably no longer needed.

Related Topic