Split text and populate columns-CodePudding

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('  ');
}

CodePudding user response：

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);
}