google-apps-script - 使用 Google Apps 脚本将文本占位符替换为图像

Question

我正在尝试将四张图片放入我的 google 文档中，它们的格式应相同且位于一个正方形内。我正在尝试替换文本占位符，但我愿意接受其他方法。

图像 URLS 位于我的 google 表格中，我将它们初始化为变量。

我试图遍历 4 张图片中的每一张，并用它们替换每个相应的文本占位符。

function createDocument() {
  var headers = Sheets.Spreadsheets.Values.get('1Yy70i8aQ3GZEp5FE5mhO458Bmivu01ZeykG0GFZmrVo', 'A4:AL4');
  var tactics = Sheets.Spreadsheets.Values.get('1Yy70i8aQ3GZEp5FE5mhO458Bmivu01ZeykG0GFZmrVo', 'A5:J26');
  var templateId = '1ajVQxJAgGxXF3ivkaWL5WEv2xhiJc9r0YwxLH5Hm17w';
      
  for(var i = 0; i < tactics.values.length; i++){
        
        // see what the sheets API is returning
        Logger.log(tactics);
        
        // initialise variables from the sheet
        var projectID = tactics.values[i][0];
        var projectName = tactics.values[i][1];
      
        // copy our template and capture the ID of the copied document
        var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
        
        // name the copied doc by projectID
        DriveApp.getFileById(documentId).setName(projectID + 'overview');
        
        // update the body of the document
        var body = DocumentApp.openById(documentId).getBody();
        
        // replace template placeholders with sheet variables
        body.replaceText('##ProjectID##', projectID)
        body.replaceText('##ProjectName##', projectName)

        // initialise images from URLs
        var URL1 = tactics.values[i][10];
        var URL2 = tactics.values[i][11];
        var URL3 = tactics.values[i][12];
        var URL4 = tactics.values[i][13];

        var fileID1 = URL1.match(/[\w\_\-]{25,}/).toString();
        var img1   = DriveApp.getFileById(fileID1).getBlob()
        var item1 = body.appendListItem('Item 1');
        // item1.addPositionedImage(img1); add images so that they are formatted as a square as shown below

执行此操作的 github 函数有被评论 - https://gist.github.com/tanaikech/f84831455dea5c394e48caaee0058b26 - 我如何为 4 个图像 URL 中的每一个实现这个？

Answer 1

您似乎使用的是高级服务而不是常规服务。您还应该将此代码拆分为更易于管理的函数。我喜欢这个问题，所以这是我的解决方案:

const SHEET_ID = ''
const TEMPLATE_ID = ''

/**
 * Entry, what you call to make the template.
 */
function createAllDocuments() {
  const spreadsheet = SpreadsheetApp.openById(SHEET_ID)
  const sheet = spreadsheet.getSheets()[0] // Only sheet?
  const tactics = sheet.getRange('A5:M')
    .getValues()
    .filter(arr => arr.some(v => !!v)) // Remove empty rows
  
  for (let tactic of tactics) {
    // Get the variables for the tactic
    const projectID = tactic[0]
    const projectName = tactic[1]
    const images = tactic.slice(10).filter(v => !!v)
    createDocument(projectID, projectName, images)
  }
}


/**
 * Creates a single document from the info.
 * @param {string} projectID ID of the project.
 * @param {string} projectName Name of the project.
 * @param {string[]} images URL of the images to replace with.
 */
function createDocument(projectID, projectName, images) {
  // Copy the document and get its body
  const doc = openDocumentCopy(TEMPLATE_ID)
  const body = doc.getBody()
  
  // Make the changes
  doc.setName(`${projectID} overview`)
  body.replaceText('##ProjectID##', projectID)
  body.replaceText('##ProjectName##', projectName)
  
  for (let i = 0; i < images.length; i++) {
    const img = getFileByUrl(images[i])
    const placeholder = `##GoogleID${i+1}##`
    replaceTextWithImage(body, placeholder, img, 200)
  }
}


/**
 * Creates and opens a copy of a template.
 * @param {string} id ID of the template.
 * @returns {DocumentApp.Document}
 */
function openDocumentCopy(id) {
  return DocumentApp.openById(DriveApp.getFileById(id).makeCopy().getId())
}


/**
 * Gets a file that doesn't have a resource key from its URL
 * @param {string} url URL of the file
 * @returns {DriveApp.File}
 */
function getFileByUrl(url) {
  const id = url.match(/[\w\_\-]{25,}/)[0]
  return DriveApp.getFileById(id)
}


/**
 * Replaces a document text with an image file. It repalces the entire paragraph.
 * Based of Tanaike's https://gist.github.com/tanaikech/f84831455dea5c394e48caaee0058b26
 * 
 * @param {DocumentApp.Body} body Body of the file. Where to replace in.
 * @param {string} text Text to replace.
 * @param {DriveApp.File} imgFile Image as a file.
 * @param {number} [width] Optional width to set to the image.
 */
function replaceTextWithImage(body, text, imgFile, width) {
  for (let entry of findAllText(body, text)) {
    const r = entry.getElement()
    
    // Remove text
    r.asText().setText("")
    
    // Add image
    const image = r.getParent().asParagraph().insertInlineImage(0, imgFile.getBlob())
    
    // Resize if given
    if (width != null) {
      setImageWidth(image, width)
    }
  }
}


/**
 * Generator that finds all the entryies when searching.
 * @param {DocumentApp.Body} body Body of the file.
 * @param {string} text Text to find.
 * @returns {Iterator.<DocumentApp.RangeElement>}
 */
function* findAllText(body, text) {
  let entry = body.findText(text)
  while(entry != null) {
    yield entry
    entry = body.findText(text, entry)
  }
}


/**
 * Sets an image size based on its width.
 * @param {DocumentApp.Image|DocumentApp.InlineImage} image Image to apply.
 * @param {number} width Width to set
 */
function setImageWidth(image, width) {
  const ratio = image.getHeight() / image.getWidth()
  image.setWidth(width)
  image.setHeight(width * ratio)
}

请注意，这里的大部分工作是清理代码并将它们拆分为不同的函数。我使用了 Tanaike 代码的修改版本。

您可能需要稍微修改一下这段代码。此外，它只支持用图像替换整个段落，这对您的情况应该足够了。

引用资料

• Body.prototype.findText(searchPattern) (Apps Script reference)
• Paragraph.prototype.insertInlineImage(childIndex, image) (Apps Script reference)
• Array.prototype.slice() (MDN JavaScript reference)
• Array.prototype.filter() (MDN JavaScript reference)
• Array.prototype.some() (MDN JavaScript reference)
• Template literals (Template strings) (MDN JavaScript reference)
• for...of statement (MDN JavaScript reference)
• Iterators and generators (MDN JavaScript guide)