I have copied and edited a Google Sheets Apps Script for creating google docs using a template and replacement values from a google sheet. It was working, and then I changed a bunch of stuff and now it is returning the error message: "Exception: Invalid argument: replacement". How could I fix this?
Update: the script successfully runs the first row of values, but stops after that.
I tried unchecking run V8 in settings, all that did was throw an "illegal character" error in line 24: const copy = googleDocTemplate.makeCopy(${row[1]}, ${row[0]} TRANSCRIPT , destinationFolder)
Here is the script:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('AutoFill Docs Y2');
menu.addItem('Create New Docs', 'createNewGoogleDocs')
menu.addToUi();
}
function createNewGoogleDocs() {
const googleDocTemplate = DriveApp.getFileById('1lxBpD-EGpwhzVmuLTJ-CxB7PQDBxL-7cQo7jSIeMyII');
const destinationFolder = DriveApp.getFolderById('1DAN94pQNMrnVd09FP47byZfDrAGMA6fb')
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TRANSCRIPTS Y2')
//Now we get all of the values as a 2D array
const rows = sheet.getDataRange().getValues();
//Start processing each spreadsheet row
rows.forEach(function(row, index){
//Here we check if this row is the headers, if so we skip it
if (index === 0) return;
//Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
if (row[90]) return;
//Using the row data in a template literal, we make a copy of our template document in our destinationFolder
const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} TRANSCRIPT` , destinationFolder)
//Once we have the copy, we then open it using the DocumentApp
const doc = DocumentApp.openById(copy.getId())
//All of the content lives in the body, so we get that for editing
const body = doc.getBody();
//In this line we do some friendly date formatting, that may or may not work for your locale
//const friendlyDate = new Date(row[6]).toLocaleDateString();
//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText('{{FIRST}}', row[0]);
body.replaceText('{{LAST}}', row[1]);
body.replaceText('{{DATE ISSUED}}', row[2]);
body.replaceText('{{STUD ID}}', row[3]);
body.replaceText('{{PROGRAM}}', row[4]);
body.replaceText('{{START}}', row[5]);
body.replaceText('{{END}}', row[6]);
body.replaceText('{{SKI/BOARD}}', row[7]);
body.replaceText('{{A1}}', row[8]);
body.replaceText('{{A1C}}', row[9]);
body.replaceText('{{A2}}', row[10]);
body.replaceText('{{A2C}}', row[11]);
body.replaceText('{{SB1}}', row[12]);
body.replaceText('{{SB1C}}', row[13]);
body.replaceText('{{SB2}}', row[14]);
body.replaceText('{{SB2C}}', row[15]);
body.replaceText('{{EC1}}', row[16]);
body.replaceText('{{EC1C}}', row[17]);
body.replaceText('{{IR1}}', row[18]);
body.replaceText('{{IR1C}}', row[19]);
body.replaceText('{{IGA}}', row[20]);
body.replaceText('{{IGAC}}', row[21]);
body.replaceText('{{PH1}}', row[22]);
body.replaceText('{{PH1C}}', row[23]);
body.replaceText('{{RM1}}', row[24]);
body.replaceText('{{RM1C}}', row[25]);
body.replaceText('{{SG1}}', row[26]);
body.replaceText('{{SG1C}}', row[27]);
body.replaceText('{{WA1}}', row[28]);
body.replaceText('{{WA1C}}', row[29]);
body.replaceText('{{WI}}', row[30]);
body.replaceText('{{WIC}}', row[31]);
body.replaceText('{{T1C}}', row[32]);
body.replaceText('{{T1A}}', row[33]);
body.replaceText('{{OG1}}', row[34]);
body.replaceText('{{GPA1}}', row[35]);
body.replaceText('{{ELECTIVESET1}}', row[36]);
body.replaceText('{{E1}}', row[37]);
///////////SKIP PERCENT GRADE ROW 38
body.replaceText('{{E1C}}', row[39]);
body.replaceText('{{E1A}}', row[40]);
body.replaceText('{{E1P}}', row[41]);
body.replaceText('{{ELECTIVESET2}}', row[42]);
body.replaceText('{{E2}}', row[43]);
///////////SKIP PERCENT GRADE ROW 44
body.replaceText('{{E2C}}', row[45]);
body.replaceText('{{E2A}}', row[46]);
body.replaceText('{{E2P}}', row[47]);
body.replaceText('{{TEC}}', row[48]);
body.replaceText('{{TEA}}', row[49]);
body.replaceText('{{OGE}}', row[50]);
body.replaceText('{{GPAE}}', row[51]);
body.replaceText('{{SB3}}', row[52]);
///////////SKIP PERCENT GRADE ROW 53
body.replaceText('{{SB3C}}', row[54]);
body.replaceText('{{SB3A}}', row[55]);
body.replaceText('{{CA}}', row[56]);
///////////SKIP PERCENT GRADE ROW 57
body.replaceText('{{CAC}}', row[58]);
body.replaceText('{{CAA}}', row[59]);
body.replaceText('{{CAP}}', row[60]);
body.replaceText('{{IR2}}', row[61]);
body.replaceText('{{IR2C}}', row[62]);
body.replaceText('{{IR2P}}', row[63]);
body.replaceText('{{EC2}}', row[64]);
body.replaceText('{{EC2C}}', row[65]);
body.replaceText('{{EC2P}}', row[66]);
body.replaceText('{{PH2}}', row[67]);
body.replaceText('{{PH2C}}', row[68]);
body.replaceText('{{PH2P}}', row[69]);
body.replaceText('{{SG2}}', row[70]);
///////////SKIP PERCENT GRADE ROW 71
body.replaceText('{{SG2C}}', row[72]);
body.replaceText('{{SG2A}}', row[73]);
body.replaceText('{{SI}}', row[74]);
///////////SKIP PERCENT GRADE ROW 75
body.replaceText('{{SIC}}', row[76]);
body.replaceText('{{SIA}}', row[77]);
body.replaceText('{{WA2}}', row[78]);
///////////SKIP PERCENT GRADE ROW 79
body.replaceText('{{WA2C}}', row[80]);
body.replaceText('{{WA2P}}', row[81]);
body.replaceText('{{T2C}}', row[82]);
body.replaceText('{{T2A}}', row[83]);
body.replaceText('{{OG2}}', row[84]);
body.replaceText('{{GPA2}}', row[85]);
body.replaceText('{{T12C}}', row[86]);
body.replaceText('{{T12A}}', row[87]);
body.replaceText('{{OG12}}', row[88]);
body.replaceText('{{GPA12}}', row[89]);
//We make our changes permanent by saving and closing the document
doc.saveAndClose();
//Store the url of our new document in a variable
const url = doc.getUrl();
//Write that value back to the 'Document Link' column in the spreadsheet.
sheet.getRange(index + 1, 91).setValue(url)
})
}
I double checked that all the row calls match the google sheet and that all the text calls match the googles sheet and the template document. Not sure what is wrong.
