How do I splitTextToColumns only for cells containing commas in Google Apps Script?

Question

I am super new to Google Apps Script and I've run into a roadblock. Essentially, I have a form on my website that automatically populates names and addresses into a Google Sheet, and the entire address is entered into one cell where the street, city + state, zip code, and country are all separated by line breaks.

Ultimately I want to split the address data into individual columns (Street, City, State, Zip Code, Country) but ONLY for the cells that contain the line breaks. As new data is populated from the website form to the Google Sheet on a regular basis, the goal is for the script to skip over the already separated rows of data and just separate the data for the new entries at the bottom of the sheet.

Here's my current function:

function splitColumnV1() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

  var lastRowComma = ss.getLastRow();
  var commaRange = ss.getRange('B2:B' + lastRowComma);      // range of the data containing cells I want to split
  
  const searchText = '\n';
  const replaceText = ', ';

  commaRange                     // with the range, find all line breaks and replace with commas
  .createTextFinder(searchText)
  .replaceAllWith(replaceText); 

for (var j = 0; j < lastRowComma; j++) {                      // for each cell value
  if (j.getValues.createTextFinder(', ') != null) // currently getting the error here
   {
  j.splitTextToColumns(', ');     // if there are line breaks then split the text to columns
  }
  elseif (j.getValues.createTextFinder(', ') != null)
    {
  continue;            // if there are no commas in the cell then skip to the next cell
  }
}
}

Currently my code works up until "for, if" section, but then I get the error code: "TypeError: Cannot read properties of undefined (reading 'createTextFinder')"

If I remove the whole "for, if" section and replace it with just the line commaRange.splitTextToColumns(', '); then it sort of works, but it replaces the already separated data with blank cells. Any help would be super appreciated!

Answer 1

Splitting Each Cell Containing Commas in Google Apps Script

Your current code uses for (var j = 0; j < lastRowComma; j++). Inside the loop, it attempts to call j.getValues and j.createTextFinder(). This is incorrect because j is merely a numeric value (the loop index) and does not correspond to a cell or range object.

Attempting to perform operations like j.getValues and j.splitTextToColumns() on j is invalid, as these methods can only be applied to cell or range objects, not to integers.

You can try this Code:

function splitColumnV1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('B2:B' + lastRow);

  const searchText = '\n';
  const replaceText = ', ';
  range.createTextFinder(searchText).replaceAllWith(replaceText);

  var data = range.getValues();
  for (var j = 0; j < data.length; j++) {
    var cellValue = data[j][0];
    if (cellValue && cellValue.includes(', ')) {
      var parts = cellValue.split(', ');
      sheet.getRange(j + 2, 3, 1, parts.length).setValues([parts]);
    }
  }
}

This Code Fetches data from the range as an array (var data = range.getValues();), splits each cell value containing commas (cellValue.split(', ')), and writes the parts into the correct columns using sheet.getRange(). Rows without commas are skipped automatically.

Sample data before running the code:

Full Address	Street	City	State	Zip Code	Country
123 Main St., New York, NY, 10001, USA
789 Oak Lane Chicago IL 60601 USA

Sample Data after running the Code:

Full Address	Street	City	State	Zip Code	Country
123 Main St., New York, NY, 10001, USA	123 Main St.	New York	NY	10001	USA
789 Oak Lane, Chicago, IL, 60601, USA	789 Oak Lane	Chicago	IL	60601	USA

Reference:

getValues()