1 function onOpen() {
2 const ui = SpreadsheetApp.getUi();
3 const menu = ui.createMenu('AutoFill Docs');
4 menu.addItem('Create New Docs', 'createNewGoogleDocs');
5 menu.addToUi();
6 }
7
8 function createNewGoogleDocs() {
9
10 const googleDocTemplate = DriveApp.getFileById('REDACTED');
11 const destinationFolder = DriveApp.getFolderById('REDACTED');
12 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
13 const rows = sheet.getDataRange().getValues();
14
15 rows.forEach(function(row, index){
16 if (index === 0) return;
17 if (row[1]) return;
18
19 const copy = googleDocTemplate.makeCopy(`${row[2]}, Baptism Registration`, destinationFolder);
20 const doc = DocumentApp.openById(copy.getId());
21 const body = doc.getBody();
22 const friendlyDate1 = new Date(row[3]).toLocaleDateString();
23 const friendlyDate2 = new Date(row[17]).toLocaleDateString();
24
25 body.replaceText('{{Parishioner Status}}', row[29]);
26 body.replaceText('{{Infant Full Name}}', row[2]);
27 body.replaceText('{{Gender}}', row[30]);
28 body.replaceText('{{Infant DOB}}', friendlyDate1);
29 body.replaceText('{{Infant Place of Birth}}', row[4]);
30 body.replaceText('{{Mother Full Name}}', row[6]);
31 body.replaceText('{{Mother Class Status}}', row[28]);
32 body.replaceText('{{Mother Phone}}', row[10]);
33 body.replaceText('{{Mother Email}}', row[11]);
34 body.replaceText('{{Father Full Name}}', row[5]);
35 body.replaceText('{{Father Class Status}}', row[25]);
36 body.replaceText('{{Father Phone}}', row[8]);
37 body.replaceText('{{Father Email}}', row[9]);
38 body.replaceText('{{Address}}', row[7]);
39 body.replaceText('{{Other Details}}', row[12]);
40 body.replaceText('{{Godfather Name}}', row[13]);
41 body.replaceText('{{Godfather Status}}', row[14]);
42 body.replaceText('{{Godfather Class Status}}', row[26]);
43 body.replaceText('{{Godmother Name}}', row[15]);
44 body.replaceText('{{Godmother Status}}', row[16]);
45 body.replaceText('{{Godmother Class Status}}', row[27]);
46 body.replaceText('{{Requested Date}}', friendlyDate2);
47 body.replaceText('{{St Charles}}', row[18]);
48 body.replaceText('{{Location}}', row[31]);
49 body.replaceText('{{Church Married}}', row[33]);
50 body.replaceText('{{Adopted}}', row[34]);
51 body.replaceText('{{Baptism Status}}', row[36]);
52
53
54 doc.saveAndClose();
55 const url = doc.getUrl();
56 sheet.getRange(index + 1, 2).setValue(url);
57
58
59 })
60
61 }