Set formula for adjacent cell if text is present

Question

I'm working with a Google Sheets form which also accepts answers via text message. I'm trying to work out a method using Google Apps Scripts to split the body of the text message using a comma as a delimiter.

The problem I'm running into is overwriting information submitted by the form and not by text message.

My current script is:

function splitCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var colC = sheet.getRange("C2:C").getValues();
  var colD = sheet.getRange("D2:D").getFormulas();
  //Logger.log(colC);
  for(var i in colC){
    if(typeof(colC[i][0]) =='string'){
      colD = '=if(istext(C2:C),split(C2:C,",",true))';
    } else {
      colD = 'D2:D';
    }
  }
  sheet.getRange("D2:D").setFormula(colD);
}

The function is working correctly, splitting the contents of column C (the SMS body) into D, E, and F as expected. But, it's overwriting data in column D because the else condition isn't being met (colC is blank in those places).

How do I get the script to move over blank cells without replacing the contents of the cell?

It's sort of confusing to explain, so here's a sample document you can check out. A custom menu should install when you open it and you can run the script from there (or from the editor).

Thanks for the help.

Answer 1

There are a few simple mistakes to start.

• A spreadsheet cell can contain a value or a formula, not both.

• If you use setFormula/s(), any value in a cell will be replaced by the result of the formula, even if the formula is blank.

  Since you want to have a mix of values and formulas, you should set formulas only in the specific cells that match the criteria:

  // If we received a SMS response, set a formula to parse it
  sheet.getRange(2+i,4).setValue('=if(istext(C2:C),split(C2:C,",",true),"")')
  

• The criteria test isn't sufficient. A blank cell is still of type string, but it's a blank string. So this evaluates true for both form entries and SMS entries:

  if(typeof(colC[i][0]) =='string'){ ...
  

  A more effective test checks for a non-blank response:

  if(colC[i][0] != ''){ ...
  

  An even better one would ensure that the value in column C meets the required format requirements.

• You are looping over an array using the for .. in loop, which is meant for going over object properties. This works, but the loop value i will be a string, which can cause problems when doing math. Better to get in the habit of looping over the numeric index. (See.)

• The full-column range expression C2:C is elegant, however you end up with an array that contains all rows in the spreadsheet, more than a thousand in your example. Since we're going to loop over all rows, it's best to limit that range:

  var colC = sheet.getRange(2, 3, sheet.getLastRow()).getValues();  // C2:C, only non-blank rows
  

Adjusting for those problems, we have:

function splitCells2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var colC = sheet.getRange(2, 3, sheet.getLastRow()).getValues();  // C2:C, only non-blank rows
  //Logger.log(colC);
  for(var i=0; i< colC.length; i++){
    if(colC[i][0] != ''){
      // If we received a SMS response, set a formula to parse it
      sheet.getRange(2+i,4).setValue('=if(istext(C2:C),split(C2:C,",",true),"")')
    }
  }
}