1

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.

1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.