Please kindly help me finish the script. I have read some others article, but still not working.
Main idea : Typing data in Google Sheets and run script to replace those data into Google Docs. (text & image)
*** 1. Select rows to create new google document by using onOpen() Menu function *** ✅ (Working)
*** 2. Replacing text from Google Sheets data cell *** ✅ (Working)
*** 3. Replacing image from Google Sheets data cell *** ❌ (Not Working) (I've tried with using URL and image in cell in Google Sheets, but both were not working) (If use image in sheets cell, it will show the word "cellimage" in google docs, if I use URL it will just showing URL in google docs)
In this case, I'm trying with URL file (JPG/PNG) in my google drive.
Please let me know if there's easier way please give me some advise, I'm really appreciate it. I'm newbie in doing script. Thanks in advance.
HERE IS MY GOOGLE SCRIPT.
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet(),
entries = [{
name: "Generate Document",
functionName: "main"
}];
sheet.addMenu("Generate", entries);
}
var templateFileId = "1oWlXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXbeQA",
targetFolderId = "1DyEXXXXXXXXXXXXXXXXXXXXXXXXXX4JJa",
removeFileId = "1RUUXXXXXXXXXXXXXXXXXXXXXXXXXjfi4",
ss = SpreadsheetApp.getActiveSpreadsheet(),
sheet = ss.getActiveSheet(),
lastColumn = sheet.getLastColumn(),
lastRow = sheet.getLastRow(),
range = SpreadsheetApp.getActiveSheet().getActiveRange(),
col = 13, // start with 1
marks0 = ['#PUR1#', '#PRO1#', '#DATE1#', '#ID1#', '#NAME1#', '#P1#', '#MO1#', '#LOT1#',
'#SNO1#', '#QTY1#', '#QRID1#', '#QRPRO1#'
],
start = 0, // start with 0
target = 4;
function main() {
var values = range.getValues(),
period = values.length / target,
rng,
a;
for (a = 0; a < period; a++) {
rng = values.slice(target * a);
mainn(rng, a);
}
};
// Duplicate google doc
function createDuplicateDocument(templateFileId, name, a) {
var source = DriveApp.getFileById(templateFileId),
newFile = source.makeCopy(name);
newFile.setOwner("xxxxxxxxxx@hotmail.com");
var targetFolder = DriveApp.getFolderById(targetFolderId);
targetFolder.addFile(newFile);
var removeFile = DriveApp.getFolderById(removeFileId);
removeFile.removeFile(newFile);
var n = sheet.getRange(range.getRow() + (target * a), col);
n.setValue(newFile.getUrl()).setVerticalAlignment("middle");
n.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
return DocumentApp.openById(newFile.getId());
}
// Search a paragraph in the document and replaces it with the generated text
function replaceText(targetDocumentId, keyword, newText) {
var targetDocument = DocumentApp.openById(targetDocumentId),
targetBody = targetDocument.getBody();
targetBody.replaceText(keyword, newText);
targetDocument.saveAndClose();
}
// Main function to run
function mainn(rng, a) {
var allDataForDocument0 = rng[0];
// Check if active range is invalid
if (allDataForDocument0.length === col) {
// Create the target file, with whatever name you want
var date = allDataForDocument0[2].toString().slice(4, 15),
months = {
'Jan': '01',
'Feb': '02',
'Mar': '03',
'Apr': '04',
'May': '05',
'Jun': '06',
'Jul': '07',
'Aug': '08',
'Sep': '09',
'Oct': '10',
'Nov': '11',
'Dec': '12'
},
split = date.split(' ');
date = [split[1], months[split[0]], split[2]].join('/');
var newTargetFileName = "PO:" + allDataForDocument0[0] + " " + date,
newTargetFile = createDuplicateDocument(templateFileId, newTargetFileName, a),
newTargetFileId = newTargetFile.getId(),
len = marks0.length,
i = 0,
j = 0,
dataForDocument = "";
// For first active range
for (i = 0; i < len; i++) {
dataForDocument = allDataForDocument0[i].toString();
// For every dates you may have to use something like this to ensure a time goes out
if (marks0[i] === "#DATE1#") {
dataForDocument = dataForDocument.slice(4, 15);
months = {
'Jan': '01',
'Feb': '02',
'Mar': '03',
'Apr': '04',
'May': '05',
'Jun': '06',
'Jul': '07',
'Aug': '08',
'Sep': '09',
'Oct': '10',
'Nov': '11',
'Dec': '12'
};
split = dataForDocument.split(' ');
dataForDocument = [split[1], months[split[0]], split[2]].join('/');
}
replaceText(newTargetFileId, marks0[i], dataForDocument);
}
// For another active range
for (i = 2; i <= target; i++) {
var allDataForDocument = rng[i - 1],
marks = ['#PUR' + i + '#', '#PRO' + i + '#', '#DATE' + i + '#', '#ID' + i + '#', '#NAME' + i + '#', '#P' + i + '#', '#MO' + i + '#', '#LOT' + i + '#',
'#SNO' + i + '#', '#QTY' + i + '#', '#QRID' + i + '#', '#QRPRO' + i + '#'
];
len = marks.length;
if (allDataForDocument !== undefined) {
for (j = 0; j < len; j++) {
dataForDocument = allDataForDocument[j + start].toString();
// For every dates you may have to use something like this to ensure a time goes out
if (marks[j] === "#DATE" + i + "#") {
dataForDocument = dataForDocument.slice(4, 15);
months = {
'Jan': '01',
'Feb': '02',
'Mar': '03',
'Apr': '04',
'May': '05',
'Jun': '06',
'Jul': '07',
'Aug': '08',
'Sep': '09',
'Oct': '10',
'Nov': '11',
'Dec': '12'
};
split = dataForDocument.split(' ');
dataForDocument = [split[1], months[split[0]], split[2]].join('/');
}
replaceText(newTargetFileId, marks[j], dataForDocument);
}
} else {
for (j = 0; j < len; j++) {
dataForDocument = ' ';
replaceText(newTargetFileId, marks[j], dataForDocument);
}
}
}
}
}
