Javascript Question posted in
A very good W3school tutorial can be found here.

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

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!

Tags:

2

Answers


  1. 0

    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()

    Login or Signup to reply.
  2. 0

    The Range.splitTextToColumns() method accepts custom delimiters, so you simply can do something like this:

    function splitColumnV1() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const range = sheet.getRange(`B2:B${sheet.getLastRow()}`);
  range.splitTextToColumns('n');
}

    The split data will fill as many columns in the right as necessary, and will overwrite that many columns with the split values and blanks.

    See Range.splitTextToColumns().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search