If this is not the correct location for this post, please advise.
I have a Google script which converts a Google form submission (Row in a Google sheet) into a Google Document.
While the script works on a row by row basis, I con't get it to work on the entire sheet using a for loop.
I am using the replaceText() method to replace existing placeholder text with the answers submitted in the Google sheet.
The script takes the contents of a row, copies a template doc, copies and rename the file, then replaces placeholder text with the cell contents, column by column.
When I tried the for loop, it copies the file and renames it, but doesn't replace any of the text. The script works when I specify a particular row number, but not in a for loop which I cannot understand. The script below converts the answers from row 2 into a Google doc, see lines beginning 'var Name' and 'var Data'. If anyone can spot what might be causing this script to fail in a loop, please shout.
Code:function readRows() { var nums = ["Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten"]; function toText(num) { var s; 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); } //default: // s = toText(Math.floor(num/10))+"ty"; // break; 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).getValues(); var template = "Document id 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=0; j<length; j++) { body.replaceText("Answer"+toText(j), data[0][j]); } }


Reply With Quote

Bookmarks