Generate google docs with QR code from google sheet

Question

I have a Google Sheet with many cells, one of which is a QR code (QR code contains name and Employee ID). A Google Docs file is generated based on the values from the worksheet, by replacing placeholder text. This Google Doc will be used as a digital pass.

How can I place the QR code image in the Google Docs, generated from the Google Sheets data?

Ex. In the sheet, I stored Employee ID, Name, and QR Code in columns A, B, and D.
I use replaceText to replace placeholder text in the Document:

      copyBody.replaceText('EmployeeID', row[i][0]);
      copyBody.replaceText('firstName', row[i][1]);
      copyBody.replaceText('qrCode', row[i][3]); etc.

The QR Code is stored in the sheet as a formula result:
=IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & C2)

When I run my script, the EmployeeID and Name fields are replaced in the template document with the desired values, but nothing in the qrCode fields.

Pierre-Marie Richard Pierre-Marie Richard · Accepted Answer · 2017-05-11T07:48:41

I don't think that the QR code that is generated on your Spreadsheet cell is considering as text, so the function will replace 'qrCode' by nothing. You should use the code above:

var doc = DocumentApp.openById('DocId')
var ss = SpreadsheetApp.openById('SsId');
var qrCode = ss.getRange('qrCode').getValue(); // Get the QR code value from te Spreadsheet
var url = "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" + qrCode 
var resp = UrlFetchApp.fetch(url); // Get the image of QR code
var barcode = doc.getChild(0).asParagraph().appendInlineImage(resp.getBlob()); // Value of child depend of where you want your QR code.

For more information about appendInlineImage(blob) method and getChild(name) method

Generate google docs with QR code from google sheet

4 Answers