for loop iteration and replaceText

Question

I have changed the script to the following:

  function readRows(){
   var nums = ["Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten"];

   function toText(num) {
   var s;
   if (num >= 100)
    throw "Too big";
   if (num > 10) {
    if (num < 20) {
        switch (num) {
            case 11:
                return "Eleven";
            case 12:
                return "Twelve";
            case 13:
                return "Thirteen";
            case 14:
                return "fourteen";
            case 15:
                return "Fifteen";
            case 16:
                return "sixteen";
            case 17:
                return "seventeen";
            case 18:
                return "eighteen";
            case 19:
                return "nineteen";
            default:
                return toText(num-10)+"teen";
        }
    }

    switch (Math.floor(num / 10)) {
        case 2:
            s = "Twenty";
            break;
        case 3:
            s = "Thirty";
            break;
        case 4:
            s = "Fourty";
           break;
        case 5:
            s = "Fifty";
            break;
        case 6:
            s= "SiXty";
            break;
        case 7:
            s = "Seventy";
            break;
        default:
            s = toText(Math.floor(num/10))+"ty";
            break;
    }
    if(num > 20 && num < 30)
       {
        return "twenty"+toText(num-20);
       }
    if(num > 30 && num < 40)
       {
        return "thirty"+toText(num-30);
       }
    if(num > 40 && num < 50)
       {
        return "fourty"+toText(num-40);
       }
    if(num > 50 && num < 60)
       {
        return "fifty"+toText(num-50);
       }
    if(num > 60 && num < 70)
       {
        return "sixTy"+toText(num-60);
       }
    if(num == 70)
       {
        return "sEvEnty";
       }
    if (num % 10 > 0)
        return s + toText(num % 10);
    return s;
    }

    return nums[num];
    }


    var sheet = SpreadsheetApp.getActiveSheet();
    var lastCol = sheet.getLastColumn();
    var length = sheet.getMaxColumns();
    var rows = sheet.getMaxRows();

    var Name = sheet.getRange(2, 2).getValue(); 
    var data = sheet.getRange(2, 1, 2, lastCol);  
    var template = "spreadsheet key";
    var fileName = "Application document template.docx";
    var newFile = DocsList.getFileById(template).makeCopy(Name + " Application for Phase1 NF3").getId();
    var doc = DocumentApp.openById(newFile);
    var body = doc.getActiveSection();

    for(var j=1; j<length; j++)
    {
    body.replaceText("Answer"+toText(j), data[0][j]);   // line 105
    }
    }

At one stage I had it almost working except for replacing 70, did something and then nothing worked. Reworked it, now I'm getting this error, TypeError: Cannot read property "1.0" from undefined. (line 105, file "ConvertToDocs"). Can anyone advise me what the property "1.0" might refer to? Why is that first line of code not showing in the code window?

Answer 1

I don't know of any easy way, but here's a brute-force effort:

var nums = ["Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten"];

function toText(num) {
    var s;
    if (num >= 100)
        throw "Too big";
    if (num > 10) {
        if (num < 20) {
            switch (num) {
                case 11:
                    return "Eleven";
                case 12:
                    return "Twelve";
                case 13:
                    return "Thirteen";
                case 15:
                    return "Fifteen";
                case 18:
                    return "Eighteen";
                default:
                    return toText(num-10)+"teen";
            }
        }

        switch (Math.floor(num / 10)) {
            case 2:
                s = "Twenty";
                break;
            case 3:
                s = "Thirty";
                break;
            case 5:
                s = "Fifty";
                break;
            case 8:
                s = "Eighty";
                break;
            default:
                s = toText(Math.floor(num/10))+"ty";
                break;
        }
        if (num % 10 > 0)
            return s + toText(num % 10);
        return s;
    }

    return nums[num];
}

function readRows() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var lastCol = sheet.getLastColumn();
    var length = sheet.getMaxColumns();
    var rows = sheet.getMaxRows();
    for(var i=2; i<rows; i++) // starting from row 2 in sheet
    {
        var Name = sheet.getRange(i, 2).getValue(); 
        var data = sheet.getRange(i, 1, i, lastCol);  
        var template = <spreadsheet key goes here>;
        var fileName = "Application document template.docx";
        var newFile = DocsList.getFileById(template).makeCopy(Name + " Application for   Phase1 NF3").getId();
        var doc = DocumentApp.openById(newFile);
        var body = doc.getActiveSection();

        for(var j=1; j<length; j++)
        {
            body.replaceText("Answer"+toText(j), data[0][j]);
        }
    }
}

Since your answers are less than 100, I've only implemented up to 99. I modified your code some because some of it didn't make sense.

FWIW, the i in the inner for loop modifies the i in the outer loop because variables are function scoped, not block scoped. You didn't seem to need it so I removed it.

Answer 2

It's not clear what your question is - you've got hints in the Title, but ask something different in the text. Let's see what we can do.

First - why the error? That's easy: data is defined as a Range, but you're trying to access it as a two-dimensional array. You probably wanted var data = sheet.getRange(...).getValues(), which would give you the content of the cells in the range.

You're doing something else strange there. You define the data range with .getRange(2, 1, 2, lastCol)', which isA2:x3`; 2 rows, x=maxColumns. Then later, you try to iterate over just one row and 'maxColumns' - something isn't right there, but only you know what you wanted to do.

Second - you mention looping. You've got an Array Iteration Bug: You're looping like this - for(var j=1; j<length; j++). The problem with that is arrays start at 0 so you'll skip the first element when accessing data[][j].

Third - you mention Document.replaceText(). You don't say what problem you're having with it, but it could be that you're not getting replacements occurring because you're not finding the text in the document. Based on your code, here are some possible explanations for that:

1. Typos / inconsistent case. "SiXty", "Thirteen", "fourteen" - unless your template has the exact same mistakes, your matching will fail.
2. Spaces - you're searching for "Answer"+toText(j), you probably want "Answer "+toText(j).
3. Hyphens & more capitalization concerns - Numbers are often hyphenated, e.g. Twenty-one. Additionally, the "one" in this case is not capitalized. But your template may disagree - make sure you match it.

Finally, what you don't ask, but what your posted code is mostly about - converting a number to an English string representation. You can and should simplify your toText() function. This kind of problem contains patterns that can be exploited to simplify the solution. You were part way there - basically, you've got one set of sub-numbers that can be expressed only in terms of themselves (zero, one, two...nineteen), and another set that are composites (twenty[-blah], thirty[-blah]...). So the solution is to separate those two groups, and use simply arrays to perform a lookup for the appropriate text.

function toText(num) {
  if (num >= 100) throw new Error("Too big");
  if (num < 0) throw new Error("Negative");
  if (num - Math.floor(num) > 0) throw new Error("Not Integer");

  var smallnums = ["Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten",
                 "Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"];
  var tens = ["","","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety"];
  var s = "";

  // Is this number in the smallnums set?
  if (num < smallnums.length)
    s = smallnums[num];
  else {
    // No, so express the 'tens', then (maybe) the 'ones'.
    s += tens[Math.floor(num/10)];
    var remnant = num % 10;
    if (remnant > 0) s += "-" + smallnums[remnant].toLowerCase();
  }
  return s;
}