title: Not properly referencing cells
body: <p>I am coding a phone number formatter for a large database. Everything is working, but there is an inconsistent printing of blank cells. Most of the cells are read through and are properly formatted, but there are some that show blank outputs in the wrong cells.</p>
  <p>I have tried fixing this by resetting the cleanNumber variable to a blank string but this just posed another issue on line 33 with indexOf().</p>
  <pre><code>function myFunction() {
    var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // connects sheet to code
    var startRow = 18;
    var endRow = 41;
    for (var i = startRow; i &lt;= endRow; i++) {     // i = currnet row | row to end at | add 1 to count each time
      var workingCell = activeSheet.getRange(i, 2).getValue();
      Logger.log(&quot;Original number: &quot; + workingCell)
      //If blank, move to next row
      exit: if (workingCell.length == 0.0) {
        var blank = &quot;&quot;;
        activeSheet.getRange(i, 3).setValue(blank);
        Logger.log(&quot;This row is blank&quot;)
        //break exit;
      }
      // cleanNumber if it isn't formatted already 
      else if (isNaN(workingCell)) { // runs if active cell is not a preformatted number 
        var cleanNumber = workingCell.replace(/\D/g, '');  // removes all non-numeric values
        activeSheet.getRange(i, 3).setValue(cleanNumber);
        Logger.log(&quot;Extra char's removed: &quot; + cleanNumber)
      }
      // runs if active cell is already preformatted 
      else {
        activeSheet.getRange(i, 3).setValue(workingCell);
        Logger.log(&quot;No need for formatting: &quot; + workingCell)
      }
      // If cleanNumber has a country code(+1), remove it
      if ((cleanNumber.indexOf(&quot;1&quot;)) == 0) {
        cleanNumber = cleanNumber.substring(1); //removes first character = &quot;1&quot;
        activeSheet.getRange(i, 3).setValue(cleanNumber);
        Logger.log(&quot;Country code removed: &quot; + cleanNumber);
      }
      // If number is longer than 10 characters, create an extension variable - with entire number, remove 10 characters from front
      if (cleanNumber.length &gt; 10.0) {
        var extension = cleanNumber.substring(10, 15);
        var phoneNumber = cleanNumber.substring(0, 10);
        var formatted = phoneNumber.slice(0, 3) + &quot;-&quot; + phoneNumber.slice(3, 6) + &quot;-&quot; + phoneNumber.slice(6, 15);
        var finalPhoneNumber = formatted + &quot; ext. &quot; + extension;
        activeSheet.getRange(i, 3).setValue(finalPhoneNumber);
        Logger.log(&quot;This number is in its final ext. format: &quot; + finalPhoneNumber);
      }
      //if number doesnt have an extension, put it into final format
      else if (cleanNumber.length = 10.0) {
        var frontFinal = cleanNumber.substring(0, 3);
        var midFinal = cleanNumber.substring(3, 6);
        var endFinal = cleanNumber.substring(6, 10);
        var finalNumber = frontFinal + &quot;-&quot; + midFinal + &quot;-&quot; + endFinal;
  
        activeSheet.getRange(i, 3).setValue(finalNumber);
        Logger.log(&quot;This number is in its final format: &quot; + finalNumber);
      }
      //if number is less than 10 numbers
      else {
        Logger.log(&quot;This number is shorter than 10 numbers&quot; + cleanNumber);
      }
      cleanNumber = &quot; &quot;;
    }
  }
  </code></pre>
  <p>The pre-formatted numbers are on the left and the output is in the right column.</p>
  <p><a href="https://i.stack.imgur.com/ThhPI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ThhPI.png" alt="[1]: https://i.stack.imgur.com/bMCNc.png" /></a></p>
  <p>Here is some sample data, please consider that the issue seems to be stemming from blank rows.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Unformatted</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1999-111-1111</td>
  </tr>
  <tr>
  <td style="text-align: left;">1+2222-222222</td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">4444444444 ext. 223</td>
  </tr>
  <tr>
  <td style="text-align: left;">9738094395</td>
  </tr>
  <tr>
  <td style="text-align: left;">9172609107</td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">866.786.6682</td>
  </tr>
  <tr>
  <td style="text-align: left;">973 330 2212</td>
  </tr>
  <tr>
  <td style="text-align: left;">(631)563-4000 ext. 234</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I look forward to solving this issue, thank you for the help :)</p>