CODEWITHSUNDEEP
apiimage-processinggoogle-apps-scriptgoogle-sheetsqr-code
deags
deags

Reputation: 478

How to save as image a QR codes created in Google Sheets with api.qrserver?

I am generating QR codes in a column in Google Sheets with data entered via a Google Forms, and I would like to save them as images.

Using Ctrl + C and Ctrl+V works only inside the Google Sheet and trying to copy and paste that one outside the sheet doesnt work either. The clipboard is detected as not containing images to paste elsewhere.

The problem is not in generating the QR, but exporting or saving it as image.

To generate the QR's I'm using the api.qrserver, but the documentation for this only goes about creating or reading QR's [http://goqr.me/api/doc/] , for which this base is used to technically generate a PNG image:

http(s)://api.qrserver.com/v1/create-qr-code/?data=[URL-encoded-text]&size=[pixels]x[pixels]

However, If I use that formula externally to link directly to the cell, the PNG created only gives the URL address of the Google Sheet and not the information of the QR.

This is the array formula I'm using at row 1 to generate the QR's:

    ={"QR";arrayformula( if(                         
    len(INDIRECT("A2:A")),IMAGE("https://api.qrserver.com/v1/create-qr-        
    code/?size=120x120&data="&ENCODEURL(
    "-Tip: "&indirect("B2:B")& char(10)& 
    "-Ub: "&INDIRECT("C2:C")& char(10)& 
    "-#: "&indirect("D2:D")& char(10)&
    "-IDE: "&INDIRECT("E2:E")& char(10)& 
    "-Serial: "&INDIRECT("G2:G") & char(10)&
    "-Pa: "&indirect("H2:H") & char(10)&
    "-IP: "&INDIRECT("I2:I") & char(10)&
    "-As: "&indirect("J2:J") & char(10)&
    "-R: "&INDIRECT("K2:K") & char(10)&
    "-Ar: "&indirect("L2:L") & char(10)
    ),2)
    ,""))}

Ideally, I would like to automatically make the QR's as images and save them to a folder in google drive automatically. If possible, assigning them an IDE value that is contained in another column as well as in the same QR code.

Here is the example for the QR generation: [https://docs.google.com/spreadsheets/d/16pALQ0LNbBqqdRNNejar8zJJPPNEP8I7HSbKDOJHZaw/edit?usp=sharing ]

Upvotes: 0

Views: 10260

Answers (2)

Tanaike
Tanaike

Reputation: 201513

  • You want to save the QR codes in the Spreadsheet as the image files using Google Apps Script.
    • In this case, you want to achieve this without changing the Spreadsheet.
  • Values are put to from the column "A" to "L", when the form was submitted.

If my understanding is correct, how about this sample script? Please think of this as just one of several answers.

In this sample script, the QR code is directly retrieved from api.qrserver.com and those are saved as the image files, because in your situation, the images cannot be retrieved from the Spreadsheet. In this case, all parameters of QR code are used from the Spreadsheet.

Sample script:

Before you run the script, please set the variables of sheetName and folderId. When you run this script, all QR codes in the sheet of Hoja_calculos are created as the image files.

function myFunction() {
  var sheetName = "Hoja_calculos"; // Sheet name is from your shared Spreadsheet.
  var folderId = "###"; // Folder ID of the folder where the image files are created.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var url = "https://api.qrserver.com/v1/create-qr-code/?size=120x120&data=";
  var keys = ["-Tipo:", "-Ubicación:", "-# registro:", "-ID-C:", "-# Serial:", "-Pas:", "-Di I:", "-Asi:", "-RP:", "-Ar:"];
  var values = sheet.getRange(2, 2, sheet.getLastRow() - 1, 11).getValues()
    .filter(function(e) {return e.some(function(f) {return f})})
    .map(function(row) {
      row.splice(4, 1);
      return row;
    });
  var length = values.length;


//  values = [values.pop()]; // If you want to save only the QR code of the last row, please use this line.


  var reqs = values.map(function(e) {return {url: url + encodeURIComponent(keys.map(function(f, j) {return f + e[j]}).join("\r"))}});
  var res = UrlFetchApp.fetchAll(reqs);
  res.forEach(function(r, i) {
    var blob = r.getBlob().setName(length == values.length ? "row" + (i + 2) + ".png" : "row" + (length + 1) + ".png");
    DriveApp.getFolderById(folderId).createFile(blob);
  });
}

Note:

  • If you want to save only the QR code of the last row, please use this line. At that time, please remove // of // values = [values.pop()];. By this, only the QR code of the last row is retrieved.
    • Because I thought that above situation might be required for your situation, I added it. For example, by using the installable trigger to the function, when the form was submitted, only QR code of the last row is created as the image file.
    • But I'm not sure about your environment of the form. So I'm not sure whether this can be used for your situation. I apologize for this.
  • I confirmed that this script worked for your shared Spreadsheet. If the actual Spreadsheet is different from this, please modify the script, because it might not work fine.

References:

Upvotes: 3

Ricardo Cunha
Ricardo Cunha

Reputation: 2075

You can not use a custom function and interacts with Google Drive, there is a limitation to access user data using custom function and onEdit function on G script:

https://developers.google.com/apps-script/guides/sheets/functions

AFAIK the only way to achieve this is creating a custom menu and interacts with a custom function by menu.

  var url = "https://api.qrserver.com/v1/create-qr-code/?data=Sample&size=128x128";
  var folders = DriveApp.getFoldersByName("QRCodeFolderName");// Put here the name of the folder inside root folder
  var folder = folders.next();
  var response = UrlFetchApp.fetch(url);
  if (response.getResponseCode() == 200){
    var fileBlob = response.getBlob();
    folder.createFile(qrcodevalue+".png",fileBlob);
  }

Upvotes: 0

Related Questions