自動將Google表格生成的QR碼作為影像傳輸到GoogleDoc

我有一個谷歌表，它會自動將資料從谷歌表傳輸到谷歌檔案。現在我在谷歌表格中生成了一個二維碼，但是當我將它作為其他資料值從谷歌表格傳輸到谷歌檔案時，它在谷歌檔案中顯示 Cellimage。我想要在 Google doc 上實際生成的 QR 碼。我正在使用應用程式腳本按照杰弗里先生在 youtube 上的視頻傳輸資料。任何幫助將不勝感激。

使用 A 列和 B 列生成 QR 碼

function onOpen() {

    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu('GR Filled Form');
    menu.addItem('Create New Docs',   'createNewGoogleDocs')
    menu.addToUi();
}

    


function createNewGoogleDocs() {

      const googleDocTemplate = DriveApp.getFileById('Template ID');
 //This value should be the id of the folder where you want your completed documents stored




     const destinationFolder = DriveApp.getFolderById('FolderID')
 //Here we store the sheet as a variable




    const Sheet1 = SpreadsheetApp
    .getActiveSpreadsheet();
    var sheet = Sheet1.getActiveSheet();

    var only  = ['Gen4A','Gen4B','Gen4C','Gen4D','Gen4E','Gen5']
    if (only.indexOf(sheet.getName()) == -1)
    return;





 //Now we get all of the values as a 2D  



     const rows = sheet.getDataRange().getValues();




//Start processing each spreadsheet row




     rows.forEach(function(row, index){


     if (index === 0) return;

     if (row[9]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder



        const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} GR FORM` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp



    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate = new Date(row[3]).toLocaleDateString();
    //const pdfFolder = DriveApp.getFolderById("folderID");
    //In these lines, we replace our replacement tokens with values from our spreadsheet row



    body.replaceText('{{Pallet}}', row[1]);
    body.replaceText('{{Batch}}', row[0]);
    body.replaceText('{{Qty}}', row[2]);
    body.replaceText('{{Date}}', friendlyDate);
    body.replaceText('{{QR Code Image}}', row[4]);
    
    
    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //const blobPDF = doc.getAs(MimeType.PDF);
    //const pdfFile = pdfFolder.createFile(blobPDF).setName(row[0] "-" friendlyDate);
       
    //Store the url of our new document in a variable
    //const url = pdfFile.getUrl();
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index   1, 10).setValue(url)
    
  })
  
}

示例電子表格

uj5u.com熱心網友回復：

從您顯示的電子表格影像中，我確認“E”列具有 QR 碼的影像。但是，從您提供的示例電子表格中，我也確認了“E”列的公式如下=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=160x160&data="&ENCODEURL(A2&" - "&B2),3)。在這種情況下，我認為可以通過檢索 URL 來檢索二維碼。在此答案中，從 URL 中檢索 QR 碼并將其放入 Google 檔案中。

當使用您提供的示例腳本時，如何進行以下修改？

修改后的腳本：

在使用此腳本之前，請先設定'Template ID'和'FolderID'.

function createNewGoogleDocs() {
  // Ref: https://stackoverflow.com/a/51913863/7108653
  var replaceTextToImage = function (body, searchText, url, width = 200) {
    var next = body.findText(searchText);
    if (!next) return;
    var r = next.getElement();
    r.asText().setText("");
    var img = r.getParent().asParagraph().insertInlineImage(0, UrlFetchApp.fetch(url).getBlob());
    if (width && typeof width == "number") {
      var w = img.getWidth();
      var h = img.getHeight();
      img.setWidth(width);
      img.setHeight(width * h / w);
    }
    return next;
  };

  const googleDocTemplate = DriveApp.getFileById('Template ID');
  const destinationFolder = DriveApp.getFolderById('FolderID');
  const Sheet1 = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = Sheet1.getActiveSheet();
  var only = ['Gen4A', 'Gen4B', 'Gen4C', 'Gen4D', 'Gen4E', 'Gen5']
  if (only.indexOf(sheet.getName()) == -1) return;
  const rows = sheet.getDataRange().getValues();
  rows.forEach(function (row, index) {
    if (index === 0) return;
    if (row[9]) return;
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} GR FORM`, destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[3]).toLocaleDateString();
    body.replaceText('{{Pallet}}', row[1]);
    body.replaceText('{{Batch}}', row[0]);
    body.replaceText('{{Qty}}', row[2]);
    body.replaceText('{{Date}}', friendlyDate);
    replaceTextToImage(body, '{{QR Code Image}}', "https://api.qrserver.com/v1/create-qr-code/?size=160x160&data="   encodeURIComponent(row[0] - row[1]));
    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index   1, 10).setValue(url)
  });
}

筆記：

• 此修改后的腳本可用于您的示例電子表格。因此，當您更改了電子表格的結構并且您的實際情況與您的示例電子表格不同時，此腳本可能無法使用。所以請注意這一點。

參考：

• 相關主題
  • 使用 GAS 將表單上傳的影像嵌入到谷歌檔案
  • 使用 Google Apps 腳本將 Google 檔案的文本替換為影像

標籤：谷歌应用脚​​本 谷歌表格 谷歌文档

上一篇：在GoogleSheetsonedit函式中，如果變數單元格值不為空，則中止函式

下一篇：谷歌表格查詢唯一范圍