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.
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]);
}
}