How to embed a QR code from Google Sheets formula into a Google Doc?

Other Questions Google docs
1

I’m trying to add a QR code to my Google Doc. The code should come from a formula in my Google Sheet. I found some code online and tweaked it, but it’s not working right. Here’s what I’ve got:

function addQRCode() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('QR Generator');
  let lastRow = sheet.getLastRow();
  let docId = sheet.getRange(lastRow, 10).getValue();
  let qrData = sheet.getRange(lastRow, 2).getValue();
  
  let doc = DocumentApp.openById(docId);
  let qrUrl = 'https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=' + qrData;
  let qrImage = UrlFetchApp.fetch(qrUrl).getBlob();
  
  doc.getChild(24).asParagraph().appendInlineImage(qrImage);
}

The code runs without errors, but the QR code in the Doc doesn’t match the one in my Sheet. When I scan it, it just searches for ‘CellImage’ on Google. Any ideas what I’m doing wrong? I’m new to working with QR codes, so I’m a bit lost.

2

I’ve encountered this issue before, and it’s often related to how Google Sheets handles cell formatting. Try using the getFormula() method instead of getValue() for your qrData. This will retrieve the actual formula content:

let qrData = sheet.getRange(lastRow, 2).getFormula();

If that doesn’t work, double-check your sheet’s cell formatting. Sometimes, hidden characters or unexpected formatting can interfere with QR code generation.

Also, consider adding error handling to your script. Log the qrData value before creating the URL to ensure it’s what you expect. This can help pinpoint where things are going wrong:

console.log(‘QR Data:’, qrData);

Lastly, if you’re still having trouble, you might want to explore alternative QR code generation libraries that integrate well with Google Apps Script. They often provide more robust options for creating and embedding QR codes.

3

hey owen, looks like ur qrData variable might be the issue. try encoding it with encodeURIComponent() before adding to the url. like this:

let qrUrl = ‘https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=’ + encodeURIComponent(qrData);

that should fix the weird ‘CellImage’ thing. good luck!

4

I’ve dealt with a similar issue before, and I think I see what’s going on. The problem might be in how you’re retrieving the QR data from your sheet. Instead of using getValue(), try getDisplayValue() for the qrData variable. Like this:

let qrData = sheet.getRange(lastRow, 2).getDisplayValue();

This will grab the actual text that’s displayed in the cell, rather than any underlying formula or cell value. Also, as alexj mentioned, encoding the URL is a good practice. Combine these two changes, and you should see the correct QR code in your Doc.

One more thing - be careful with hardcoding child indices like doc.getChild(24). If you add or remove content from your Doc, this might insert the QR code in the wrong place. Consider using a more flexible method to locate where you want the image, such as searching for a specific text marker.