I have a google sheet which automatically transfer data from google sheet to google doc. Now I generated a QR code in google sheet, but when I Transfer it as other data values from google sheet to google doc it says Cellimage in google doc. I want an actual generated QR code on Google doc. I am using app script to transfer the data as per mr Jeffery’s video on youtube. Any help will be appreciated.

QR code getting generated using column A and Column B

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)
    
  })
  
}

sample spreadsheet

CodePudding user response：

From your showing image of the Spreadsheet, I confirmed that column "E" has the image of the QR code. But, from your provided sample Spreadsheet, also I confirmed that column "E" has the formula like =IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=160x160&data="&ENCODEURL(A2&" - "&B2),3). In this case, I thought that the QR code can be retrieved by retrieving the URL. In this answer, the QR code is retrieved from the URL and put into Google Document.

When your provided sample script is used, how about the following modification?

Modified script:

Before you use this script, please set 'Template ID' and '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)
  });
}

Note:

• This modified script can be used for your sample Spreadsheet. So, when you changed the structure of the Spreadsheet and your actual situation is different from your sample Spreadsheet, this script might not be able to be used. So please be careful about this.

References:

• Related threads
  • Embed image uploaded by form to google doc using GAS
  • Replacing Text to Image for Google Document using Google Apps Script