There are two types of innovations: Ones that accomplish something wholly new and original, and ones that accomplish something ordinary in a wholly new and original way. Square’s mobile card readers land, well, squarely in the latter category, converting a credit card’s magnetic strip data into audio that their app recorded and converted back into payment data.

Since the release of their headphone jack card readers, Square has released iPad point-of-sale stands, phone-based contactless payments, and software for eCommerce and payroll management. If you’re using any of those devices or services, even just one, you’re generating a lot of valuable business intelligence that could help you grow sales and revenue. That is, assuming you can find a way to simplify and centralize the high-level takeaways.

Viewing and filtering Square reports for payments and orders

The only way to chart sales, discounts, taxes, labor, and other data in Square is in the Reports sidebar tab, and its submenu is full of default views. You might pop into the Sales summary report for an hour-by-hour graph, then over to the Discounts tab to review how various promotions are performing, and wrap things up with a breakdown of which types of items are most popular in Category sales. It’s not easy to put all of that on a single page, though.

There is a Custom reports tab. But none of the available fields include charts or graphs, just text-based lists and summaries. One option for turning everything into data visualizations on a single page would be using Square’s manual CSV and PDF exports, pasting them into a spreadsheet or database. That will quickly become tedious, though. 

Although Square’s built-in reporting leaves a lot to be desired, it's one of the better platforms we’ve encountered in terms of how nicely it plays with automation tools and API calls. That gives you nearly infinite ways to build the Square dashboard your team needs.

Building a custom Square dashboard from webhook notifications

Today’s databases are far more accessible than those from a decade ago. Sure, people still use SQL, Access, and other platforms from the mid aughts. But it feels like every day, there’s a new beginner-friendly cloud-based database that includes automation and data visualization features. For a small business using Square, a free Airtable account may be all you need to create a store performance dashboard. 

Sign up for an Airtable account, and create a “base” or open one of the templates from the directory in the bottom left of your account dashboard (Order Tracking & Invoicing is probably the closest option). In your base, look for the Automations menu along the top of the screen and open it. 

The first thing you’ll want to do in Airtable’s automation builder is select + Add trigger and choose When webhook received. On the right side of the screen, look for the webhook link and click the Copy button. Now, you need to give that endpoint URL to Square.

Open a new browser tab, go to developer.squareup.com, and log in (or click Account). Hit the big ‘+’ button under Applications and title it something like “Airtable webhook”. Go to the next page, choose Manage a product catalog, followed by Myself, and complete the setup. Congratulations, you just built a Square app! Let’s configure it to actually do something.

In your Square application page, switch the toggle at the top from Sandbox to Production. Then, open the Subscriptions menu (under Webhooks on the left sidebar) and click the Add subscription button. Punch in a name and paste your Airtable webhook link into the URL field. All that’s left is to choose the data you want sent to Airtable. For this example, let’s go with `payment.created` events. Click Save and then, importantly, add a test payment (cash works best for this).

Back in your Airtable automation builder, click + Add advanced logic or action followed by Create record. Next, pick a table and a field where you want to see a specific data point from Square. That should pop up a new textbox; select the blue `+` symbol and pick from the list of details that Airtable received from Square (usually, you’ll find the most relevant variables under `body` > `data` > `object`). Run a test, and if all goes well, turn on the automation.

Now that you can automatically sync Square data to Airtable, it’s just a matter of making it look presentable. We’ve already written about how to create great-looking Airtable dashboards and connect them to ScreenCloud digital signage, so head there once your webhook notifications are set up and working (tl;dr click Share view from any of your base’s views and create a link to send to individuals or an embed to add to digital signage). That's a great way to share your company's Square revenue to keep your team motivated.

It’s also worth pointing out that you could also use Zapier or Make.com to receive webhook payloads and send them to an app that isn’t compatible with webhooks. But there are limitations. Zapier doesn’t include webhook support on its free plan, and Make.com limits you to 1,000 automation runs per month on the free plan. 

No need to hack around those constraints, though. There’s another totally free automation option.

You never know what you’re going to get when working with an app’s API. Sometimes it’s a confusing, labyrinthine mess. Other times, as in the case of Square, it’s refreshingly simple.

