How To Generate QR Codes with GraphQL Mutations Using Google Sheets

We all know that Google Sheets is super handy for tracking data.

Did you know you can also generate QR codes with it? On top of that, StepZen allows you to use GraphQL mutations to write to Google Sheets so you can do it all from a single GraphQL endpoint!

Say you're an employer transitioning your employees back to the office after the pandemic forced everyone to work from home. You'd like your employees to start checking in/out by scanning a QR code that's printed out and taped to their desk. That info goes to your separate timeclock app so that you can make sure you're in sync with CDC regulations for the number of employees in your office at once as you phase back in.

With StepZen, you can write your employees names to Google Sheets, along with an auto-generated QR code to print out for their desks, using a single GraphQL API.

Step 1: The Setup

First of all, this example is dependent on a couple accounts:

1. A StepZen account
2. A Google Cloud account (free trial is fine)

It also assumes a Google Sheet with the columns "Text" and "QRCode" and permissions allowing anyone with a link to view.

Step 2 - Create a Google Cloud Service Account and Grant Access to your Google Sheet

You'll need to create a service account, download the service account json file, grant access to the sheet for that service account, and then tell StepZen about the service account. You'll only need to do this step once.

First, log into https://console.cloud.google.com. Free trial should work fine, if you don’t have one already.

• Next, in the search bar at the top of the Google Cloud Platform console, type “Service Accounts” and select the entry called “Service Accounts”.
• Click the “+ Create service account” link at the top.
• Give the service account any name.
• Click the "create" button, then click the "done" button.

In the next step we create a JSON keyfile for this service account. You’ll download this to your computer and use it later.

• Now click on the service account you just created in the list of service accounts. It will be something like “accountname@projectid.iam.gserviceaccount.com”
• Under the KEYS tab on the resulting screen, click the Add Key button and select “Create New Key”
• Leave the default selected (JSON) and click CREATE. A JSON file will be saved to your computer. We’ll use this file later when configuring StepZen to do support mutations on the sheet, so don’t lose it.
• Finally, click the DETAILS tab for the service account, and copy the Email value (it’s just a regular email address).

Now on your Google Sheet, grant Editor access to the email address for your service account.

At this point that service account can write to your sheet! Hooray.

Note: Make sure you've turned on the Sheets API for the Google project you’re in.

Step 3 - Obtaining Your Authorization Value

You'll need to get your authorization value for connecting the dots between StepZen and your Cloud Service.

Remember that json file you downloaded earlier? Your authorization value is the base64-encoded contents of that file.

To encode it, you execute a command like so in a terminal:

base64 -w 0 ~/path/to/file.json

You'll get your authorization value printed out, ending with ==.

Step 4 - Connecting to Google Cloud and Deploying to StepZen

Notice that you've got a .gitignore file in your fork with config.yaml in it.

Create a config.yaml in your working directory, and add the following:

configurationset:
  - configuration:
      name: QRCodeSheets
      authorization: Bearer {{authorization_value_here}}

Now in your terminal, run

stepzen start --dashboard=local

The default way to test your GraphQL endpoint is from the StepZen dashboard explorer, by running stepzen start without the --dashboard=local flag.

You'll be prompted to enter your StepZen account name and admin key, which you can find on your StepZen dashboard after you sign in.

You'll also be prompted to name your endpoint-- use the suggested value or name it what you want.

Then, the StepZen Code Explorer in the form of our custom GraphiQL browser will be created on localhost:5001.

Click on the "Explorer" tab and select the dropdown under "addQRCode", then tick off all the boxes beneath it. This will automatically fill out your query like so:

mutation myMutation {
  addQRCode(QRCode__B: "", Text__A: "") {
    QRCode__B
    Text__A
  }
}

Now, you'll need to add the parameter values to the query.

In text__B you'll need to copy and paste the following (a Sheets equation for generating a QR Code):

=image(\"https://image-charts.com/chart?chs=150x150&cht=qr&choe=UTF-8&chl=\"&ENCODEURL(A2))

In text__A you can add an employee's name.

You'll end up with something like:

mutation myMutation {
  addQRCode(
    QRCode__B: "=image(\"https://image-charts.com/chart?chs=150x150&cht=qr&choe=UTF-8&chl=\"&ENCODEURL(A2))"
    Text__A: "Jane Doe"
  ) {
    QRCode__B
    Text__A
  }
}

Now, hit the play button. If you get the equation and the random string returned to you in JSON form, you know your query worked!

Check your Google Sheets file to see the result!

Imagine the simple things you can do in your frontend now that you can write to Google Sheets with a GraphQL API!

For example, in your frontend you could loop through a list of names to use mutations on.

You could also easily read data back from a Google Sheet (see our docs to learn how).

Where to Go From Here

StepZen allows you to easily create a GraphQL endpoint on any backend and connect different backends to your single GraphQL endpoint. You can integrate with

• MySQL databases
• REST APIs
• PostgreSQL databases
• An imported API schema template

Explore the possibilities! We'd love to hear what your're building- stop on by our Discord!