title: Set formula for adjacent cell if text is present
body: <p>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.</p>
  
  <p><img src="https://i.stack.imgur.com/5eHAA.png" alt="screenshot"></p>
  
  <p>The problem I'm running into is overwriting information submitted by the form and not by text message.</p>
  
  <p><img src="https://i.stack.imgur.com/7I21L.png" alt="screenshot 2"></p>
  
  <p>My current script is:</p>
  
  
  
  <pre class="lang-js prettyprint-override"><code>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);
  }
  </code></pre>
  
  <p>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 <code>else</code> condition isn't being met (colC is blank in those places). </p>
  
  <p>How do I get the script to move over blank cells without replacing the contents of the cell?</p>
  
  <p>It's sort of confusing to explain, so <a href="https://docs.google.com/spreadsheets/d/1GINxNla523cl9kQxoLvfCTwiolbqFwK3txtxAVcQlYo/edit?usp=sharing" rel="nofollow noreferrer">here's a sample document</a> 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).</p>
  
  <p>Thanks for the help.</p>