Split text and populate columns

Question

I use a sheet that gets auto-populated by addition of rows by a 3rd party app. Once a row gets added, I wish to split the text to the following empty columns, the separator being " "(double space). My app script is able to process a static sheet correctly, but in case of a dynamic one(where the 3rd party app keeps adding rows at the bottom), the script just splits the last row and deletes the cells content of the previously split text above it.

How to solve this issue? The script should just split the text into columns as its added in the last row and not delete the ones above.

function splitColumn() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var commaSheet = ss.getSheetByName('Sheet2');
  var lastRowComma = commaSheet.getLastRow();
  var commaRange = commaSheet.getRange('b1:b' + lastRowComma);
  commaRange.splitTextToColumns('  ');
}

johndee31415 · Accepted Answer · 2022-07-04 06:23:29Z

2

This appears to be the default behavior of the text to columns feature in Google sheets. It does the same if you select the functionality from the menu in Google Spreadsheets. To avoid it, you could loop through the range and split the cells individually. That way, no cells will be overwritten. Example:

function split() {
  const DELIMITER = "  ";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var commaSheet = ss.getSheetByName('Sheet2');
  var lastRowComma = commaSheet.getLastRow();
  var commaRange = commaSheet.getRange('b1:b' + lastRowComma);
  for(let r = 1; r <= commaRange.getNumRows();r++) 
    commaRange.getCell(r, 1).splitTextToColumns(DELIMITER);
}

answered Jul 4, 2022 at 6:23

johndee31415

3212 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rinku Over a year ago

Thank you for the solution. You saved my day. However am not able to extract the 1st row..the 1st row imports the range at A1 and B1=A1 and so on...pls see SS at the following link [link] (prnt.sc/xoYAfSmlRyUc) Also is there a way that I can define B=A using some arrayformula etc so that i dont need to fill B using drag.

Collectives™ on Stack Overflow

Split text and populate columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related