Search code examples
google-apps-scriptqr-code

Inserting a QR Code generated from a formula in Google Sheets to Google Docs

I currently have this code which I got online and fitted it to mine.

//worksheets
const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("QR CODE GENERATOR");
//lastrow
const lastrow_ws = ws.getLastRow();

function qrCode(){
  var documentID = ws.getRange(lastrow_ws, 10).getValue();
  Logger.log(documentID)
  var doc = DocumentApp.openById(documentID)
  var qrCode = ws.getRange(lastrow_ws, 2).getValue(); // Get the QR code value from the 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(24).asParagraph().appendInlineImage(resp.getBlob()); // Value of child depend of where you want your QR code.
}

Currently it does place the QR Code but not the QR Code generated from the link provided in the Sheets. The QR code placed redirects you to Google searching CellImage which I don't understand. It is my first time handling QR codes.

Screenshots

Google spreadsheets QR:

enter image description here

Google Docs QR below:

enter image description here

Solution

  • About but not the QR Code Generated from the link provided in the Sheets, when I saw your script, it seems that var qrCode = ws.getRange(lastrow_ws, 2).getValue(); is the value of column "B". And, when I saw your sample image of Spreadsheet, it seems that column "B" is images inserted with a formula. I thought that this might be the reason for your current issue.

    It seems that the formula uses a value from column "E". From this situation, how about the following modification?

    From:

    var qrCode = ws.getRange(lastrow_ws, 2).getValue();

    To:

    var qrCode = ws.getRange(lastrow_ws, 5).getValue();

    or

    var qrCode = encodeURIComponent(ws.getRange(lastrow_ws, 5).getValue());
    • By this modification, the value is retrieved from column "E" as qrCode of var url = "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=&" + qrCode.