Search code examples
google-apps-scriptpdf-generationqr-codegoogle-sheets-api

How to place QR code that generated by formula as image in google doc (Export to PDF)?

I've generated QRcode by using image function (=image("https://chart.googleapis.com/....) and want to place in google doc, after the export to PDF, but it's show nothing, How do i place QRcode from spreadsheet in doc as image. Thank everyone for advance.

Here is code. (Just Example)

function createBulkPDFs(e) {

  const pdfFolder = DriveApp.getFolderById("xxx");
  const tempFolder = DriveApp.getFolderById("xxx");
  const docFile = DriveApp.getFileById("xxx");
  const ws = SpreadsheetApp.openById("xxx").getSheetByName("eee");

  const range = ws.getRange(2, 1, ws.getLastRow() - 1, 6).getValues();
  var data = range[range.length - 1];
  createPDF(data[1],data[3], data[4], data[5], data[6], data[3] + "" + data[4], docFile, tempFolder, pdfFolder);
}

function createPDF(qcode, First, Last, address, quantity, pdfName, docFile, tempFolder, pdfFolder) {

  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();

  body.replaceText("{qr}", qcode);
  body.replaceText("{fn}", First);
  body.replaceText("{ln}", Last);
  body.replaceText("{addr}", address);
  body.replaceText("{qty}", quantity);
  tempDocFile.saveAndClose();

  const pdfContentBolb = tempFile.getAs("application/pdf");
  const pdfFile = pdfFolder.createFile(pdfContentBolb).setName(pdfName);
  tempFile.setTrashed(true);

  return pdfFile;}

Just want to place B column as image and save to PDF. and google sheet here !! Click...

Solution

  • I believe your goal as follows.

    • You want to copy the values from Google Spreadsheet to Google Document using Google Apps Script.
    • The Google Spreadsheet is as follows.

    Modification points:

    • When I saw your sample Spreadsheet and your script, it seems that you send the arguments to the function createPDF like createPDF(data[1],data[3], data[4], data[5], data[6], data[3] + "" + data[4], docFile, tempFolder, pdfFolder).
      • The Spreadsheet has the values from the columns "A" to "F". But in your arguments, data[6] is used. In this case, an error occurs at body.replaceText("{qty}", quantity).
      • From createPDF(qcode, First, Last, address, quantity, pdfName, docFile, tempFolder, pdfFolder), I thought that it is required to be createPDF(data[1], data[2], data[3], data[4], data[5], data[2] + "" + data[3], docFile, tempFolder, pdfFolder).
      • If your actual Spreadsheet is different from your sample Spreadsheet, the following modified script doesn't work. So, please be careful this. In this answer, I modified your script using your sample Spreadsheet.
    • In order to retrieve the image from the cell of =image("https://chart.googleapis.com/...., I would like to propose to retrieve the image blob using UrlFetchApp. About the script for replacing the text to image, I used my sample script at this answer.
      • The URL is retrieved from the formula and data you retrieved.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    Before you use this script, please set each values of xxx.

    function createBuikPDFs(e) {
  const pdfFolder = DriveApp.getFolderById("xxx");
  const tempFolder = DriveApp.getFolderById("xxx");
  const docFile = DriveApp.getFileById("xxx");
  const ws = SpreadsheetApp.openById("xxx").getSheetByName("eee");

  const values = ws.getRange(2, 1, ws.getLastRow() - 1, 6).getValues();
  const length = values.length;
  var data = values[length - 1];
  const formula = ws.getRange(length + 1, 2).getFormula();
  const url = formula.replace(/\=image\("/i, "").replace(/chl\="&.+/i, `chl=${data[5]}`);
  data[1] = UrlFetchApp.fetch(url).getBlob();
  createPDF(data[1], data[2], data[3], data[4], data[5], data[2] + "" + data[3], docFile, tempFolder, pdfFolder);
}

function createPDF(qcode, First, Last, address, quantity, pdfName, docFile, tempFolder, pdfFolder) {

  // This function is from https://stackoverflow.com/a/51913863
  var replaceTextToImage = function (body, searchText, image, width) {
    var next = body.findText(searchText);
    if (!next) return;
    var r = next.getElement();
    r.asText().setText("");
    var img = r.getParent().asParagraph().insertInlineImage(0, image);
    if (width && typeof width == "number") {
      var w = img.getWidth();
      var h = img.getHeight();
      img.setWidth(width);
      img.setHeight(width * h / w);
    }
    return next;
  };

  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();

  replaceTextToImage(body, "{qr}", qcode);
  body.replaceText("{fn}", First);
  body.replaceText("{ln}", Last);
  body.replaceText("{addr}", address);
  body.replaceText("{qty}", quantity);
  tempDocFile.saveAndClose();

  const pdfContentBolb = tempFile.getAs("application/pdf");
  const pdfFile = pdfFolder.createFile(pdfContentBolb).setName(pdfName);
  tempFile.setTrashed(true);

  return pdfFile;
}

    Note:

    • In this modified script, your sample Spreadsheet is used. Please be careful this.

    References: