For Loop Iteration And Replacetext

I have changed the script to the following: function readRows(){ var nums = ['Zero', 'One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven', 'Eight', 'Nine', 'Ten']; functi

Solution 1:

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

varnums= ["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) {
                case11:
                    return"Eleven";
                case12:
                    return"Twelve";
                case13:
                    return"Thirteen";
                case15:
                    return"Fifteen";
                case18:
                    return"Eighteen";
                default:
                    return toText(num-10)+"teen";
            }
        }

        switch (Math.floor(num / 10)) {
            case2:
                s = "Twenty";
                break;
            case3:
                s = "Thirty";
                break;
            case5:
                s = "Fifty";
                break;
            case8:
                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() {
    varsheet= SpreadsheetApp.getActiveSheet();
    varlastCol= sheet.getLastColumn();
    varlength= sheet.getMaxColumns();
    varrows= sheet.getMaxRows();
    for(var i=2; i<rows; i++) // starting from row 2 in sheet
    {
        varName= sheet.getRange(i, 2).getValue(); 
        vardata= sheet.getRange(i, 1, i, lastCol);  
        vartemplate= <spreadsheet key goes here>;
        varfileName="Application document template.docx";
        varnewFile= DocsList.getFileById(template).makeCopy(Name + " Application for   Phase1 NF3").getId();
        vardoc= DocumentApp.openById(newFile);
        varbody= 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.

Solution 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.

functiontoText(num) {
  if (num >= 100) thrownewError("Too big");
  if (num < 0) thrownewError("Negative");
  if (num - Math.floor(num) > 0) thrownewError("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;
}