使用Google Script创建和/或更新邮件合并Google文档 [英] Creating and/or update mail merge Google document using Google Script
问题描述
此代码还引用了一个映射脚本，可以在此处找到 https://textuploader.com/18xic 仅包括修改后的功能/新功能.解决方法摘要:
样本修改代码:
函数docMerge(templateID，ssID，sheetName，已映射，fileNameData，rowLen ="auto"){//获取电子表格和表格标签const ss = SpreadsheetApp.openById(ssID);const sheet = ss.getSheetByName(sheetName);//获取要处理的行数rowLen =(rowLen =自动")?getRowLen():rowLen;//获取工作表中的数据范围，然后获取该范围的值const range = sheet.getRange(1,1，rowLen，sheet.getDataRange().getNumColumns());const matrix = range.getValues();//搜索文件映射的对象并找到相应的数字，以返回数组中的列号.const fileNameRows = getFileNameRows()//遍历工作表的每一行，从每一行中获取数据并将其放入新文档中.for(让i = 1; i< rowLen; i ++){设row = matrix [i];//获取文件的标题.让fileName = buildFileName(row);//此查询参数将搜索与Doc文件类型完全匹配的文件名let params ="title ='" + fileName +"和mimeType ='application/vnd.google-apps.document'"让文件= DriveApp.searchFiles(params);if(files.hasNext()){while(files.hasNext()){//文件名存在var file = files.next();//创建一个新文件var tmpDoc = DriveApp.getFileById(templateID).makeCopy("tempfile");//更新文件updateFileData(row，tmpDoc.getId());//将新文件的内容复制到现有文件copyFileData(tmpDoc.getId()，file.getId());//删除临时文件tmpDoc.setTrashed(true);}}别的{//创建一个新文件让newDoc = DriveApp.getFileById(templateID).makeCopy(fileName);updateFileData(row，newDoc.getId());}};函数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 ++){//描述和辅助信息的段落索引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());}}} 它做什么?
- 如果找到文件名，则基于电子表格中的更新信息创建一个临时文档
- 将特定段落从临时文档复制到现有文档
- 使用 setTrashed()方法删除临时文档
(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
https://textuploader.com/18xic
Workaround Summary:
- If filename already exist, create a temporary document that will replace the textId tags from the template document
- Copy the paragraph text and attributes of the temporary document to the existing document.
Sample Modified Code:
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());
}
}
}
What it does?
- If filename was found, create a temporary document based on the updated information in the spreadsheet
- Copy the specific paragraphs from the temporary document to the existing document
- Delete the temporary document using setTrashed() method.
这篇关于使用Google Script创建和/或更新邮件合并Google文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
