For long lists in documents, it is usually too tedious to create QR codes manually and then insert them into the list. You can automate this task in Google Sheets and save a lot of time. To do this, use the IMAGE function, which retrieves images from the internet and inserts them into the active cell.
Enter =IMAGE in a cell; the program will then offer online help with an example. Click the arrow icon on the right to expand the help view. Copy the example with the Google logo, paste it into the active cell and press Enter. Google Sheets automatically retrieves the image from the web and displays it in the cell.
In combination with a query to QRServer, QR codes can be dynamically generated in a Google spreadsheet.
Foundry
The QR Server allows QR images to be generated via an online request. You can use the service directly in Google Sheets and insert the data automatically. To do this, use the IMAGE function again.
A list of entries serves as an example: the first two columns contain the name and web address of the offers respectively. In the third column, a QR code is generated using QR-Server. When retrieving the image, the address must be constructed as follows:
=IMAGE(“https://api.qrserver.com/v1/create-qr-code/?data=”&(A2)&”&color=000000&bgcolor=FFFFFF&size=200×200”)
The URL for retrieval begins with the address for accessing the QR Server API: https://api.qrserver.com/v1/create-qr-code/
The parameters are appended to this with a question mark and separated by ampersands. First, the destination address for the QR code is defined using “data”. This is read from the column to the left of the QR code.
This is followed by the optional settings for the foreground and background colors: “color” and “bgcolor” are formatted in RGB, as is standard in HTML. The final parameter, “size”, specifies the size of the QR code to be generated.
The IMAGE function in Google Sheets allows you to embed images from various online sources. The help section shows an example for a logo.
Sam Singleton
Such lists can be used, for example, as a notice board or for catalog design: users scan with their smartphone and are thus quickly directed to the web pages for the individual entries. Once the spreadsheet has been finalized, you can save the document as a PDF to your PC via the File menu.
The QR codes in Google Sheets are retrieved dynamically. This means that the code is queried again every time Google Sheets is opened. As an alternative to Google’s spreadsheet program, you can use Microsoft Office 365 or the online version of Excel. These programs also have an IMAGE function, although you will need to adjust the syntax.
