function onOpen() {
constui = SpreadsheetApp.getUi();
constmenu = ui.createMenu(‘CA Labels’);
menu.addItem(‘Generate Now’, ‘createNewGoogleDocs’)
menu.addToUi();
}
function replaceLogo(documentId) {
varreplaceTextToImage = function(body, searchText, image, width) {
varnext = body.findText(searchText);
if (!next) return;
varr = next.getElement();
r.asText().setText(“”);
varimg = r.getParent().asParagraph().insertInlineImage(0, image);
if (width && typeofwidth == “number”) {
varw = img.getWidth();
varh = img.getHeight();
img.setWidth(width);
img.setHeight(width * h / w);
}
returnnext;
};
varreplaceText = “{{KT-logo}}”;
varimageFileId = “1Nsg9YvfrrIOi8_LBJXotRBdX6M-1Fa8C”;
varbody = DocumentApp.openById(documentId).getBody();
varimage = DriveApp.getFileById(imageFileId).getBlob();
do {
varnext = replaceTextToImage(body, replaceText, image, 200);
} while (next);
}
function mergeDocuments(docIDs) {
// append to base doc
varbaseFile = DriveApp.getFileById(docIDs[0]);
varbaseBody = DocumentApp.openById(docIDs[0]).getActiveSection();
for (vari = 1; i < docIDs.length; i++) {
varappendFile = DriveApp.getFileById(docIDs[i]);
varappendBody = DocumentApp.openById(docIDs[i]).getActiveSection();
vartotalElements = appendBody.getNumChildren();
for (varj = 0; j < totalElements; j++) {
varelement = appendBody.getChild(j).copy();
vartype = element.getType();
if (type == DocumentApp.ElementType.PARAGRAPH) baseBody.appendParagraph(element);
elseif (type == DocumentApp.ElementType.TABLE) baseBody.appendTable(element);
elseif (type == DocumentApp.ElementType.LIST_ITEM) baseBody.appendListItem(element);
elsethrownewError(‘Unknown element type: ‘ + type);
}
appendFile.setTrashed(true);
}
returnbaseFile;
}
function createNewGoogleDocs() {
//This value should be the id of your document template that we created in the last step
constcaLabelTemplate = DriveApp.getFileById(‘1oVyIF9nnH-sosY3i-MxsQjRNpm_1z-ji1kG_JiQgLGA’);
//This value should be the id of the folder where you want your completed documents stored
constdestinationFolder = DriveApp.getFolderById(‘1w56jtIQs6uRD58OYK-1_0QX0o6dX72GH’)
//Here we store the sheet as a variable
constsheet = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName(‘Data’)
//Now we get all of the values as a 2D array
constrows = sheet.getDataRange().getValues();
varcurrentLabelPosition = 1;
vardate = Utilities.formatDate(newDate(), “GMT-8”, “MM-dd”)
//Using the row data in a template literal, we make a copy of our template document in our destinationFolder
varappendCopy;
varappendDoc;
varbody;
varappendDocIDs = [];
//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;
if (currentLabelPosition == 1) {
appendCopy = caLabelTemplate.makeCopy(‘CA-Label-‘ + date , destinationFolder);
appendDoc = DocumentApp.openById(appendCopy.getId());
body = appendDoc.getBody();
appendDocIDs.push(appendCopy.getId());
}
//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText(‘{{Name’ + currentLabelPosition + ‘}}’, row[28]);
if (row[32]&&row[33]&&row[34]) {
body.replaceText(‘{{Address’ + currentLabelPosition + ‘}}’, `${row[32]}\n${row[33]}\n${row[34]}`);
} else if (row[32]&&row[29]) {
body.replaceText(‘{{Address’ + currentLabelPosition + ‘}}’, `${row[32]}\n${row[33]}`);
} else if (row[32]) {
body.replaceText(‘{{Address’ + currentLabelPosition + ‘}}’, row[32]);
}
body.replaceText(‘{{City’ + currentLabelPosition + ‘}}’, row[35]);
body.replaceText(‘{{Province’ + currentLabelPosition + ‘}}’, row[36]);
body.replaceText(‘{{PostalCode’ + currentLabelPosition + ‘}}’, row[37]);
if (currentLabelPosition == 4) {
currentLabelPosition = 0; //reset
}
currentLabelPosition++;
})
varcaLabelsDoc = mergeDocuments(appendDocIDs);
replaceLogo(caLabelsDoc.getId());
consturl = caLabelsDoc.getUrl();
sheet.insertRowAfter(rows.length);
//Write that value back to the ‘Document Link’ column in the spreadsheet.
sheet.getRange(rows.length+1, 1).setValue(url);
}