(Continuation of another thread)
I currently have a Google Sheet with rows of data that I want to merge into a Google Document using a template file. Each row would be generate a new Google Document based on the template's layout. The script I am running currently will check to see if a file related to a Google Sheet row (using a title cell to match) already exists. If it does, then it will be deleted and a new document will be created in it's place with all of the changes made in the Google Sheet. If it does not exist a fresh document will be created.
However, I now need to link to these Google Documents from an external website. With the current script it means that if a change is made to a row in the spreadsheet, then it's corresponding document will be deleted and re-created, along with it's URL, thus making the website link useless and needing to be changed manually.
So ideally what I would like to achieve is rather than deleting and re-creating the documents, is to update the same document if it already exists. That way, the document gets updated and the URL will always be the same.
The script so far is this
/*###################################################################
* Create multiple versions of a document based on Google Sheet Data
* and a Google Doc Template (Mailmerge)
*###################################################################
*/
/*###################################################################
* Main run file sets up variables for insertion into the mailMerge()
* function.
*/
function mergeSheetDoc() {
const TEMPLATE_ID = '16YfyeDjGDp-88McAtLCQQyZ1xz4QX5zEKAS09SaLkJI';//Add your Google Doc template ID
const SS_ID = '1C5gtJCSzHMuSz-oVWEItl2EUVRDwF5iH_RVr6BxLkOU'; // Add your Google Sheet ID
const SHEET_NAME = "data"; // Add your Google Sheet Tab name
const MAPPED = mappedDocToSheet; // Go to Map.gs to update
const FILE_NAME = ["Titre de la formation"] // Header IDs from your Sheet. Change to make your own file name.
docMerge(TEMPLATE_ID,SS_ID,SHEET_NAME,MAPPED, FILE_NAME);
}
/** ###################################################################
* Merges data from a Google Sheet into a newly created doc based on a
* Google Doc template.
*
* param {string} templateID: The id from the Google Doc template.
* param {string} ssID: The id of the Google Sheet.
* param {string} sheetName: the name of the sheet tab you are referencing.
* param {object} mapped: Object array of data you mapped from your Doc template against your Google Sheet headers
* param {array} fileNameDara: An array of data used to generate the file name.
* param {string} rowLen: (optional) If you want to add a number rows to create your merged documents.
*/
function docMerge(templateID,ssID, sheetName, mapped, fileNameData, rowLen = "auto"){
//Get the Spreadsheet and sheet tab
const ss = SpreadsheetApp.openById(ssID);
const sheet = ss.getSheetByName(sheetName);
//Get number of rows to process
rowLen = (rowLen = "auto") ? getRowLen() : rowLen;
//Gets the range of data in the sheet then grabs the values of the range
const range = sheet.getRange(1,1,rowLen,sheet.getDataRange().getNumColumns());
const matrix = range.getValues();
// Searches the file mapped object and finds the corresponding number returns the column number in an array.
const fileNameRows = getFileNameRows()
//Loops through each row of the sheet grabbing the data from each row and putting it into a new doc.
for(let i = 1; i < rowLen; i++){
let row = matrix[i];
//Get the title for the file.
let fileName = buildFileName(row);
//This query parameter will search for an exact match of the filename with Doc file type
let params = "title='"+fileName+"' and mimeType = 'application/vnd.google-apps.document'"
let files = DriveApp.searchFiles(params);
if(files.hasNext()){
while (files.hasNext()) {
//Filename exist
var file = files.next();
//Create a new file
var tmpDoc = DriveApp.getFileById(templateID).makeCopy("tempfile");
//Update the file
updateFileData(row, tmpDoc.getId());
//Copy contents of the new file to the existing file
copyFileData(tmpDoc.getId(), file.getId());
//Delete temporary file
tmpDoc.setTrashed(true);
}
}else{
//Create a new file
let newDoc = DriveApp.getFileById(templateID).makeCopy(fileName);
updateFileData(row, newDoc.getId());
}
}
};
function copyFileData(sourceDoc, destinationDoc){
var srcParagraph = DocumentApp.openById(sourceDoc).getBody().getParagraphs();
var dstParagraph = DocumentApp.openById(destinationDoc).getBody().getParagraphs();
for(var i = 0; i<dstParagraph.length; i++){
//Paragraph Index of description and suivantes information
if(i==5||i==21){
dstParagraph[i].editAsText().setText(srcParagraph[i].editAsText().getText());
dstParagraph[i].setAttributes(srcParagraph[i].getAttributes());
dstParagraph[i].setLeftToRight(true);
dstParagraph[i].setIndentFirstLine(srcParagraph[i].getIndentFirstLine());
dstParagraph[i].setIndentStart(srcParagraph[i].getIndentStart());
dstParagraph[i].editAsText().setFontSize(srcParagraph[i].editAsText().getFontSize());
}
}
}
This code also references a mapping script which can be found here
Included only the modified/new functions.
function docMerge(templateID,ssID, sheetName, mapped, fileNameData, rowLen = "auto"){
//Get the Spreadsheet and sheet tab
const ss = SpreadsheetApp.openById(ssID);
const sheet = ss.getSheetByName(sheetName);
//Get number of rows to process
rowLen = (rowLen = "auto") ? getRowLen() : rowLen;
//Gets the range of data in the sheet then grabs the values of the range
const range = sheet.getRange(1,1,rowLen,sheet.getDataRange().getNumColumns());
const matrix = range.getValues();
// Searches the file mapped object and finds the corresponding number returns the column number in an array.
const fileNameRows = getFileNameRows()
//Loops through each row of the sheet grabbing the data from each row and putting it into a new doc.
for(let i = 1; i < rowLen; i++){
let row = matrix[i];
//Get the title for the file.
let fileName = buildFileName(row);
//This query parameter will search for an exact match of the filename with Doc file type
let params = "title='"+fileName+"' and mimeType = 'application/vnd.google-apps.document'"
let files = DriveApp.searchFiles(params);
if(files.hasNext()){
while (files.hasNext()) {
//Filename exist
var file = files.next();
//Create a new file
var tmpDoc = DriveApp.getFileById(templateID).makeCopy("tempfile");
//Update the file
updateFileData(row, tmpDoc.getId());
//Copy contents of the new file to the existing file
copyFileData(tmpDoc.getId(), file.getId());
//Delete temporary file
tmpDoc.setTrashed(true);
}
}else{
//Create a new file
let newDoc = DriveApp.getFileById(templateID).makeCopy(fileName);
updateFileData(row, newDoc.getId());
}
};
function copyFileData(sourceDoc, destinationDoc){
var srcParagraph = DocumentApp.openById(sourceDoc).getBody().getParagraphs();
var dstParagraph = DocumentApp.openById(destinationDoc).getBody().getParagraphs();
for(var i = 0; i<dstParagraph.length; i++){
//Paragraph Index of description and suivantes information
if(i==5||i==21){
dstParagraph[i].editAsText().setText(srcParagraph[i].editAsText().getText());
dstParagraph[i].setAttributes(srcParagraph[i].getAttributes());
dstParagraph[i].setLeftToRight(true);
dstParagraph[i].setIndentFirstLine(srcParagraph[i].getIndentFirstLine());
dstParagraph[i].setIndentStart(srcParagraph[i].getIndentStart());
dstParagraph[i].editAsText().setFontSize(srcParagraph[i].editAsText().getFontSize());
}
}
}