Go back to your Square developer dashboard, click on Credentials from the left sidebar, and copy your Production Access token. Now, open your Terminal or Command Prompt and enter this command (after replacing PRODUCTION_ACCESS_TOKEN with the one you copied from your account:

<code style="font-family:monospace;">cURL https://connect.squareup.com/v2/payments -H "Square-Version: 2024-01-18" -H "Authorization: Bearer PRODUCTION_ACCESS_TOKEN" -H "Content-Type: application/json"</code>

That’s all you need to return data from your most recent payments (you can use Square’s API explorer to add parameters that narrow your search. It’s cool but not exactly useful. A more convenient solution is to turn that simple command into a Google Apps Script that turns the output into readable spreadsheet rows and runs a few times per day.

Start by opening a new spreadsheet, opening the Extensions menu, and selecting Apps Script. Here, you can write and run JavaScript that interacts with your Google Drive documents. First, let’s add some code to make the API call parse the JSON response to the spreadsheet you had open when creating the script:

<code style="font-family:monospace;">function fetchSquareOrderData() { 

  var url = 'https://connect.squareup.com/v2/payments'; 

  var headers = { 

    'Square-Version': '2023-12-13', 

    'Authorization': 'Bearer PRODUCTION_ACCESS TOKEN', // Use your token 

    'Content-Type': 'application/json' 

  }; 

  var options = { 

    'method': 'get', 

    'headers': headers, 

    'muteHttpExceptions': true 

  }; 

  var response = UrlFetchApp.fetch(url, options); 

  var responseData = JSON.parse(response.getContentText()); 

  if (response.getResponseCode() === 200) { 

    writeToSpreadsheet(responseData); 

  } 

}</code>

Next, we need to add the writeToSpreadsheet function with a few important features. First, we want to split the data that we’re grabbing with the call into columns and always insert it in row two. Then, it’s a good idea to reformat what you’re getting back so it’s more readable. For example, payment.amount_money.amount is in cents, so we’ll divide that by 100 and put a dollar sign in front of it.

<code style="font-family:monospace;">function writeToSpreadsheet(data) { 

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 

  var payments = data.payments; 

  var startRow = 2; // Assuming row 1 is for headers 

  payments.forEach(function(payment) { 

    var amountInDollars = '$' + (payment.amount_money.amount / 100).toFixed(2); 

    var createdAt = new Date(payment.created_at); 

    var date = createdAt.toLocaleDateString(); 

    var time = createdAt.toLocaleTimeString(); 

    var row = [ 

      amountInDollars, 

      date, 

      time, 

      payment.source_type, 

      payment.note || 'No Note' 

    ]; 

    // Inserting the row at the specified position 

    sheet.insertRowBefore(startRow); 

    var range = sheet.getRange(startRow, 1, 1, row.length); 

    range.setValues([row]); 

  }); 

}</code>

You can find the whole code snippet in this doc. Click Deploy and New deployment in the upper right corner of the screen. Choose Web app from the Select type dropdown, give it a name, and hit Deploy. 

During deployment, Apps Script will ask you to Authorize access and warn you that your code isn’t a verified app. Select Advanced and Go to project to bypass the warning and allow the connection. Finally, click Run above your code after finishing deployment and authorization. Your spreadsheet should now contain all of the formatted data.

Obviously, manually running this script is only marginally better than doing it from the command line. But there is a way to run an Apps Script automatically: Triggers (in the left sidebar). Add two triggers, one for fetchSquareOrderData and one for write ToSpreadsheet, both with time-driven event sources (every 2 hours seems best) and a daily failure notification setting.

Considering the ubiquity and interoperability of Google Sheets, your options for creating a Square dashboard at this point are endless. Sure, you could create charts in graphs right in the spreadsheet (possibly connecting Sheets to digital signage). Other options include feeding the numbers to a handful of automated Chart.js graphs or connecting the spreadsheet to a more powerful data visualization tool like Looker Studio for more detailed Square revenue charts on your digital signage. 

Any app that gives you a public sharing link or embed can be added to digital signage where everyone can see new sales as they come in. And for apps that don’t have public links, ScreenCloud’s Pro tier includes a Dashboard tool that gives you a virtual browser to log into the app and highlight any data you want to scrape.

Get more eyes on your business’s most important data 

We’re seeing more and more software companies limit external access to their apps and data. But if you know how to set up a webhook or make an API call, you’ll always be ahead of the curve, creating your own solutions instead of paying someone else to build what you need.

While ScreenCloud is easy enough for non-technical people to use, it’s also designed for you to DIY and automate your signage, whether that’s turning a hand-drawn picture into a dashboard or applying CSS animations to content updates.

Grab a free 14-day trial of ScreenCloud today and start turning your data into revenue growth!

While its built-in reporting features can feel somewhat limited, there are several workaround for extracting and consolidating store performance data.

Turn Square Payments & Orders Into a Digital Signage Dashboard

Turn Square Payments & Orders Into a Digital Signage Dashboard

Turn Square Payments & Orders Into a Digital Signage Dashboard

Contents

Ask for an AI Summary